paulette33
Technical User
Hi -
I have an excel file that linked to several external data sources. In the code, i have a "refreshAll" command. After the sheets refresh, the pivot table is refreshed, and then the sheets are copied/saved to a new file. Everything seems to work, however, it looks like it is moving through the code before the refresh has actually completed. Is there a command that i can use that will pause the code - make the refresh complete before it moves to the next step? I pasted a copy of the code below. Thanks for your help!!!
Paulette33
********************************************************
Private Sub btnRefresh_Click()
Dim strCell As String
Dim intCol As Integer
Dim intLastCol As Integer
Dim strFileName As String
On Error GoTo btnRefreshErr
'Refresh Data Source
ActiveWorkbook.RefreshAll
'Get reporting period
strFileName = "RPT04-0140_" & Format(Cells(3, 1).Value, "yyyymm")
'Refresh Pivot Table
With Sheets("Data")
.PivotTables("CpnData").RefreshTable
.Activate
End With
'Set Column Widths on Data Sheet
strCell = ActiveSheet.Range("Results").Find("HOSP_ID").Address(False, False)
ActiveSheet.Range(strCell).Select
intCol = ActiveCell.Column
strCell = ActiveSheet.Range("Results").Find("Grand Total").Address(False, False)
ActiveSheet.Range(strCell).Select
intLastCol = ActiveCell.Column
If Columns(intCol + 1).ColumnWidth <> 14.7 Then
For x = intCol + 1 To intLastCol - 1
Columns(x).ColumnWidth = (14.7)
Next x
End If
ActiveSheet.Range("A2").Select
'Set focus back to Execute sheet
Sheets("Execute").Select
'Copy sheets to new excel workbook
With Sheets(Array("Data", "Scenario Summary", "Client Detail"))
.Copy
End With
'Set pivot table on new workbook to not automatically refresh
ActiveWorkbook.Sheets("Data").PivotTables("CpnData").PivotCache.RefreshOnFileOpen = False
'Save new workbook
ActiveWorkbook.SaveAs Filename:= _
"\\Data\Projects\Public\Bayer Coupon Report\" & strFileName & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks(strFileName).Close
Sheets("Execute").Activate
MsgBox strFileName & " has been saved", vbOKOnly
btnRefreshExit:
Exit Sub
btnRefreshErr:
If Err.Number = 1004 Then
MsgBox "File folder specified does not exist!" & Chr(13) & _
"Please save " & ActiveWorkbook.Name & _
" to the appropriate location", vbOKOnly
Else
MsgBox "An unexpected error has occured" & Chr(13) & _
Err.Number & " - " & Err.Description, vbOKOnly
End If
End Sub
I have an excel file that linked to several external data sources. In the code, i have a "refreshAll" command. After the sheets refresh, the pivot table is refreshed, and then the sheets are copied/saved to a new file. Everything seems to work, however, it looks like it is moving through the code before the refresh has actually completed. Is there a command that i can use that will pause the code - make the refresh complete before it moves to the next step? I pasted a copy of the code below. Thanks for your help!!!
Paulette33
********************************************************
Private Sub btnRefresh_Click()
Dim strCell As String
Dim intCol As Integer
Dim intLastCol As Integer
Dim strFileName As String
On Error GoTo btnRefreshErr
'Refresh Data Source
ActiveWorkbook.RefreshAll
'Get reporting period
strFileName = "RPT04-0140_" & Format(Cells(3, 1).Value, "yyyymm")
'Refresh Pivot Table
With Sheets("Data")
.PivotTables("CpnData").RefreshTable
.Activate
End With
'Set Column Widths on Data Sheet
strCell = ActiveSheet.Range("Results").Find("HOSP_ID").Address(False, False)
ActiveSheet.Range(strCell).Select
intCol = ActiveCell.Column
strCell = ActiveSheet.Range("Results").Find("Grand Total").Address(False, False)
ActiveSheet.Range(strCell).Select
intLastCol = ActiveCell.Column
If Columns(intCol + 1).ColumnWidth <> 14.7 Then
For x = intCol + 1 To intLastCol - 1
Columns(x).ColumnWidth = (14.7)
Next x
End If
ActiveSheet.Range("A2").Select
'Set focus back to Execute sheet
Sheets("Execute").Select
'Copy sheets to new excel workbook
With Sheets(Array("Data", "Scenario Summary", "Client Detail"))
.Copy
End With
'Set pivot table on new workbook to not automatically refresh
ActiveWorkbook.Sheets("Data").PivotTables("CpnData").PivotCache.RefreshOnFileOpen = False
'Save new workbook
ActiveWorkbook.SaveAs Filename:= _
"\\Data\Projects\Public\Bayer Coupon Report\" & strFileName & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks(strFileName).Close
Sheets("Execute").Activate
MsgBox strFileName & " has been saved", vbOKOnly
btnRefreshExit:
Exit Sub
btnRefreshErr:
If Err.Number = 1004 Then
MsgBox "File folder specified does not exist!" & Chr(13) & _
"Please save " & ActiveWorkbook.Name & _
" to the appropriate location", vbOKOnly
Else
MsgBox "An unexpected error has occured" & Chr(13) & _
Err.Number & " - " & Err.Description, vbOKOnly
End If
End Sub