GetSaveAsFilename corrupts spreadsheet

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
219
Code:
sFName = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx, Macro Enabled Workbook" & "(*.xlsm), *xlsm")

    If sFName <> "False" Then
        ws.SaveAs sFName, 56
    End If

With sFName set as a string. I am using MS Office 2016 on win10 if that helps, have not tried this in Office 2010 yet, that will happen later. Laptop is on 2016, VM is on 2010 on win7.

The code works so far as populating a desired file name (that code not shown, just a few text strings placed under: InitialFileName:=Variable_here), but when I click save I am faced with compatibility errors, than after that the file while showing in the correct location is broke and corrupted to the point you must delete, zero access.

This is a major problem, is the GetSaveAsFilename code trying to save as some other file even though xlsx and xlsm are "options" shown?

This happens with both xlsx and xlsm files for this code.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
What format do you want the file saved as?
 
Upvote 0
It’s nothing to do with that. You’re specifying a file format of 56 which is for xls files. You need 51 and 52 for xlsx and xlsm respectively.
 
Upvote 0
It’s nothing to do with that. You’re specifying a file format of 56 which is for xls files. You need 51 and 52 for xlsx and xlsm respectively.

RoryA, thank you for that bit of info.

Do I have to build an if statement for both 51 and 52?

I tried , 51 52

that produces an expected end of statement error. when I comma separate them that is trying to set a password.

I also tried with zero file type that errored out and failed to save the document.
 
Upvote 0
Does the workbook contain macros?
 
Upvote 0
Yes you’ll need an if statement based on the selected file extension.
 
Upvote 0
The spreadsheet does currently contain macros, but I would like the option to save as either enabled or not.
 
Upvote 0
In that case how about
Code:
    If sFName <> "False" Then
      If Right(sFName, 4) = "xlsx" Then
         Application.DisplayAlerts = False
         Ws.SaveAs sFName, 51
         Application.DisplayAlerts = True
      ElseIf Right(sFName, 4) = "xlsm" Then
         Ws.SaveAs sFName, 52
      End If
    End If
 
Upvote 0
Fluffy, that worked like a dream. Thank you.

Progress so far on the project:

1. Both Master and Table are located on a ShareNow server
2. Master is able to open and activate the Table workbook, but not perform work on Table :(
3. As I was unsuccessful at getting the VBScript to perform tasks like saveas or copy/pastespecial on the Table workbook started code on Table wookbook.
4. Table workbook is now able to be modified (2 cell values require changing to pull data using an array from Master)
5. Table copy/paste values only across set range and performs the saveas you guys just helped me fix.

Good progress so far. Thank you all. I hope some of these threads help others down the line.
 
Upvote 0
Full code currently

Code:
Sub FOOsaveValues()

Dim wb As Workbook
Dim ws As Worksheet
Dim Network As String
Dim Group As String
Dim sFName As String
Dim Def As String


Set wb = ActiveWorkbook                 ' Used as shortcut for Workbook
Set ws = wb.Worksheets("FOO Table")     ' Worksheets shortcut to read data from FOO Table
    ' Read Cells(row, column).Value and store as variable - C1, Network name
Network = ws.Cells(1, 3).Value
    ' Read Cells(row, column).Value and store as variable - G1, Group #
Group = ws.Cells(1, 7).Value
Def = "FOO_Table-" & Network & "_Group_" & Group


'=====================================
' Start Copy/Paste Values only section                  Column D -> H, Rows 3 -> 52
'=====================================
ws.Range("d3:h52").Copy                                 ' Copy the data from New Vault (Row 3) -> RTU/VaultGard Serial # (Row 52)
ws.Range("d3:h52").PasteSpecial Paste:=xlPasteValues    ' Pastes value only, dropping the array created by XXXXX
Application.CutCopyMode = False                         ' Clears values so not to double paste if users clicks on cell or hits Return


'=====================================
'Start Save As process
'=====================================
sFName = Application.GetSaveAsFilename(InitialFileName:=Def, FileFilter:="Excel Files (*.xlsx), *.xlsx, Macro Enabled Workbook" & "(*.xlsm), *xlsm")


    If sFName <> "False" Then                   ' Checks that sFName has not been cancled
      If Right(sFName, 4) = "xlsx" Then         ' If saved as *.xlsx this section provides correct file type 51
         Application.DisplayAlerts = False      ' *.xlsx is no macros enabled
         ws.SaveAs sFName, 51
         Application.DisplayAlerts = True       ' Displays all errors from SaveAs
      ElseIf Right(sFName, 4) = "xlsm" Then     ' If saved as *.xlsm this section provides correct file type 52
         ws.SaveAs sFName, 52                   ' *.xlsm is macro enabled
      End If
    End If


End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top