2

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
R3uK
  • 14,417
  • 7
  • 43
  • 77
davidb
  • 263
  • 5
  • 10
  • 23

1 Answers1

2

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
R3uK
  • 14,417
  • 7
  • 43
  • 77
  • Hi, i tried it, the 'Application.wait' command works. but what if my data is huge and the refreshing time takes more than 5 seconds? i will not be able to freeze the hold time because i do not know how much time it is going to take for refreshing huge data each time. please advice. – davidb Aug 04 '15 at 11:19
  • The `DoEvents` wasn't enough? Because most of the time, it should be! If it is not, deactivate the **background refresh** for your connections and this will work smoothly. And if it is still not enough, you'll have to try with a longer wait time I'm afraid... – R3uK Aug 04 '15 at 11:22
  • i have deactivated the background refresh and tried. it is not working. i think i need to work on longer time wait.is there any alternative solution for this? can we export only when there is a change on a particular cell value? – davidb Aug 04 '15 at 11:37
  • i used application.intersect, thank you very much. it worked. – davidb Aug 05 '15 at 11:14
  • Glad I could help! ;) – R3uK Aug 05 '15 at 12:17