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?
mldaigle1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Application.Run will let you run a macro in another workbook. You pass the name of the workbook and its macro as text. If that other workbook name includes space characters, you must enclose it in single quotes.
Sub Dashboarder()
Dim wb As Workbook
Application.ScreenUpdating = False
Application.EnableEvents = False    'Optional: Don't let event macros run

Set wb = Workbooks.Open("C:\DataRefresh1.xlsm")
Application.Run "'DataRefresh1.xlsm'!Sheet9.ConnectSqlServer"   'Single quotes required if workbook name includes space characters
wb.Close Savechanges:=False     'Don't display warning dialog if workbook contents were changed. Just close it.

Set wb = Workbooks.Open("C:\DataRefresh2.xlsm")
Application.Run "'DataRefresh2.xlsm'!Sheet20.ConnectSqlServer"   'Single quotes required if workbook name includes space characters
wb.Close Savechanges:=False     'Don't display warning dialog if workbook contents were changed. Just close it.

Set wb = Workbooks.Open("C:\DataRefresh3.xlsm")
Application.Run "'DataRefresh3.xlsm'!Sheet3.ConnectSqlServer"   'Single quotes required if workbook name includes space characters
wb.Close Savechanges:=False     'Don't display warning dialog if workbook contents were changed. Just close it.

Application.EnableEvents = True     'Turn event macros back on (if they were turned off above)
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
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

0
mldaigle1Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.