Results 1 to 8 of 8

Fails to save in XLSB using ActiveWorkbook.SaveAs method

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-20-2021
    Location
    UK
    MS-Off Ver
    2016
    Posts
    126

    Fails to save in XLSB using ActiveWorkbook.SaveAs method

    Trying to create a userform with two options buttons for saving the activeworkbook in either xlsx, or xlsb format. Where the option button names are SS: .for saving in xlsb;NS: for saving in .xlsx, in the code below.

    Run-time error 1004: Method "SaveAs" of object "_Workbook" failed

    I have highlighted the code in red to where the debugger points towards. Funny enough, it manages to save it in .xlsx format just fine.

    Dim sPath As String
    Dim GetTheName As String
    Dim Form1 As Variant, Form2 As Variant
    Dim add_ext1 As String, add_ext2 As String
    Dim wb As Workbook
    ____________________________________________________________________
    Private Sub CancelButton_Click()
        Unload Me 'me = userform
    End Sub
    ____________________________________________________________________
    Private Sub GetFilePath_Click()
       
        
        GetTheName = FileNameTextBox.Value
        
        With Application.FileDialog(msoFileDialogFolderPicker)
        
            .ButtonName = "Save in this folder"
            If .Show = -1 Then 'if ok is pressed
                sPath = .SelectedItems(1)
            End If
            
        End With
    
    Set wb = ActiveWorkbook
    
    todaysDay = Format(Now(), "dd.mm.yyyy")
    
        If sPath <> "" Then
        
            If NS = True Then
                wb.SaveAs Filename:=sPath & "\" & GetTheName & " " & Format(Now(), "dd.mm.yyyy") & ".xlsx", FileFormat:=Form1
            ElseIf SS = True Then
                wb.SaveAs Filename:=sPath & "\" & GetTheName & " " & Format(Now(), "dd.mm.yyyy") & ".xlsb", FileFormat:=Form2
            End If
            
        End If
            
        Unload Me
        
    End Sub
    
    ____________________________________________________________________
    Private Sub NS_Click()
        Form1 = xlWorkbookDefault
    End Sub
    ____________________________________________________________________
    Private Sub SS_Click()
        Form2 = xlExcel12
    End Sub
    Last edited by immigrated4urjob; 11-22-2021 at 06:03 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 06-08-2015, 01:14 AM
  2. [SOLVED] Saveas method fails due to duplicate file name
    By sauerj in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-21-2014, 12:08 AM
  3. [SOLVED] ActiveWorkbook.SaveAs function needs to save as .xlsb instead of .xlsm
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-21-2012, 10:38 AM
  4. ActiveWorkbook.SaveAs
    By Bhawxfan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2010, 12:00 PM
  5. Method 'SaveAs' fails
    By mtpsuresh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2006, 06:25 AM
  6. Replies: 0
    Last Post: 09-19-2005, 01:05 PM
  7. ActiveWorkbook SaveAs
    By Mike in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2005, 12:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1