Forum Discussion

jenthevb's avatar
jenthevb
Copper Contributor
Jun 03, 2022

VBA Error 1004 Method 'SaveAs of object '_Workbook' failed

Hello,

 

I got the error 1004. Method SaveAs of Object '_Workbook" failed.
When i check the solutions that i've found, it doesn't work.

I have office 365, here is my code:

 

Sub UpdateMaandSpSite()

'***** WEGSCHRIJVEN PLANNING IN PDF VORM NAAR SP SITE ******
    
    Dim NewBook As Workbook
    Dim DatumTijd As Date
    Dim Tijd As Date
    Dim Path As String
    
    datum = FormatDateTime(Now(), vbShortDate)
    Tijd = FormatDateTime(Now(), vbShortTime)
        
      
    ActiveSheet.Unprotect Password:="Covid21"
    
    Application.GoTo Reference:=maand
    Selection.Copy
    Set NewBook = Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

    Columns("A:AQ").EntireColumn.AutoFit

    Cells(3, 2).Select
    ActiveWindow.FreezePanes = True
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, Password:="Covid21"

    'NewBook.SaveAs Filename:="H:" & maand & "_" & Jaar & "_" & "_SC_WK.xlsx", FileFormat:= _
     '   xlOpenXMLWorkbook, CreateBackup:=False

    NewBook.SaveAs Filename:="https://sharepoint.colruytgroup.com/teams/8/TI002/Planning SC_Waakdienst/" & maand & "_" & Jaar & "_" & "SC_WK.xlsx", FileFormat:= _
       1, CreateBackup:=False

    ActiveWorkbook.Close
    Application.CutCopyMode = False

    MsgBox ("Planning SC_Waakdienst " & maand & "_" & Jaar & " is weggeschreven")

   ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, Password:="Covid21"
   
End Sub

 


The URL is correct, so i don't find the correct solution.

Thank in advance for the help.

 

Regards,

Jenthe 

3 Replies

  • jenthevb 

    Does it work if you change the SaveAs instruction to

        NewBook.SaveAs Filename:="https://sharepoint.colruytgroup.com/teams/8/TI002/Planning SC_Waakdienst/" & maand & "_" & Jaar & "_" & "SC_WK.xlsx", FileFormat:= _
           xlOpenXMLWorkbook, CreateBackup:=False
    • jenthevb's avatar
      jenthevb
      Copper Contributor

      HansVogelaar 

      No it doesn't work. It gives the same issue. When you click on debug, 

      NewBook.SaveAs Filename:="https://sharepoint.colruytgroup.com/teams/8/TI002/Planning SC_Waakdienst/" & maand & "_" & Jaar & "_" & "SC_WK.xlsx", FileFormat:= _
             xlOpenXMLWorkbook, CreateBackup:=False

      This is code is highlighted. I've checked the url multiple times, this is correct.

       

      Regards,
      Jenthe 

Resources