none
VBA SaveAs not correctly working RRS feed

  • Question

  • Hi,

    Any help would be greatly appreciated.

    I have an excel file which refreshes (connects to the SQL Server) & calculates automatically when opened. I then use a SaveAs command to save these changes to another file which I then use to send to a few people.

    This works fine when I run it normally i.e. open the file myself and see the events occur but when I create a task within the task scheduler the SQL updates never stick to the file (or the old for that matter).

    Ive placed a couple of test values such as set one of the cells to 1 to see if its working and it does but the SQL updates that I need never update to the spreadsheet.

    This is the code that I have:

    Private Sub Workbook_Open()
    
    Application.DisplayAlerts = False
    
    Calculate
    
    If ActiveWorkbook.Name = "South_Report_Pack.xlsm" Then
    'Range("A1").Value = 4
    Workbooks("South_Report_Pack.xlsm").RefreshAll
    Calculate
    Workbooks("South_Report_Pack.xlsm").Save
    Workbooks("South_Report_Pack.xlsm").SaveAs Filename:= _
          "C:\Users\JasR\Documents\South\South_Update.xlsx", _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=True, ReadOnlyRecommended:=True
    Workbooks("South_Update.xlsx").Save
    End If
    
    If ActiveWorkbook.Name = "South_Update.xlsx" Then
    Save
    End If
    
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
    Application.Quit
    End Sub

    Can anybody see where I'm going wrong?

    Thanks for the help,

    Jas

    Friday, November 4, 2011 8:28 AM

Answers

  • The next step is to determine if the RefreshAll is completing in a timely fashion.  Is there a cell that you can examine before and after the refresh with  MsgBox's that will prove the refresh completed??
    gsnu201109
    • Marked as answer by Jas10 Friday, November 4, 2011 4:30 PM
    Friday, November 4, 2011 1:33 PM
    Moderator
  • I managed to solve it, it was indeed the RefreshAll that was not completing in time. I de-selected the Enable Refresh In The Background from the Data Options and now it completes the task before saving and closing.

    Thanks for the taking time to reply.

    Jas

    • Marked as answer by Jas10 Friday, November 4, 2011 4:30 PM
    Friday, November 4, 2011 4:30 PM

All replies

  • First remove DisplayAlerts=False.

    Perhaps Excel itself will tell us why the code is not working.


    gsnu201109
    Friday, November 4, 2011 11:10 AM
    Moderator
  • Hi,

    I disabled the line of code, the message it stated was that Excel couldnt save the file in the format specified as it a contains macro. So i have changed it to a MacroEnabled workbook within the SaveAs. After re-running it, it displays another mesage stating that a file exists and whether I want to overwrite it. From what Ive read online Application.DisplayAlerts = False was meant to solve this issue, Is this true?. Either way, after re-enabling Application.DisplayAlerts = False the data remains the same.

     

    Thanks,

    Jas

    • Marked as answer by Jas10 Friday, November 4, 2011 4:30 PM
    • Unmarked as answer by Jas10 Friday, November 4, 2011 4:30 PM
    Friday, November 4, 2011 11:57 AM
  • The next step is to determine if the RefreshAll is completing in a timely fashion.  Is there a cell that you can examine before and after the refresh with  MsgBox's that will prove the refresh completed??
    gsnu201109
    • Marked as answer by Jas10 Friday, November 4, 2011 4:30 PM
    Friday, November 4, 2011 1:33 PM
    Moderator
  • I managed to solve it, it was indeed the RefreshAll that was not completing in time. I de-selected the Enable Refresh In The Background from the Data Options and now it completes the task before saving and closing.

    Thanks for the taking time to reply.

    Jas

    • Marked as answer by Jas10 Friday, November 4, 2011 4:30 PM
    Friday, November 4, 2011 4:30 PM
  • Thanks for the feedback!
    gsnu201109
    Friday, November 4, 2011 4:31 PM
    Moderator