Openning Excel from Access 97

Can anyone suggest a useful article / reference book which details how to open and close Excel worksheets from Access 97.

I have copied some code from the help text into my database, but I have two probblems. One I don't really understand what the code does, and I would like to understand what is happenning. Two, the code checks if Excel is running, if not it opens it, then (with my worksheet name included) it opens the file I require; but if Excel is open but without a blank sheet in the workspace, I get an error.

Answer this question

Openning Excel from Access 97

Frans Bouma - C# MVP
Per our support engineer:

I understand you want some code to open and close Excel workbook from Access 97. Firstly, I need to say Access 97 is not supported anymore. We only support Access 2000 and above.

http://support.microsoft.com/lifecycle/ p1=1269

Therefore, I can only try to provide some suggestions, though it is violating the policy somewhat. To open and close Excel workbook from Access 97 or above, we usually use VBA code. Do you have experience on using VBA If not, here is some information on VBA.

http://spreadsheets.about.com/cs/exceladvanced/a/vba1index.htm

http://lacher.com/toc/tutvba1.htm

Here is a code snippet to open an Excel workbook and close it (Access 2003 and Excel 2003). Then close Excel application.

Dim xlObj As Excel.Application
Dim xlWbk As Excel.Workbook

' open excel, create excel application object
Set xlObj = CreateObject("Excel.Application")
' open your workbook
Set xlWbk = xlObj.Workbooks.Open("c:\1.xls")

' modify the workbook

' close workbook

xlWbk.Close SaveChanges:=True
Set xlWbk = Nothing

' close Excel
xlObj.Quit
Set xlObj = Nothing

Here are reference to the object used in above code

Application Object [Excel 2003 VBA Language Reference]

http://msdn.microsoft.com/library/en-us/vbaxl11/html/xlobjApplication1_HV05199211.asp

Workbook Object [Excel 2003 VBA Language Reference]
http://msdn.microsoft.com/library/en-us/vbaxl11/html/xlobjWorkbook1_HV05205955.asp

-brenda (ISV Buddy Team)
Openning Excel from Access 97

Answer this question