asked on

Excel Macro - Opening others workbooks in background and run macros from main workbook

Good Day Experts,

Here my situation :

I have a dahsboard that retrieve data from 3 other excel files. The other excel files have a VBAProject password protected on, so cannot copy the sql macro. So what I would like to do is the following:

From the Dashboard file, run a macro which will:
- Open an excel data file in background, execute in background the macro to refresh their data and this for the 3 data files I need.

Ex:

MyDashboard will open :
DataRefesh1, run macro DataRefresh1.xlsm'!Sheet9.ConnectSqlServer
DataRefesh2, run macro DataRefresh2.xlsm'!Sheet20.ConnectSqlServer
DataRefesh3, run macro DataRefresh3.xlsm'!Sheet3.ConnectSqlServer

Is this possible?

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

One thing I didn't mention, but perhaps should have, is that Sheet3, Sheet9 and Sheet20 in the suggested code are the code names of the worksheets rather than the tab names. The tab names are what you see at the bottom of the screen when viewing the worksheets. The code names are what you see in the VBA Project Explorer. The main benefit of using code names is that they cannot easily be changed by the user, so code that uses code names will be more robust than code that uses tab names to refer to a worksheet.

So how do you find out what is the code name for a worksheet? Here is a user-defined function that will tell you the code name for a worksheet if you know its tab name. Put the code in a regular module sheet in your dashboard workbook, just like a macro. You then use it with a worksheet formula like:
=GetCodeName("DataRefresh1.xlsm", "Business Summary")

Function GetCodeName(BookName As String, TabName As String)
GetCodeName = Workbooks(BookName).Worksheets(TabName).CodeName
End Function

Open in new window

mldaigle1

ASKER

Hi Byundt,

Your code is doing exactly what i wanted. Many Thanks!!

Regarding the information for the code names, like you mentioned, using the code names will be more robust than the code that uses tab names, i will then use the code names.

Big kiss!!

Thanks again!

/MLD