CODEWITHSUNDEEP
vbaexcel
davidb
davidb

Reputation: 273

On workbook open, Excel Macro to refresh all data connections sheets and pivot tables and then export the pivot to csv

I have an Excel File which has CSV Data sources and Pivot tables, I want to refresh all the data sources and pivot tables automatically and export one pivot table as CSV on opening the excel file.

I tried the below code, but this code export the CSV file before the data getting refreshed.

please help with a solution. Thanks in advance.

Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
    Run "Macro1"
End Sub


Sub Macro1()

 Dim ws As Worksheet, newWb As Workbook
 Dim SaveToDirectory As String

 SaveToDirectory = "C:\Macro\"

 Application.ScreenUpdating = False
 For Each ws In Sheets(Array("locationwise"))
     ws.Copy
     Set newWb = ActiveWorkbook
     With newWb
        .SaveAs SaveToDirectory & ws.Name, xlCSV
        .Close (False)
     End With
 Next ws
Application.ScreenUpdating = True
Application.DisplayAlerts = False
End Sub

Upvotes: 2

Views: 26541

Answers (1)

R3uK
R3uK

Reputation: 14547

A simple DoEvents should do the trick! ;)

Try this : 

Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
    DoEvents
    Run "Macro1"
End Sub

And if it's not, just add this line after the DoEvents : 

Application.Wait(Now + TimeValue("0:00:05"))

This will put on hold the execution of the code, here for 5 seconds!

If you want to launch the save parts once a specific range has been modified, place your that code into the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Me.Range(Rg_To_Check)) Is Nothing Then
    'Not in range
Else
    'In range to check
   Run "Macro1"
End If
End Sub

And get rid of the Run "Macro1" in the Workbook_Open() event.

Also, be careful, because your last line is Application.DisplayAlerts = False you won't have alerts afterwards, you should use it like this instead :

Sub Macro1()

 Dim ws As Worksheet, newWb As Workbook
 Dim SaveToDirectory As String

 SaveToDirectory = "C:\Macro\"

 Application.DisplayAlerts = False
 Application.ScreenUpdating = False
 For Each ws In Sheets(Array("locationwise"))
     ws.Copy
     Set newWb = ActiveWorkbook
     With newWb
        .SaveAs SaveToDirectory & ws.Name, xlCSV
        .Close (False)
     End With
 Next ws
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Upvotes: 2

Related Questions