Macro Excel - Open, Run macro, Copy / Paste, Close

Hello again Experts,

Thanks for the experts that help me with the macro  Dashboarder and CopySheetData.  I probably should write my full question first instead on separate it in two questions because now, i am back to square one.



Let's start over

I have a Dashboard in a workbook (Dashboard).  The data to populate this Dashboard come from 3 different workbooks (Data_Dump, Graph_View, Tech_Calc)

Those 3 workbooks are VBA password protected, no modification allowed.  So since I am unable to work with the sources files, I have to work in my Dashboard in order to retrieve data and graphs.

Here below what I would like to be able to do from a macro in my Dashboard:


- from the active workbook Dashboard:
      - open the first workbook (Data_Dump)
      - run the macro to retrieved data (Data_Dump.xlsm'!Feuil6.ConnectSqlServer)
      - copy Data_Dump.xlsm sheets(IN_Data,CH_Data,WO_O,WO_B,WO_H,PR), starting on Cell A2 into the same sheet in the activeworkbook Dashboard, starting in A2 also
               (I will need to clean up the data after since I only need the data from the current year & month)
      - close file Data Dump

- from the active workbook Dashboard:
     - open the second workbook (Graph_View)
     - run the macro to retrieved data and update graphs(Graph_SLO.xlsm'!Feuil11.ConnectSqlServer)
     - copy graph found on sheet INSLO1 to the activeworkbook Dashboard.Sheet(INMonth), in cell A1 with the specific chart size (H: 10.5cm x 24.37cm)
     - copy graph found on sheet INSLO2 to the activeworkbook Dashboard.Sheet(INMonth), in cell A25 with the specific chart size (H: 10.5cm x 24.37cm)
     - copy graph found on sheet HOSLO1 to the activeworkbook Dashboard.Sheet(HOMonth), in cell A1 with the specific chart size (H: 10.5cm x 24.37cm)
     - copy graph found on sheet HOSLO2 to the activeworkbook Dashboard.Sheet(HOMonth), in cell A25 with the specific chart size (H: 10.5cm x 24.37cm)
     - copy Table found on sheet TargetSLO1 (range A1:E7) to the activeworkbook Dashboard.Sheet(TargetSLO1, range A1:E7)
     - close file Graph_View


- from the active workbook Dashboard:
      - open the third workbook (Tech_Calc)
        - run the macro to retrieved data and update table(Tech_Cacl.xlsm'!Feuil9.ConnectSqlServer)
        - copy table found on sheet TeamView (range A6:J21) to the activeworbook Dashbook.Sheet(TargetSLO1), range A11:J25





The two macros that you kindly gave me are working fine separatly, but when I am trying to merge them, it does not work like I want.  Find below the macros:




Sub Dashboarder()
Dim wb As Workbook
Application.ScreenUpdating = False
Application.EnableEvents = False    

Set wb = Workbooks.Open("C:\Data_Dump.xlsm")
Application.Run "'Data_Dump.xlsm'!Feuil6.ConnectSqlServer"  
wb.Close Savechanges:=False    

Set wb = Workbooks.Open("C:\Graph_SLO.xlsm")
Application.Run "'Graph_SLO.xlsm'!Feuil11.ConnectSqlServer"  
wb.Close Savechanges:=False    

Set wb = Workbooks.Open("C:\Tech_Cacl.xlsm")
Application.Run "'Tech_Cacl.xlsm'!Feuil9.ConnectSqlServer"  
wb.Close Savechanges:=False    

Application.EnableEvents = True  
End Sub







Sub CopySheetData()

Dim wbDest As Workbook
Dim wbSource As Workbook

Set wbSource = Workbooks("C:\Data_Dump.xlsm")
Set wbDest = ActiveWorkbook
With wbSource
    .Sheets("IN_Data").UsedRange.Cells.Offset(1, 0).Copy Destination:=wbDest.Sheets("IN_Data").Range("A2")
    .Sheets("CH_Data").UsedRange.Cells.Offset(1, 0).Copy Destination:=wbDest.Sheets("CH_Data").Range("A2")
    .Sheets("WO_O").UsedRange.Cells.Offset(1, 0).Copy Destination:=wbDest.Sheets("WO_O").Range("A2")
    .Sheets("WO_B").UsedRange.Cells.Offset(1, 0).Copy Destination:=wbDest.Sheets("WO_B").Range("A2")
    .Sheets("WO_H").UsedRange.Cells.Offset(1, 0).Copy Destination:=wbDest.Sheets("WO_H").Range("A2")
    .Sheets("PR").UsedRange.Cells.Offset(1, 0).Copy Destination:=wbDest.Sheets("PR").Range("A2")
End With

End Sub


***********************************

I also uploaded sample of the mentioned files.  Hope you will be able to help me.

/mld
Tech_Calc.xlsm
Data_Dump.xlsm
Graph_View.xlsm
Dashboard.xlsm
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.

gowflowCommented:
Now your talking .... as when we first requested you to post a sample it was not for nothing will look at this if no one beats me to it.

Rgds/gowflow
0
gowflowCommented:
I have a question:

I don't seem to get clear on this:
run the macro to retrieved data (Data_Dump.xlsm'!Feuil6.ConnectSqlServer)

I do not see any sheet Feuil6 in this workbook and do not see any macro there. Actually in all the workbook you posted there are no macros. As a matterof fact all your sheets (Feuil6 in Data_Dump, Feuil11 in Graph_View and Feuil9 in Tech_Calc are all missing in the workbook you attached).

I take it that the macro you are talking about are the one you posted in the question here.

I worked on the CopySheetData Macro and adapted it to the workbook Dashboard.

Simply open the attachement and make sure your other files are in the same directory as the one you will save here and run the macro CopySheetData and tell me if it give the desired result.

Will worry about the rest later. Lets take it one at a time.
rgds
gowflow
Dashboard-V01.xlsm
0
mldaigle1Author Commented:
Hi GowFlow,

First step done and working fine.  In order to use only 1 macro, I add the execution of the macro of the external file:


sPath = ActiveWorkbook.Path
Set wbDest = ActiveWorkbook
Set wbSource = Workbooks.Open(sPath & "\Data_Dump.xlsm")
Application.Run "'Data_Dump.xlsm'!Feuil6.ConnectSqlServer"       <--------------------------------  YES!!

'---> Clean Existing Data in Destination prior copy
With wbDest
    .Sheets("IN_Data").Range("2:" & Rows.Count).EntireRow.Delete
.
.
.
etc...




Work like a charm... so... now we are at step 2?  Again  I will open the external file, run the macro to refresh data, copy what i need in the Dashboard and close the external file...


:)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

gowflowCommented:
Sorry I don't understand what your talking about.

What is this??
Application.Run "'Data_Dump.xlsm'!Feuil6.ConnectSqlServer"       <--------------------------------  YES!!

and when you say:
Work like a charm... so... now we are at step 2?  Again  I will open the external file, run the macro to refresh data, copy what i need in the Dashboard and close the external file...

Why you want to open a file and copy/paste we can do this thru code if you provide the sub called: ConnectSqlServer as do not see it any where.

gowflow
0
mldaigle1Author Commented:
Sorry GowFlow, it is sometime hard for me to express my self in English.

unfortunately, the external files are VBA password protected.  That's why I open and run the sub in the external file, in order to retrieve data.
0
gowflowCommented:
Anything else needed then ?
gowlfow
0
mldaigle1Author Commented:
Step 2 ?   Open the second external file then Copy a graph to my dashboard in specific sheet, starting in specific cell which specific size, like describe on top of the thread, is this possible
0
gowflowCommented:
Ooops I mised that you are correct. Will do
gowflow
0
gowflowCommented:
OK here is your solution sorry it took sometime but here is how it works.

1) I created 3 Macroes
CopyData_Dump
CopyGraph_View
CopyTechCalc

in each of these 3 it performs the requested tasks for those specific files you will only need to add prior to each starting module like you did before the update sql whatever.

2) I Created a Main sub that will be called by a button that is in sheet TargetSLO1 you only need to activate this button and it will ask you if you want to proceed with the update you may say no if you are not ready and nothing will happen by saying yes it will launch sequentially each of the previous 3 Subs and once terminated it will advise you that all is done. This sub is called CopySheets

NOTE
I followed your instructions and set your graphs as close as 10.5 and 24.37 and had a challenge as you need cm and the settings is in pixels and Inch. But the comment is not here it is in the Margin of your sheets INMonth and HOMonth if you do a print preview you will see that they are off and they require 4 pages instead of 1 I will leave it to you to play with your margins to get it to what you want if you need help I can assist in this.

Please check this version and sorry again for the delay.
Rgds/gowflow
Dashboard-V02.xlsm
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
mldaigle1Author Commented:
let me test it and i'll get back to you.  At this moment, i do have problem with the graph that does not copy a shape (blue horizontal line) in 2 graphs.

in the mean time, have a nice weekend,

xxx
/mld
0
gowflowCommented:
You too hv a nice weekend and let me know outcome.
gowflow
0
mldaigle1Author Commented:
My Customer seem to be very happy with the result.  So for me, it is a success 'cause of you

Thanks alot,

xxx

/mld
0
gowflowCommented:
Your welcome. great and glad I could help.
gowflow
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.