GetSaveAsFilename corrupts spreadsheet

[LlebKcir]

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.

 

[Fluff]

What format do you want the file saved as?

 

[RoryA]

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.

 

[LlebKcir]

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.

 

[Fluff]

Does the workbook contain macros?

 

[RoryA]

Yes you’ll need an if statement based on the selected file extension.

 

[LlebKcir]

The spreadsheet does currently contain macros, but I would like the option to save as either enabled or not.

 

[Fluff]

In that case how about

    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

 

[LlebKcir]

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.

 

[LlebKcir]

Full code currently

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