MS Access VBA: Reference Excel Application Object created in separate Module

Question

This seems like it should be an easy one but I'm stuck.

I'm running a VBA script in Access that creates a 40+ page report in Excel.

I am creating an Excel Application Object using Early Binding:

Public obj_xl As New Excel.Application

Here is an example of how I am referencing the object:

With obj_xl
  .Workbooks.Add
  .Visible = True
  .Sheets.Add
  .blahblahblah
End With

The problem is that the procedure has become too large and I need to break the code up into separate modules.

If I try to reference the Excel Application Object from a different module than it was created in, it throws an error ("Ambiguous Name").

I'm sure I could do something with Win API but that seems like it would be overkill.

Any thoughts? Thanks

How are you referencing from another module? Please post a little code. — Fionnuala
– Fionnuala, Commented Aug 1, 2012 at 21:11
Hey Remou- I am refrencing it the same way I am in the example code above (using the With statement) but I am not declaring a new excel object. Let me know if that still isn't clear and I'll add some sample code. thx — UberNubIsTrue
– UberNubIsTrue, Commented Aug 1, 2012 at 21:22
After checking @SeanCheshire's answer, consider passing the excel sheet or workbook as an object to your next procedure. — Fionnuala
– Fionnuala, Commented Aug 1, 2012 at 21:26
@Remou - This sounds promising, unfortunately I have run so I can't test it right now. I will first thing in the morning and post back with the results. — UberNubIsTrue
– UberNubIsTrue, Commented Aug 1, 2012 at 21:36
Oh man I feel like a n00b! Remou - I just tested your suggestion at home and it works perfecty! I am passing the object, as you suggested, as an argument to the new subroutine - ie Sub newRoutine(ByVal xlObject as Object). Thank you for the help on this! Much appreciated. If you want to post it as an answer, I will try to accept, assuming I can with my rep. — UberNubIsTrue
– UberNubIsTrue, Commented Aug 1, 2012 at 23:55

SeanC · Accepted Answer · 2012-08-01 21:16:46Z

1

this is the type of situation that can cause the error "Ambiguous Name"

Function Split(s As String) 
    MsgBox s 
End Function 
Function Split(s As String) 
    MsgBox s 
End Function

I know the example is trivial, but what you are looking for is a function , an object and/or a form control with the same names.

answered Aug 1, 2012 at 21:16

SeanC

16k5 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

UberNubIsTrue Over a year ago

thank you for the suggestion...I was able to work around my issue by passing the object as an argument to the new sub. But if I run into the ambiguous name error again, I'll know to give the Split function a try. thx

PowerUser Over a year ago

@UberNubIsTrue, He's not suggesting that function specifically. He's suggesting that you have 2 variables or functions with the same name. In Sean's example, calling Split() would result in an error since the compiler doesn't know which one to choose.

PowerUser · Accepted Answer · 2012-08-01 21:27:07Z

0

If you convert your declaration to Global, you can reference it in all your modules. For example, in one module, put this at the top:

Global obj_xl As Excel.Application

Then in an another module,

Sub xx()
     Set obj_xl = New Excel.Application
     Debug.Print obj_xl.Name
End Sub

answered Aug 1, 2012 at 21:27

PowerUser

11.8k20 gold badges67 silver badges103 bronze badges

2 Comments

Fionnuala Over a year ago

You have to be careful to capture all errors with a global variable, unhandled errors will clear them.

UberNubIsTrue Over a year ago

@PowerUser - thx for the feedback...On the first run this did not solve the issue but I will test again tomorrow morning to see if I can get it to work.

Collectives™ on Stack Overflow

MS Access VBA: Reference Excel Application Object created in separate Module

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related