Access calling Excel macro "Run-time error '9': Subscript out of range" 1

[08211987]

Hi,
I am trying to call an excel macro from a Access 2010 DB and am getting a "Run-time error '9': Subscript out of range" at the obj.Run statement. I am certain it is how I am referencing the macro. I've tried changing it multiple ways and the errors just get worse:
Dim macrofile As String
macrofile = "\\somedirectory\Builder_Combiner.xlsm"
'please note this file has a Sub Combiner() (a macro called Combiner) that is listed under the MS Excel Objects as Sheet2(Combiner)
Dim obj As Object
Set obj = GetObject(macrofile)
obj.Application.Visible = True
obj.Application.Workbooks(1).Activate
obj.Windows(1).Visible = True
obj.Run Workbooks(1).Sheets("Combiner").Combiner
obj.ActiveWorkbook.Close (False)
obj.Quit
Set obj = Nothing

I found this code on the internet and it actually worked a few years ago but it now stops immediately at x1.Visible with "Run-time error '424': Object required". I think it is the changes is Access & Excel versions from today to then.
Dim x1 As Object
Set x1 = CreateObject("Excel.Application")
x1.Workbooks.Open ("\\somedirectory\Builder_Combiner.xlsm")
xl.Visible = True
xl.Run "Combiner"
xl.ActiveWorkbook.Close (True)
xl.Quit
Set xl = Nothing

Any assistance would be appreciated in helping me understand the syntax of how to reference the macro.
Thanks!

 

[SkipVought]

Hi,

You declared x1 (that is a numeral one)

Then you use xl (that is a character el)

You should have

Option Explicit set from your Tools menu item. This would have indicated to you your typo.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[08211987]

Skip, wow that was a fast response! Thanks! This prompted me to change my font..
Thanks again!

 

[Andrzejek]

Wow, good eye Skip

08211987, you may want to consider to NOT repeat the object and use With statement:

[pre]
Dim x1 As Object
Set x1 = CreateObject("Excel.Application")
With x1
.Workbooks.Open ("\\somedirectory\Builder_Combiner.xlsm")
.Visible = True
.Run "Combiner"
.ActiveWorkbook.Close (True)
.Quit
End With
Set x1 = Nothing
[/pre]
Less chances for your initial issue.
And [tt]Option Explicit[/tt] is a MUST - IMO.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[08211987]

Ok thanks Andy.

 

[SkipVought]

...or...

With CreateObject("Excel.Application")
  .Workbooks.Open ("\\somedirectory\Builder_Combiner.xlsm")
  .Visible = True
  .Run "Combiner"
  .ActiveWorkbook.Close (True)
  .Quit
End With

More than one way to skin a cat!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]