Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

    If iSheetCount > wrkOldBook.Sheets.Count Then
        iSheetCount = wrkOldBook.Sheets.Count
    End If

    While (wrkOldBook.Sheets.Count > iSheetCount)

    Application.ScreenUpdating = True

    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

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

You must log in to answer this question.

Browse other questions tagged .