INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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!

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

Wow, good eye Skip smile

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

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
 
Less chances for your initial issue.
And Option Explicit is a MUST - IMO.

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

(OP)
Ok thanks Andy.

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

...or...

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close