Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
Try adding a DoEvents between the lines Application.Calculate and wrkOldBook.SaveAs ... –  wbeard52 Aug 17 '12 at 14:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.