Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form: code to read a value from a cell in excel 97 (97)

    In my datbase, I have a form that is linked to a table. I want to read a cell value in an excel 97 spreadsheet, and assign it to a text box in the access form, so it will be added to the access table automatically.

    The form has a text box that lets me input the excel filename, and another one that will have the sheet name. The value will allways be read from cell "e21". (format: number, fixed 2 decimals)

    The excel file will be open at the moment of doing the operation.

    I need the code to assign the value to my text box, for example:

    Text1 = workbooks ("filename.xls").worsheets("sheetname").Range("e21 ").Value
    (Itried that , but it yields an error message)

    I already have a commandbutton, that when clicked, will run the subroutine.

    Thanks for the help!!!
    Guillermo

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form: code to read a value from a cell in excel 97 (97)

    In order to get data from Excel, you need to do the following:
    <UL><LI>In any module, for instance the form module, select Tools/References...
    <LI>Look for Microsoft Excel 8.0 Object Library and check the box to the left of that.
    <LI>Click OK.
    <LI>Next, in the OnClick routine of the button, put something like the following

    Dim objXL as Excel.Application
    Set objXL = GetObject(, "Excel.Application")
    Text1 = objXL.Workbooks("filename.xls").Worksheets("sheetn ame").Range("E21").Value
    Set objXL = Nothing[/list]GetObject assumes that - as you mentioned - Excel is active when you call this. Otherwise, you need to use CreateObject. (See my reply to another question about Excel in Access in <!post=Post 145156, 145156>Post 145156<!/post>)

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form: code to read a value from a cell in excel 97 (97)

    Hans:

    I inserted the code , and I get: Type mismatch (Error 13)

    any idea of what could be happening?

    thank you for the help
    Guillermo

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form: code to read a value from a cell in excel 97 (97)

    Try splitting the assignment into smaller bits:

    Dim objXL As Excel.Application
    Dim objWB As Excel.Workbook
    Dim objWS As Excel.Worksheet
    Dim objRG As Excel.Range
    Dim myVal
    ' If you wish, you can put Dim myVal As ...
    ' where ... is the data type of the field you want to fill

    Set objXL = GetObject(, "Excel.Application")
    Set objWB = objXL.Workbooks("filename.xls")
    Set objWS = objWB.Worksheets("sheetname")
    Set objRG = objWS.Range("E21")
    myVal = objRG.Value
    MsgBox myVal
    Text1 = myVal

    Now, run the code and see where the error occurs. If it's in the last line, it probably means that the text box Text1 is bound to a field of a type not compatible with the value from the spreadsheet.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form: code to read a value from a cell in excel 97 (97)

    Your code works fine!!! (the one in your first answer too!!!

    The problem was that I tried...:

    Set objWB = objXL.Workbooks(text16)
    Set objWS = objWB.Worksheets(text19)

    where text16 and text19 contain the reference in text to the filename and sheet name , that is stored in the access table. what is wrong with that syntax?

    (my questions must by very very dumb for access and excel experts like you... sorry!!!)

    Thank you
    Guillermo

  6. #6
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form: code to read a value from a cell in excel 97 (97)

    REALLY DUMB!!!!

    the answer is

    Set objWB = objXL.Workbooks(text16.value)
    Set objWS = objWB.Worksheets(text19.value)

    Your help has been excellent!
    Guillermo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •