Please Help

[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

 

[Dick Kusleika]

Dave

If you're working in Access, then Dimming xlApp as Application is telling
the compiler that xlApp is an Access Application, not an Excel Application.

When it errors on the second line, is it the same as the first error? The
first error is likely because you are requiring variable declaration on your
work Access and not on your home Access. Putting Option Explicit at the top
of the module requires you to Dim all variables and is considered good
practice. You should be declaring all the variables you use with Dim.
Here's how your sub might look

Public Sub q()

On Error GoTo MError

Dim xlApp as Object
Dim xlBook as Object
Dim xlSheet as Object

'The rest of your code is fine

The example above shows you how to late-bind. You could also use
early-binding. Here's a page from my site that discusses early and late
binding from Excel to Outlook, but the principles are the same

http://www.dicks-clicks.com/excel/olBinding.htm

Before the error handling label, you use End. You might consider using Exit
Sub instead. End terminates all code execution, which is probably fine for
what you're doing. However, if this sub ever gets called from another sub,
Exit Sub will return control to the calling sub while End will stop
everything. You don't lose anything by using Exit Sub, but it may cause you
less problems in the future than End will.