pilot1ks
10-28-2010, 07:01 AM
Hello,
I have an Excel 2007 macro that I want to open several other spreadsheets to consolidate into one. The individual sheets are linked to a stored procedure to refresh them with data.
My problem is the macro appears to proceed before the refresh is completed. I would like to see if there is not a way to code the macro to wait for the refresh to complete before it moves on to the next step.
Below is the portion of my code that is causing me grief. Any help you can give is greatly appreciated!!!!
Sub MTD_DATA_Load()
Dim Row_Index As Long
Dim Last_Loop_Row As Long
Dim Final_Row As Long
Dim FinalCol As Long
Dim Final_Position As Long
Dim Final_Sum_Start_Row As Long
Dim Nbr_Rows As Long
Dim Run_Date As String
Dim Run_Time As String
Dim Ship_Date As String
Dim Shipment_Date As String
Dim PauseTime, Start
PauseTime = 30 ' Set duration in seconds
Run_Date = "Report Date: " & Date
Run_Time = "Report Time: " & Hour(Now()) & ":" & Minute(Now())
Nbr_Rows = 0
Last_Loop_Row = 0
'Opens Workbooks and clear previous data and subtotals
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "Daily MTD Shipping Percentage Report SQL.xls"
'Clears the Location Only Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("MTD By Location").Select
Range("A4:AF60000").Select
Selection.ClearContents
'Clears the Brand Only Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("MTD Brand Only Summary").Select
Range("A4:AF60000").Select
Selection.ClearContents
Selection.RemoveSubtotal
'Clears the Brand by BU Summary Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("MTD Brands by BU Summary").Select
Range("A4:AF60000").Select
Selection.ClearContents
Selection.RemoveSubtotal
'Clears the MTD by BU Brand PRP5 Summary Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("MTD by BU Brand PRP5 Summary").Select
Range("A4:AF60000").Select
Selection.ClearContents
Selection.RemoveSubtotal
'Clears the Daily Shipments Summary Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("Daily Shipment Summary").Select
Range("A4:AF60000").Select
Selection.ClearContents
Selection.RemoveSubtotal
'Clears the Daily Short Shipments Details Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("DailyShort Shipments").Select
Range("A4:AF60000").Select
Selection.ClearContents
Selection.RemoveSubtotal
'Opens Output Spreadsheets from Access DB and copies data to to be used in the Report
'Opens the Location Only Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_MTDByLocationOnly.xlsx"
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_MTD_By_Location").TableStyle = ""
ActiveSheet.ListObjects("Table_MTD_By_Location").Unlist
Range("A2:Y" & Range("Y" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("MTD By Location").Select
Range("A4").Select
ActiveSheet.Paste
'Opens the Brand Only Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_MTDbyBrandOnlyDataOutput.xlsx""
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_MTD_Brands_Summary").TableStyle = ""
ActiveSheet.ListObjects("Table_MTD_Brands_Summary").TableStyle = Unlist
Range("A2:Y" & Range("Y" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("MTD Brand Only Summary").Select
Range("A4").Select
ActiveSheet.Paste
'Opens the EBU Brand Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_MTDByEBUBrandOnly_Output"
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_MTD_Brands_By_BU_Summary").TableStyle = ""
ActiveSheet.ListObjects("Table_MTD_Brands_By_BU_Summary").TableStyle = Unlist
Application.Wait (Now() + TimeSerial(0, 0, 20))
Range("A2:Z" & Range("Z" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("MTD Brands by BU Summary").Select
Range("A4").Select
ActiveSheet.Paste
'Opens the EBU Brand PRP5 Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_MTDByEBUBrandPRP5.xlsx"
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_MTD_by_BU_Brand_PRP5_Summary").TableStyle = ""
ActiveSheet.ListObjects("Table_MTD_by_BU_Brand_PRP5_Summary").TableStyle = Unlist
Application.Wait (Now() + TimeSerial(0, 0, 20))
Range("A2:AA" & Range("AA" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("MTD by BU Brand PRP5 Summary").Select
Range("A4").Select
ActiveSheet.Paste
'Opens the Daily Shipment Summary Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_Daily_Shipments_Summary.xlsx"
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_Daily_Shipment_Summary").TableStyle = ""
ActiveSheet.ListObjects("Table_Daily_Shipment_Summary").TableStyle = Unlist
Range("A2:AB" & Range("AB" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("Daily Shipment Summary").Select
Range("A4").Select
ActiveSheet.Paste
'Opens the Daily Short Shipment Details Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_Daily_Short_Ships.xlsx"
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_DailyShortShipments").TableStyle = ""
ActiveSheet.ListObjects("Table_DailyShortShipments").TableStyle = Unlist
Range("A2:AC" & Range("AC" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("DailyShort Shipments").Select
Range("A4").Select
ActiveSheet.Paste
I have an Excel 2007 macro that I want to open several other spreadsheets to consolidate into one. The individual sheets are linked to a stored procedure to refresh them with data.
My problem is the macro appears to proceed before the refresh is completed. I would like to see if there is not a way to code the macro to wait for the refresh to complete before it moves on to the next step.
Below is the portion of my code that is causing me grief. Any help you can give is greatly appreciated!!!!
Sub MTD_DATA_Load()
Dim Row_Index As Long
Dim Last_Loop_Row As Long
Dim Final_Row As Long
Dim FinalCol As Long
Dim Final_Position As Long
Dim Final_Sum_Start_Row As Long
Dim Nbr_Rows As Long
Dim Run_Date As String
Dim Run_Time As String
Dim Ship_Date As String
Dim Shipment_Date As String
Dim PauseTime, Start
PauseTime = 30 ' Set duration in seconds
Run_Date = "Report Date: " & Date
Run_Time = "Report Time: " & Hour(Now()) & ":" & Minute(Now())
Nbr_Rows = 0
Last_Loop_Row = 0
'Opens Workbooks and clear previous data and subtotals
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "Daily MTD Shipping Percentage Report SQL.xls"
'Clears the Location Only Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("MTD By Location").Select
Range("A4:AF60000").Select
Selection.ClearContents
'Clears the Brand Only Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("MTD Brand Only Summary").Select
Range("A4:AF60000").Select
Selection.ClearContents
Selection.RemoveSubtotal
'Clears the Brand by BU Summary Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("MTD Brands by BU Summary").Select
Range("A4:AF60000").Select
Selection.ClearContents
Selection.RemoveSubtotal
'Clears the MTD by BU Brand PRP5 Summary Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("MTD by BU Brand PRP5 Summary").Select
Range("A4:AF60000").Select
Selection.ClearContents
Selection.RemoveSubtotal
'Clears the Daily Shipments Summary Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("Daily Shipment Summary").Select
Range("A4:AF60000").Select
Selection.ClearContents
Selection.RemoveSubtotal
'Clears the Daily Short Shipments Details Tab data
Application.Wait (Now() + TimeSerial(0, 0, 2))
Sheets("DailyShort Shipments").Select
Range("A4:AF60000").Select
Selection.ClearContents
Selection.RemoveSubtotal
'Opens Output Spreadsheets from Access DB and copies data to to be used in the Report
'Opens the Location Only Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_MTDByLocationOnly.xlsx"
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_MTD_By_Location").TableStyle = ""
ActiveSheet.ListObjects("Table_MTD_By_Location").Unlist
Range("A2:Y" & Range("Y" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("MTD By Location").Select
Range("A4").Select
ActiveSheet.Paste
'Opens the Brand Only Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_MTDbyBrandOnlyDataOutput.xlsx""
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_MTD_Brands_Summary").TableStyle = ""
ActiveSheet.ListObjects("Table_MTD_Brands_Summary").TableStyle = Unlist
Range("A2:Y" & Range("Y" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("MTD Brand Only Summary").Select
Range("A4").Select
ActiveSheet.Paste
'Opens the EBU Brand Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_MTDByEBUBrandOnly_Output"
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_MTD_Brands_By_BU_Summary").TableStyle = ""
ActiveSheet.ListObjects("Table_MTD_Brands_By_BU_Summary").TableStyle = Unlist
Application.Wait (Now() + TimeSerial(0, 0, 20))
Range("A2:Z" & Range("Z" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("MTD Brands by BU Summary").Select
Range("A4").Select
ActiveSheet.Paste
'Opens the EBU Brand PRP5 Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_MTDByEBUBrandPRP5.xlsx"
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_MTD_by_BU_Brand_PRP5_Summary").TableStyle = ""
ActiveSheet.ListObjects("Table_MTD_by_BU_Brand_PRP5_Summary").TableStyle = Unlist
Application.Wait (Now() + TimeSerial(0, 0, 20))
Range("A2:AA" & Range("AA" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("MTD by BU Brand PRP5 Summary").Select
Range("A4").Select
ActiveSheet.Paste
'Opens the Daily Shipment Summary Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_Daily_Shipments_Summary.xlsx"
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_Daily_Shipment_Summary").TableStyle = ""
ActiveSheet.ListObjects("Table_Daily_Shipment_Summary").TableStyle = Unlist
Range("A2:AB" & Range("AB" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("Daily Shipment Summary").Select
Range("A4").Select
ActiveSheet.Paste
'Opens the Daily Short Shipment Details Spreadsheet
Application.Wait (Now() + TimeSerial(0, 0, 5))
Workbooks.Open "SQL_Daily_Short_Ships.xlsx"
ActiveWorkbook.RefreshAll
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
ActiveSheet.ListObjects("Table_DailyShortShipments").TableStyle = ""
ActiveSheet.ListObjects("Table_DailyShortShipments").TableStyle = Unlist
Range("A2:AC" & Range("AC" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
'Pastes the new data into the Report
Windows("EMD Daily MTD Shipping Percentage Report SQL.xls").Activate
Sheets("DailyShort Shipments").Select
Range("A4").Select
ActiveSheet.Paste