MS ACCESS VBA - Dynamic object names possible?

S_NGAN
S_NGAN used Ask the Experts™
on
In my Access DB code, I created 12 MS xcel objects to open 12 sheets at once.  However, based on a user input, I want to write to the sheet that corresponds to the user's input.  How can I dynamically change the the object name based on the user's input to write to a specified sheet?  Or do I have to repeat the same write code for each 12 sheets?  

Cose looks something like below. But the code to insert data is much larger...I dont want to repeat the same code 12 times for each object name...

Set objExcel_00 = CreateObject("Excel.Application")
Set objExcel_01 = CreateObject("Excel.Application")
Set objExcel_02 = CreateObject("Excel.Application")
Set objExcel_03 = CreateObject("Excel.Application")
Set objExcel_04 = CreateObject("Excel.Application")
Set objExcel_05 = CreateObject("Excel.Application")
Set objExcel_06 = CreateObject("Excel.Application")
Set objExcel_07 = CreateObject("Excel.Application")
Set objExcel_08 = CreateObject("Excel.Application")
Set objExcel_09 = CreateObject("Excel.Application")
Set objExcel_10 = CreateObject("Excel.Application")
Set objExcel_11 = CreateObject("Excel.Application")
...
...
...
 
Select Case PL_Region
                Case "00"
                    ' code to insert data into object  = "objExcel_00"
                Case "02"
                     ' code to insert data into object  = "objExcel_00"
                Case "03"
                     ' code to insert data into object  = "objExcel_00"
                Case "04"
                     ' and so on....
 End Select

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Use one additional variable in the actual insert logic.  Right before that insert logic have a small (relatively) case statement that assigns the master object variable to the specific excel instance to reference.

~bp
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
In other words, in concept, not exact syntax...

Select Case PL_Region
                Case "00"
                    obj_Excel = objExcel_00
                Case "01"
                    obj_Excel = objExcel_01
                Case "02"
                    obj_Excel = objExcel_02
                Case "03"
                     ' and so on....
 End Select

Then write your insert logic to only reference objExcel

~bp
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Depending on how many places in the code you need to do this you could also store the original set of object variables in an array, and then just reference them by the right index based on PL_Region.

~bp
billprew's idea is good, using an object variable as an actual variable. However, you need to use Set (and not the implicit Let):

    Set obj_Excel = objExcel_00

Another solution is to put the actual writing code into a Sub, and pass the object as reference:

Sub WriteToXL(pobjXL As Excel.Application)
    ' do your stuff
End Sub

and then

    Case "00": WriteToXL(objExcel_00)
    Case "01": WriteToXL(objExcel_01)

If the code is "long", it's a good idea to break it into smaller subroutines and functions, anyway.

This being said, why all the Excel instances? You can open several workbooks using one instance of the application. The code would be similar, only using Excel.Workbook objects instead of Excel.Application objects.

Cheers!
(°v°)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial