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

Topic

New Reply

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!!!

Reply | Quote

Replies

In order to get data from Excel, you need to do the following:

• In any module, for instance the form module, select Tools/References…
• Look for Microsoft Excel 8.0 Object Library and check the box to the left of that.
• Click OK.
• 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(“sheetname”).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 145156)

Reply | Quote

Hans:

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

any idea of what could be happening?

thank you for the help

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

REALLY DUMB!!!!

the answer is

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

Your help has been excellent!

Reply | Quote