please help
 

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




--
Please type david.com inplace of no.thanks.
Sorry for the inconvenience but fed up of spam mail.

please help
 

the one where the error occurs has

Option Explict

this requires you to declare variables. That is always a good practice.

Public Sub q()
Dim xlApp as Object
Dim xlBook as Object
dim xlSheet as Object

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
Exit Sub
MError:

MsgBox (Err.Description), vbCritical
End Sub

It is usually a bad idea to use END in your sub since it clears global
variables.

However, if you have problems with Excel remaining in the Task list (and not
shutting down), you might put it back.

--
Regards,
Tom Ogilvy



Dave wrote in message
...
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




--
Please type david.com inplace of no.thanks.
Sorry for the inconvenience but fed up of spam mail.