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

1
votes

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

2
excelms-accessvba
How are you referencing from another module? Please post a little code. - Fionnuala
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
After checking @SeanCheshire's answer, consider passing the excel sheet or workbook as an object to your next procedure. - Fionnuala
@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
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

2 Answers

1
votes

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.

0
votes

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