Runtime Error 438 -- in process that has always worked

[ThriftyFinanceGirl]

We have a process created by a previous employee that has never had issues
but is stopping on this line....
where we are setting the xlapp

var = xlapp.workbook.worksheet(1)

Any ideas what is wrong with this? The error is "object not supported" but
I believe it is since it has always worked before.

 

[ThriftyFinanceGirl]

Here is the actual code (stops on the Set xlSheet line)
=======================================
Dim xlApp As Variant
Dim xlBook As Variant
Dim xlSheet As Variant
On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
Set xlBook =
xlApp.Workbooks.Open("\\Nas01\dol_exch\Departments\Accounting\Kyle
Enns\SalesTax\Sales Tax Update Tools\Sales Tax Utility Central.xls")
Set xlSheet = xlApp.Workbooks.Worksheets(1)
xlApp.Application.Visible = False
' Buttonline
xlApp.Application.Worksheets(1).CmdVarWO.Font.Bold = True
xlApp.Application.Worksheets(1).CmdVarWO.Activate
xlApp.Application.Worksheets(1).CmdVarWO.Refresh
xlApp.Application.Visible = False
xlBook.Close False
Set xlApp = Nothing
Set xlBook = Nothing
===========================================

 

[Graham Mandeno]

Hi ThriftyFinanceGirl

Are you *sure* this exact code has worked before?

It doesn't look right to me. Workbooks is a collection, not an object. It
should be either:
Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
or
Set xlSheet = xlApp.ActiveWorkbook.Worksheets(1)

But since you already have an object xlBook, this would be better:
Set xlSheet = xlBook.Worksheets(1)

Also, I think that the incidences of xlApp.Application.Worksheets(1) further
down should instead just be xlSheet.

Finally, you should have xlApp.Quit before setting the object to Nothing.

Oh, and finally finally <g> you should declare the xlBlah variables "As
Object", not "As Variant".

 

[ThriftyFinanceGirl]

Well, I've been told that it has always worked. Again, this is "inherited"
code from a previous employee. Hopefully I'm going to do away with this app
he made entirely but until I get through creating the new one they are stuck
with using this. I will look into what you said as I am not very familiar
with programming Excel via Access. thank you so much for your help... and If
I have to delve into it further, I'm sure we will be in touch!!

Hi ThriftyFinanceGirl

Are you *sure* this exact code has worked before?

It doesn't look right to me. Workbooks is a collection, not an object. It
should be either:
Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
or
Set xlSheet = xlApp.ActiveWorkbook.Worksheets(1)

But since you already have an object xlBook, this would be better:
Set xlSheet = xlBook.Worksheets(1)

Also, I think that the incidences of xlApp.Application.Worksheets(1) further
down should instead just be xlSheet.

Finally, you should have xlApp.Quit before setting the object to Nothing.

Oh, and finally finally <g> you should declare the xlBlah variables "As
Object", not "As Variant".

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Here is the actual code (stops on the Set xlSheet line)
=======================================
Dim xlApp As Variant
Dim xlBook As Variant
Dim xlSheet As Variant
On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
Set xlBook =
xlApp.Workbooks.Open("\\Nas01\dol_exch\Departments\Accounting\Kyle
Enns\SalesTax\Sales Tax Update Tools\Sales Tax Utility Central.xls")
Set xlSheet = xlApp.Workbooks.Worksheets(1)
xlApp.Application.Visible = False
' Buttonline
xlApp.Application.Worksheets(1).CmdVarWO.Font.Bold = True
xlApp.Application.Worksheets(1).CmdVarWO.Activate
xlApp.Application.Worksheets(1).CmdVarWO.Refresh
xlApp.Application.Visible = False
xlBook.Close False
Set xlApp = Nothing
Set xlBook = Nothing
===========================================

 

[sfodom]

We are having the same exact problem. This has worked for 10 years but all of a sudden it doesn't. Maybe a patch issue?

 

[sfodom]

In fact, it still works on my machine, but my coworkers' ability to run it is changing workstation by workstation.