Welcome to the EGGhead Forum - a great place to visit and packed with tips and EGGspert advice! You can also join the conversation and get more information and amazing kamado recipes by following Big Green Egg to Experience our World of Flavor™ at:
Facebook  |  Twitter  |  Instagram  |  Pinterest  |  Youtube  |  Vimeo
Share your photos by tagging us and using the hashtag #BigGreenEgg.

Want to see how the EGG is made? Click to Watch

importing value from MS excel into MS access

SangKisuke
SangKisuke Posts: 10
edited November -1 in EggHead Forum
greeting,
i am building a program, in Access, and in certain part of the part of the program, it has to import data from an excel file. i manage to build a code for the MS Access to open the excel file, but i did not know how to take the value from the specific cell in one of the sheet in the excel file.
i try
a= x1.Range("A19").Value
and i didnt work.
help.
«1

Comments

  • FlaPoolman
    FlaPoolman Posts: 11,676
    350 indirect to 140° ;)
  • Carolina Q
    Carolina Q Posts: 14,831
    damn, I'm getting slow in my old age!! :laugh:

    I hate it when I go to the kitchen for food and all I find are ingredients!                                                                                                                                                                                                                           

    Michael 
    Central Connecticut 

  • FlaPoolman
    FlaPoolman Posts: 11,676
    I used both fingers :P
  • FlaPoolman
    FlaPoolman Posts: 11,676
    Thats what I said Sang,,,, I think you have the wrong eggheads :laugh:
  • FlaPoolman wrote:
    Thats what I said Sang,,,, I think you have the wrong eggheads :laugh:
    \
    oh.. haha
  • stike
    stike Posts: 15,597
    that's not correct. e-mc^2. you were multiplying mc by a factor of 2
    ed egli avea del cul fatto trombetta -Dante
  • stike
    stike Posts: 15,597
    post this in the OT forum. odd first post. do you live in Nova Scotia by any chance?
    ed egli avea del cul fatto trombetta -Dante
  • WessB
    WessB Posts: 6,937
    LOL...man we do that all the time....you can figure it out..
  • LDD
    LDD Posts: 1,225
    send me your code I'll take a look
    context is important :)
  • LDD wrote:
    send me your code I'll take a look

    Sub checkingOnTotal()
    '-On Error GoTo LocalError


    Dim a, b, c, d As Integer
    Dim TotalVolume, TotalValue As Integer




    Dim objApp As Excel.Application
    Dim objSheet As Excel.Worksheet
    Dim objBook As Excel.Workbook
    Dim sSQL As String
    Dim Path As String
    Dim CurrentFolder As Object


    Set Db = CurrentDb()


    Set objBook = Workbooks.Add("" & CurrentProject.Path & "\eFIX\eFix\012\ISR.xls") 'the ISR spreadsheet
    Set objApp = objBook.Parent
    Set objSheet = objBook.Worksheets("A") 'sheet that contain the Grand Total of Trading Volume OMT
    objBook.Windows(1).Visible = True
    objApp.Visible = True




    '--take value of Grand Total of Trading Volume OMT

    a = xl.Range("O19").Value
    c = xl1.Range("O" & 31 & "").Value





    Set objSheet = objBook.Worksheets("B") 'sheet that contain the Grand Total of Trading Volume DBT
    objBook2.Windows(1).Visible = True
    objApp.Visible = True



    '--take value of Grand Total of Trading Volume DBT

    b = xl1.Range("M" & 19 & "").Value
    d = xl1.Range("O" & 31 & "").Value




    Set objSheet = objBook.Worksheets("C") 'sheet that contain the Grand Total of Trading Volume DBT
    objBook.Windows(1).Visible = True
    objApp.Visible = True



    '--take value of Grand Total from BO

    m = xl1.Range("O" & 49 & "").Value
    n = xl1.Range("O" & 49 & "").Value


    '-- checking formula

    TotalVolume = ((e - (a + b)) / e) * 100
    TotalValue = ((f - (a + b)) / f) * 100





    '--create new excel book for report


    strLocation = CurrentProject.Path & "\template2.xls"

    Set xl = CreateObject("Excel.Application")
    xl.Visible = False 'Makes the spreasheet visible. False will let you open it behind the scenes
    Set xb = xl.Workbooks.Open(strLocation, Updatelinks:=False) 'xl.Workbooks.Add 'Will Create a new workbook




    create_excel (where_to)

    openexcel (WhereTo) 'Gets the location of the template
    xl.UserControl = False 'Doesnt allow user any control whilst we run our update
    xl.Worksheets(1).Select 'Selects the claim breakdown sheet



    '--insert title for report
    xl.Range("A2").Value = "5% Checking on Total Volume & Total value" & UCase(Format(Label49.Caption, "MMMM YYYY")) & ""





    '--Insert data for OMT Trading Volume Local Purchase
    If TotalVolume > 5 Then
    CellReff = 6
    '- if cellReff ade value, Then
    CellReff = 6 + 1
    xl.Range("c" & CellRef & "").Value = a
    xl.Range("D" & CellRef & "").Value = c
    xl.Range("E" & CellRef & "").Value = b
    xl.Range("F" & CellRef & "").Value = d
    xl.Range("G" & CellRef & "").Value = TotalValue
    xl.Range("H" & CellRef & "").Value = TotalVolume
    Else
    response = MsgBox("Oopps!!")

    End If



    xb.SaveAs CurrentProject.Path & "CheckingTotalValue.xls"
    xb.Close
    xl.Quit

    LocalError:
    MsgBox Err.Number & vbCr & vbCr & Err.Description
    '--GoTo LocalExit

    End Sub
  • there it is LDD.. :)
  • 2Fategghead
    2Fategghead Posts: 9,624
    How do you cook on your Big Green Egg? What is your favorite rub? What part of world do you live in? :)
  • hornhonk
    hornhonk Posts: 3,841
    It appears he..she?..ate too many coco-puffs.
  • LDD
    LDD Posts: 1,225
    check your e-mail...
    context is important :)
  • 2Fategghead
    2Fategghead Posts: 9,624
    I guess they are getting their jollies. Lots of ice and snow and people have nothing better to do. Truth be told their was a time I new that stuff. I had several semesters of programing language. :)
  • LDD wrote:
    check your e-mail...

    got it. :)
  • LDD
    LDD Posts: 1,225
    2Fategghead wrote:
    I guess they are getting their jollies. Lots of ice and snow and people have nothing better to do. Truth be told their was a time I new that stuff. I had several semesters of programing language. :)

    jollies... :woohoo:

    if I reach that point, it'll be time to stop ;-)
    context is important :)
  • TNmike
    TNmike Posts: 643
    If you can't see the problem I'll be damn if I'm gonna' tell ya. :angry:
  • Fidel
    Fidel Posts: 10,172
    You need to design a form in Access, and you can program the form to look for the particular cell/sheet value.

    Or you could create a permanent link if you want to maintain a relationship between your Access database and a range of cells in a particular worksheet.
  • Fidel wrote:
    You need to design a form in Access, and you can program the form to look for the particular cell/sheet value.

    do i really need a form? because i only want to take certain value from the excel sheet.. that is the total amount that have been calculated in the excel..help.
  • Fidel
    Fidel Posts: 10,172
    Do you have excel object library 11 installed?

    If so then you can use something along these lines, assuming you are actually coding in Access:


    Dim xl As Excel.Application
    Dim xlsht As Excel.Worksheet
    Dim xlWrkBk As Excel.Workbook


    Set xl = CreateObject("Excel.Application")
    Set xlWrkBk = GetObject("H:/ggg.xls")
    Set xlsht = xlWrkBk.Worksheets(1)
    Reply With Quote

    set myRec=currentdb.openrecordset("NameOfTable")
    set xlApp=createobject("Excel.Application")
    set xlWrksht=xlApp.Open("PathOfWorksheet").Worksheets( "WorksheetNumber")

    myrec.addnew
    myrec.fields("NameOfFields")=xlWrkSht.cells(1,"A")
    ......
    myRec.update


    But I don't understand why using a form would be difficult.
  • Fidel wrote:
    Do you have excel object library 11 installed?

    If so then you can use something along these lines, assuming you are actually coding in Access:


    Dim xl As Excel.Application
    Dim xlsht As Excel.Worksheet
    Dim xlWrkBk As Excel.Workbook


    Set xl = CreateObject("Excel.Application")
    Set xlWrkBk = GetObject("H:/ggg.xls")
    Set xlsht = xlWrkBk.Worksheets(1)
    Reply With Quote

    set myRec=currentdb.openrecordset("NameOfTable")
    set xlApp=createobject("Excel.Application")
    set xlWrksht=xlApp.Open("PathOfWorksheet").Worksheets( "WorksheetNumber")

    myrec.addnew
    myrec.fields("NameOfFields")=xlWrkSht.cells(1,"A")
    ......
    myRec.update


    But I don't understand why using a form would be difficult.

    i am coding in access.. i will try to use it..thanx. :)
  • i dont understand the line

    With oWs
    .Cells(3, 1) = test
    End With


    can u explain it to me.please. :( . i am trying to pass the value from the excel, into the access.. so i can calculate it in the access
  • Fidel
    Fidel Posts: 10,172
    I made an edit in the code, check the latest version of my post.
  • I had no idea we had this knowledge among us eggers...I am really impressed, I think I'll pour me another single malt scotch and enjoy the aromas coming fromst my egg. :evil:
  • i think i may have a drink ok i will
  • Well you see...You have to heat your egg up to about 400F and then you can access your egg for an excel erated cook.
  • Carolina Q
    Carolina Q Posts: 14,831
    I gotta ask... Dude, why are you HERE? We COOK stuff! On a GRILL! Many of us Excel at it, but most of us probably don't have Access to the information you need.

    I hate it when I go to the kitchen for food and all I find are ingredients!                                                                                                                                                                                                                           

    Michael 
    Central Connecticut