Help with Spreadsheet

[Shirley]

Does anyone know how to use a Excel worksheet in Access, or how to reference a field in the Excel worksheet.

 

[codehead]

Shirley,<br>
<br>
To the best of my knowledge, you have to import the spreadsheet into Access.<br>
<br>
Bob

 

[Jonathan]

You can use ActiveX and other fancy stuff to do it:<br>
<br>
Add a reference to the Microsoft Excel object library using the references window<br>
You can then control excel from your application by means of objects. So a function to load a worksheet called C:\sheet.xls and set the value of cell C5 to &quot;hello&quot; would be something like this<br>
<br>
Sub Test()<br>
<br>
dim objXLApp as new Excel.Application<br>
dim objXLWorkbook as Excel.Workbook<br>
dim objXLSheet as Excel.Sheet<br>
<br>
<br>
set objXLWorkbook = objXLApp.Workbooks.Open(&quot;C:\sheet.xls&quot<br>
set objXLSheet = objXLWorkbook.Worksheets(0)<br>
<br>
objXLSheet.Cells(&quot;C5&quot = &quot;hello&quot;<br>
<br>
objXLApp.Visible = true<br>
'when you load excel by creating a new object for it, it is not visible by default<br>
<br>
end sub<br>
<br>
All the Excel object will appear in the object browser. Basically, anything you can do with the mouse in excel, you can do from your code.<br>
<br>
Hope this helps<br>
<br>
Jonathan