Big Green Egg - EGGhead Forum - The Ultimate Cooking Experience...
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 at:

Facebook  |  Twitter  |  Instagram  |  Pinterest  |  Youtube  |  Vimeo
Share your photos by tagging us and using the hashtag #EGGhead4Life.


In Atlanta? Come visit Big Green Egg headquarters, including our retail showroom, the History of the EGG Museum and Culinary Center!  3786 DeKalb Technology Parkway, Atlanta, GA 30340.

importing value from MS excel into MS access

SangKisukeSangKisuke Posts: 10
edited 4:03AM 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

  • FlaPoolmanFlaPoolman Posts: 11,675
    350 indirect to 140° ;)
  • Carolina QCarolina Q Posts: 12,858
    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!

                                                                …Unknown

    Michael 
    Central Connecticut 

  • hurm?
  • e=mc2
  • FlaPoolmanFlaPoolman Posts: 11,675
    I used both fingers :P
  • FlaPoolmanFlaPoolman Posts: 11,675
    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
  • stikestike 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
  • stikestike 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
  • WessBWessB Posts: 6,937
    LOL...man we do that all the time....you can figure it out..
  • LDDLDD 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.. :)
  • 2Fategghead2Fategghead Posts: 9,623
    How do you cook on your Big Green Egg? What is your favorite rub? What part of world do you live in? :)
  • hornhonkhornhonk Posts: 3,841
    It appears he..she?..ate too many coco-puffs.
  • LDDLDD Posts: 1,225
    check your e-mail...
    context is important :)
  • 2Fategghead2Fategghead Posts: 9,623
    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. :)
  • LDDLDD 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 :)
  • TNmikeTNmike Posts: 643
    If you can't see the problem I'll be damn if I'm gonna' tell ya. :angry:
  • FidelFidel 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.
  • FidelFidel 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
  • FidelFidel 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 QCarolina Q Posts: 12,858
    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!

                                                                …Unknown

    Michael 
    Central Connecticut 

Sign In or Register to comment.
Click here for Forum Use Guidelines.