Opening an Excel file in Access VBA

[popper]

When I use

Set xlApplication = CreateObject("Excel.Application")
.
.
.

Set xlWorkbook = xlApplication.Workbooks.Open("c:\CashFlow Template.xls")

I get an run time error. This worked fine for a while and then just stopped. It still works on some machines but not others??? The accompanying message is "Server threw an exception" and a big negative error number. The help comment relates to 'Automation' objects but I can't figure out the relevance of that. Can anyone help? It would be most appreciated.

With thanks

 

[ZmrAbdulla]

I am not sure..
A shot in the dark..
Did you upgrade MS Office in those PCs in which this code is not working?

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[PHV]

How are Dim'ed xlApplication and xlWorkbook ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[earthandfire]

Zameer, that shouldn't really be relevant because late-binding is being used, which, by its very nature "should" be version independent.

 

[andols]

Hi,

I guess it dosent help you, but I am using the code below and it works for me. Im writing the code in ESRI ArcGIS, maybe that matters.

Dim oExcel As Object
Dim excWorkbook As Object

Set oExcel = GetObject(, "Excel.Application")
If Err.Number <> O Then
Set oExcel = CreateObject("Excel.Application")
Set excWorkbook = oExcel.Workbooks.Open("C:\Projekt\AnlReg\data.xls")
oExcel.Visible = True
excWorkbook.Sheets("Blad3").Select
Else
excWorkbook = oExcel.Workbooks
excWorkbook.Sheets("Agön").Select
oExcel.Visible = True
End If
Err.Clear

But my problem is that if the excel-document is open the (Err.Number <> 0) = FALSE and then I cant get the active-woorkbook and I cant controll wich sheet that is visible.
Anyone having an idea.

Many thanx.

 

[popper]

Thank you for your responses but the problem is not resolved. Here is all the relevant code: I am getting desperate.


Dim xlApplication As Excel.Application
Dim xlWorkbook As Workbook
Dim xlWorksheet1 As Worksheet
Dim xlWorksheet2 As Worksheet
Dim xlRange1 As Excel.Range
Dim xlRange2 As Excel.Range


Set xlApplication = CreateObject("Excel.Application")


Set xlWorkbook = xlApplication.Workbooks.Open("c:\CashFlow Template.xls")

''' We crash at the previous line with a strange message "Server threw an exception"

Set xlWorksheet1 = xlWorkbook.Worksheets("Revenue")
Set xlWorksheet2 = xlWorkbook.Worksheets("Budget")
Set xlRange1 = xlWorksheet1.Range("A1:IV3000")
Set xlRange2 = xlWorksheet2.Range("A1:IV3000")

Can anyone help??

 

[Nikita6003]

Hiya,

shot in the dark - but do you perhaps have another instance of Excel running on the machines where you get the error (check all active threads)

I've had this happen to me as well & it turned out to be due to me not properly releasing my Excel objects. Went through the code & made sure I Quit all instances of Excel objects & set them to nothing. So in your example:
[code}
xlApplication.Quit
Set xlApplication = Nothing
[/code]

may help - may not. Let us know how you get on.

Cheers
Nikki

Cheers
Nikki
Look, mommy, I'm flying!

 

[TGandon]

This may be irrelevant but I had a similar problem using Excel 97 on XP.
The solution was to download and install the Office 97 service releases 1 & 2 from Microsoft.

Trevor