I have a small Excel file with VBA. We use the task scheduler in Windows to open this small file which then runs a macro to open, refresh, and save various reports that we have automated. The file works fine in Windows 2000 and Excel 2003, but we are converting to Windows 7 and Excel 2010. The task scheduler opens the file and the file then opens the appropriate report. It looks like it refreshes, and then it deletes the prior report and saves the new report to a particular location. But when I open the updated report, the data from the external query has not changed. I can run the report manually by hitting refresh all, and the data does get updated. Also I did not encounter any errors in the VBA. Here is the code:
Private Sub Workbook_Open() Dim wrkNewBook As Workbook Dim wrkOldBook As Workbook Dim iCount As Integer Dim sFileName As String Dim sReportName As String Dim iSheetCount As Integer Dim sWrapperName As String sWrapperName = ActiveWorkbook.Name sFileName = CStr(Application.Workbooks(sWrapperName).Sheets(1).Range("FileName").Value) iSheetCount = CLng("0" & Application.Workbooks(sWrapperName).Sheets(1).Range("NumberOfWorkSheets").Value) Set wrkOldBook = Application.Workbooks.Open(sFileName) Application.ScreenUpdating = False Application.DisplayAlerts = False ActiveWorkbook.RefreshAll Application.Calculate If iSheetCount > wrkOldBook.Sheets.Count Then iSheetCount = wrkOldBook.Sheets.Count End If While (wrkOldBook.Sheets.Count > iSheetCount) wrkOldBook.Sheets(wrkOldBook.Sheets.Count).Delete Wend Application.ScreenUpdating = True Application.Calculate wrkOldBook.SaveAs "W:\SHAREU\Finance\POLR 500 Reports\" & wrkOldBook.Name wrkOldBook.SaveAs "E:\PUB\EEP\Finance\Cost Reports\" & wrkOldBook.Name wrkOldBook.Close False Application.DisplayAlerts = True Application.Quit End Sub
Is there something I need to change so that it will work in Excel 2010?