none
VB Runtime Error 1004 - Excel File - Save Instruction

    Question

  • Here are some important facts:

    This code works (no Error 1004 on numerous laptops and desktops)

    A couple of months ago it began to fail (Error 1004 reared it's ugly head) on a couple of machines

    The users claim that nothing changed - I can neither confirm nor deny accuracy of these claims

    All users, except one, are running Windows XP and Office 2003

    The problem also surfaced on a new machine running Windows XP and Office 2007

    I could not force the error on my development/test machine - Windows XP with Offic 2003

    It fails every time on another machine I have running Vista with Office 2007

     

    The following is the code that causes me the problem.  I know the error is raised on the WB.Save statement.  I added the error trap to eliminate the problem of the Excel file being unaccessable without a reboot, due to the fact that Windows thought Excel still had it open.

     

    If anyone can help me solve this problem or at least develop a work-around I would be most appreciative!

     

    Public Sub ExportCostAnalysis(ByVal RunMode As String)

    On Error GoTo ErrorHandler

        Dim rs             As dao.Recordset

        Dim RcdCnt         As Integer

        Dim FGPart         As String

        Dim wdbl           As Double

        Dim UnitCost       As Double

        Dim ExtCost        As Double

        Dim VendorTotal    As Double

        Dim ReportTotal    As Double

        Dim oFSO           As New FileSystemObject

        Dim XL             As New Excel.Application

        Dim WB             As Excel.Workbook

        Dim WS             As Excel.Worksheet

        Dim Selection      As Excel.Range

        Dim Row            As Long

        Dim EMailClient    As String

        Dim MsgList        As String

        Dim ExcelFileName  As String

        Dim PrevVendorCode As String

        Dim PrevVendorName As String

        '-------------------------------------

       

        If RunMode = "DETAIL" Then

            ExcelFileName = cCOSTANALYSISDETAIL

        ElseIf RunMode = "SUMMARY" Then

            ExcelFileName = cCOSTANALYSISSUMMARY

        Else

            Exit Sub

        End If

     

        ReportTotal = 0#

       

        Set rs = db.OpenRecordset("qCostAnalysisExport")

        If rs.EOF Then

            MsgBox "No Products have been selected", vbCritical, "PROCESS ABORTED"

            GoTo TheEnd

        Else

            PrevVendorCode = rs!VendorCode

            PrevVendorName = rs!Vendor

        End If

       

        On Error Resume Next

        Call oFSO.DeleteFile(ExcelFileName, True)

        On Error GoTo ErrorHandler

       

        Screen.MousePointer = vbHourglass

           

     If RunMode = "DETAIL" Then

            If UnZipFile(cACSLIB, cMYPATH, "CostAnalysisDetail.xls") <> True Then Exit Sub

        ElseIf RunMode = "SUMMARY" Then

            If UnZipFile(cACSLIB, cMYPATH, "CostAnalysisSummary.xls") <> True Then Exit Sub

        End If

       

        XL.Workbooks.Open ExcelFileName

        Set WB = XL.ActiveWorkbook

        Set WS = XL.ActiveSheet

       

        WB.DefaultSaveFormat = xlWorkbookNormal

       

        Row = 2

        Do While Not rs.EOF

            If RunMode = "SUMMARY" Then

     

    (my code to build and fill cells)

     

            EndIf

        Loop

       

        rs.Close

       

        If RunMode = "DETAIL" Then

     

    (my code to build and fill cells)

     

            Loop

        End If

       

        rs.Close

       

        WS.Range("A1").Font.Bold = True         'reposition to top left cell

        WB.Saved = True

        On Error Resume Next    'expecting error here   Added due to Error 1004

            WB.Save

        If Err.Number <> 0 Then                 'Added due to Error 1004

            MsgBox Err.Description & vbLf & Err.Number

            Err.Clear

        Else

            'no error occured

        End If

       

        WB.Close

        XL.Quit

     

    Tuesday, May 27, 2008 1:54 AM

All replies

  •  

    1. In what are you writing this?  VB6?  VBA?

     

    2. When you do get the error, what does it tell you in your error-catching MsgBox?

     

    3. Why do use the line WB.Saved = True?

     

    4. What happens if you replace WB.Save with WB.SaveAs (with the necessary parameters, that is)?

    Tuesday, May 27, 2008 3:28 AM
  • 1.  It is VB6

     

    2.  The error is 1004 and the verbiage is "Method '~' of object '~' failed".  I don't know why it is missing some of the information ('~').

     

    3.  To be honest with you, I don't know.  I inherited this system from a consulting friend who passed away from cancer, and I am just getting familiar with all of the programs.  I am told by the client, this problem surfaced just before he passed away.

     

    4.  I attempted to do that, but was unable to get past the syntax errors with all of the parameters associated with the SaveAs command.

    Wednesday, May 28, 2008 10:57 AM
  • I say try:

     

    1. commenting out WB.Saved = True

    2. replacing WB.Save with

    WB.SaveAs("C:\XLSTest.xls", xlWorkbookNormal)

    Thursday, May 29, 2008 3:19 AM
  • When I enter the WB.SaveAS statement, I get a compile error "Expected: =".

    This is the same issue I had when I first attempted to substitute this call for WB.Save.

    I triple checked, and my code is entered exactly as you have suggested.

    Where to now?

    Thursday, May 29, 2008 11:12 AM
  • Take out the parentheses:

     

    Code Snippet

    WB.SaveAs "C:\XLSTest.xls", xlWorkbookNormal

     

     

     

     

    Thursday, May 29, 2008 11:57 AM
  • I now can get a clean compile.
    When I run the app (on either computer - the Vista/Office 2007 system, and the Win 2003/Office 2002 system) it fails with the following error:
    Error No: 438
    Description: Object doesn't support this property or method

    The XLTest.xls file does not get created.

    The error message is not from the special trap I put in the code to catch the 1004 Error, it must be put out by the standard error handler.
    Friday, May 30, 2008 3:23 AM
  • Thank you for sharing info on runtime error 1004 with saveas command.   I too am having this issue and will change to your suggested syntax.    I am concerned that this issue is very sporatic.   My application runs several excel jobs daily.   Jobs will run for months OK and then this error occurs for several days in a row.    The error always occurs on the same macro line in all workbooks.   That line is saving the workbook to a sharepoint site where I have total publish authorisation.   Any suggestions on other areas to look at would be greatly appreciated.
    Thursday, June 12, 2008 1:35 PM
  • I am having the same problem with trying to do the SaveAs command when saving to a sharepoint location.  Below is a copy of the code that I have written. I have tried adding the "On Error Resume Next" command right above the SaveAs command but all that does is goes onto the next step in the process and therefore does not upload the file to the sharepoint site.  Without the "On Error Resume Next" command, I get the Runtime error 1004 message, i click on debug and it takes me to the SaveAs line of code, then i can click Run and Continue and it will run from there.

    I would greatly appreciate it if anyone could help with this!
    Thanks in advance!

    Application.DisplayAlerts = False 
    Application.EnableCancelKey = xlDisabled 
    ChDir _  
            "G:\Customer Markets\Consumer\HQ\ManagersShared\Force\REPORTS GROUP\DAILY"  
        Workbooks.Open Filename:= _  
            "G:\Customer Markets\Consumer\HQ\ManagersShared\Force\REPORTS GROUP\DAILY\Master - Office Daily Cum.xls"  
        Sheets("Cur Mon Data").Select  
        Range("A2").Select  
        Selection.QueryTable.Refresh BackgroundQuery:=False  
        Sheets("Hist Mon Data").Select  
        Range("A2").Select  
        Selection.QueryTable.Refresh BackgroundQuery:=False  
        ActiveWorkbook.Save  
        Application.Run "'Master - Office Daily Cum.xls'!unhideYTDtabs"  
        ActiveWindow.ScrollWorkbookTabs Position:=xlFirst  
        ChDir _  
            "G:\Customer Markets\Consumer\HQ\ManagersShared\Force\REPORTS GROUP\DAILY\DAILY CUM\Office Daily Cum"  
        ActiveWorkbook.SaveAs Filename:= _  
            "G:\Customer Markets\Consumer\HQ\ManagersShared\Force\REPORTS GROUP\DAILY\DAILY CUM\Office Daily Cum\Office Daily Cum 09-03 (Mar).xls" _  
            , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _  
            ReadOnlyRecommended:=False, CreateBackup:=False  
        ChDir _  
            "G:\Customer Markets\Consumer\HQ\ManagersShared\Force\Misc Scheduling Tools"  
        ActiveWorkbook.SaveAs Filename:= _  
            "G:\Customer Markets\Consumer\HQ\ManagersShared\Force\Misc Scheduling Tools\Office Daily Cum 09-03 (Mar).xls" _  
            , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _  
            ReadOnlyRecommended:=False, CreateBackup:=False  
      ActiveWorkbook.SaveAs Filename:= _  
            "http://retailmkts.teamsites.bls.com/sites/Workforcemgmt/Consumer/Daily Reports/Daily Cum - Office/Office Daily Cum 09-03 (Mar).xls" _  
            , FileFormat:=1, Password:="", WriteResPassword:="", _  
            ReadOnlyRecommended:=False, CreateBackup:=False  
        ActiveWorkbook.Close 
    Tuesday, March 17, 2009 5:18 PM