Opening Excel via Access

[Dave]

Hi All

I want to open an Excel file, change cells and the save the file via Access.
This code works fine at home (Running Windows XP & MS Office XP) but when I
use the same code at work (Windows NT MS Office 97) it errors. It first
errors at the first line of code, Refering to line "SetxlApp =
CreateObject("Excel.Application")" saying xlApp is not dimensiond, I then
Dim xlApp as Application. Then it errors at line "Set xlBook =
xlApp.workbooks.Open("C:\Test.XLS")". At this stage i'm stuck on what to do.

Here's the code:


Public Sub q()
On Error GoTo MError

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.workbooks.Open("C:\Test.XLS")
Set xlSheet = xlBook.Sheets("Sheet1")

Set xlSheet = Nothing

xlBook.Save
xlBook.Close

Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End
MError:

MsgBox (Err.Description), vbCritical
End Sub

 

[merjet]

This is a "shot in the dark" since I can't replicate the
environment you described, but you could try this
alternative code.

Set xlApp = CreateObject("Excel.Application")
With xlApp.Application
.Workbooks.Open("C:\Test.XLS")
.Visible = False
End With
Set xlSheet = xlApp.Workbooks("Test.XLS").Sheets("Sheet1")

Good luck,

Merjet