none
The SaveAs function in vba is not working for FileFormat:= 51 RRS feed

  • Question

  • With the recent MS Office 2016 update, when I am trying to save an workbook through SaveAs function and using the FileFormat 51, it does nothing. Also no error shows up. Please find below the code I am using

    strDirPath = "C:\Xauthor Models\"

    strTempPath = "C:\Xauthor Models\Template.xlsx"

    Set WkbAptModel = Workbooks.Open(Filename:=strTempPath, UpdateLinks:=0, ReadOnly:=False)

    WkbAptModel.SaveAs Filename:=strDirPath & "Test", FileFormat:=51

    WkbAptModel.Close SaveChanges:=False

    I am using MS Office 365 pro plus  MSO( 16.0.8201.2200 ) 32 bit and windows 10.

    Please help!!.

    Friday, November 3, 2017 5:24 PM

All replies

  • Hi Rakesh3235,

    I suppose your code will work fine.

    I made a sample VBA according to your code with little modification.
    (This code is in [Save] button on my sample sheet.)
    ' ---[Save]
    Private Sub btn_Save_Click()
        Dim strDirPath As String
        Dim strTempPath As String
        Dim WkbAptModel As Workbook
        ' ---
        strDirPath = "C:\temp\"
        strTempPath = "C:\temp\Excel Test.xlsx"
        Set WkbAptModel = Workbooks.Open _
            (Filename:=strTempPath, UpdateLinks:=0, ReadOnly:=False)
        WkbAptModel.SaveAs _
            Filename:=strDirPath & "Test", FileFormat:=51
        WkbAptModel.Close SaveChanges:=False
    End Sub
    My Office version is:
        

    Can't you see a file "
    Template.xlsx" in "C:\Xauthor Models" folder?

    Regards,

    Ashidacchi

    Saturday, November 4, 2017 3:57 AM
  • Yes, I am able to see the Template.xlsx File and it is opening correctly.

    I am opening that "Template.xlsx" File using a Macro. Write some data on the file and try to save it using the vba SaveAs option, but it is not working when I use as below.

    WkbAptModel.SaveAs Filename:=strDirPath & "Test", FileFormat:=51

    But it works when I try to save it as macro enabled file with the below code

    WkbAptModel.SaveAs Filename:=strDirPath & "Test", FileFormat:=52

    Is there any other way I can save the file in .xlsx format as the FileFormat 51 is not working for me.

    Also find attached screenshots showing the Excel version I am using at my end

    Saturday, November 4, 2017 3:26 PM
  • Your file must be "*.xlsM" instead of "*.xlsX".

    Please check file extension.

    strTempPath = "C:\Xauthor Models\Template.xlsM"


    Ashidacchi


    • Edited by Ashidacchi Saturday, November 4, 2017 5:08 PM
    Saturday, November 4, 2017 5:00 PM
  • Hi Ashidacchi,

    In my code, I run a Macro Enabled .xlsm File which opens the Template.xlsx File through the Macro code. Then write some data to the Template.xlsx file through the code logic and then was trying to save the Template.xlsx File with some another name through code logic in the .xlsx format, through the below code which didn't worked.

    WkbAptModel.SaveAs Filename:=strDirPath & "Test", FileFormat:=51

    But When I try to save the File in the .xlsm Format with the below code, it runs successfully and saves the File.

    WkbAptModel.SaveAs Filename:=strDirPath & "Test", FileFormat:=52

    I am really not sure why it is unable to save the Template.xlsx File in .xlsx format. Is there anyway I can run the SaveAs Function which will save the file in .xlsx Format. Please suggest

    Thanks

    Rakesh

    Sunday, November 5, 2017 12:06 AM
  • Hi Rakesh3235,

    You have not provided your code. So I don't know what happens in the Template.xlsx.
    I suspect the Template file is added some macro (VBA code) or some controls such as ActiveX.

    I hope you will provide code related to the Template file.

    Regards,

    Ashidacchi

    Sunday, November 5, 2017 1:36 AM
  • Hi,

    I notice that the issue is related to the Macro code for Excel SaveAs. To better resolve the issue, I would move the thread to Excel for developers forum for further troubleshooting.

    Thanks for your understanding.

    Best Regards,
    Winnie Liang


    Please remember to mark the replies as answers if they help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, November 6, 2017 2:00 AM
  • Hi Rakesh,

    I failed to reproduce your issue too. I have tried to edit the workbook after opening it and it could be saved with the edited content. 

    Here is the code I used. I would suggest you provide your whole code or the xlsm file so we could try to reproduce your issue. 

    Sub CustomSaveAs()
    strDirPath = "C:\Users\v-guaxu\Desktop\"
    
    strTempPath = "C:\Users\v-guaxu\Desktop\Template.xlsx"
    
    Set WkbAptModel = Workbooks.Open(Filename:=strTempPath, UpdateLinks:=0, ReadOnly:=False)
    For i = 1 To 20
    WkbAptModel.Sheets(1).Cells(i, 1).Value = "Edit Test " & i
    Next i
    WkbAptModel.SaveAs Filename:=strDirPath & "Test", FileFormat:=52
    
    WkbAptModel.Close SaveChanges:=False
    End Sub

    Besides, I would suggest you update your Office to latest version(8625.2121) and then do the test again.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 7, 2017 3:00 AM
  • Hi Rakesh,

    I suspect .....if some controls (such as AxtiveX controls using with macro) are in the Template.* file. Please check it.

    And I hope you will provide your code in your post or share the Template.* file via cloud storage such as OneDrive, Dropbox, etc.
    (Remember to edit/modify you private/important data before sharing.)

    Regards,   

    Ashidacchi

    Tuesday, November 7, 2017 3:21 AM
  • Hi Terry,

    Thank you a lot that you tried that from your end. I can see that in the above code , you tried to save the file in the FileFormat:=52. That is working fine for me too. But when I try to save the File with the FileFormat:=51, that's where I am facing the issue.

    Please find below the code I am using at my end.

    Private Sub CmdSubmit_Click()
    
    Dim WrkModel As Workbook, wksModel As Worksheet, WkbAptModel As Workbook, wksAptModel As Worksheet
    Dim Target As Range, r As Range, strParentBundle As String, strOfferingName As String, a As Integer, TabSeq As Integer, OptionClassSeq As Integer, OptionItemSeq As Integer
    Dim strTabName As String, strTempPath As String, StrOptGrpName As String
    On Error Resume Next
    
    strFileName = Me.TxtPath.Value
    Set WrkModel = Workbooks.Open(Filename:=strFileName, UpdateLinks:=0, ReadOnly:=False)
    
    Set wksModel = WrkModel.Worksheets(1)
    
    strParentBundle = wksModel.Range("D1").Value
    
    strOfferingName = wksModel.Range("D2").Value
    
    strTempPath = "C:\Xauthor Models\Template.xlsx"
    
    Set WkbAptModel = Workbooks.Open(Filename:=strTempPath, UpdateLinks:=0, ReadOnly:=False)
    
    Set wksAptModel = WkbAptModel.Worksheets("Sheet1")
    
    intRowTotal = wksModel.Cells.Find(what:="*", After:=[A1], _
                                      SearchOrder:=xlByRows, _
                                      SearchDirection:=xlPrevious).Row
    
    intcolLngDes = wksModel.Rows(4).Find(what:="Long Description", lookat:=xlWhole).Column
    intcolLevel = wksModel.Rows(4).Find(what:="Level", lookat:=xlWhole).Column
    wksAptModel.Range("B2").Value = strParentBundle
    
    a = 3
    TabSeq = 10
    OptionClassSeq = 10
    OptionItemSeq = 10
    
    For i = 5 To intRowTotal
    
        If wksModel.Cells(i, intcolLevel).Value = "Tab" Then
           wksAptModel.Cells(a, 1).Value = wksModel.Cells(i, intcolLevel).Value
           wksAptModel.Cells(a, 3).Value = wksModel.Cells(i, 3).Value
           wksAptModel.Cells(a, 4).Value = strParentBundle
           strTabName = wksModel.Cells(i, 3).Value
           wksAptModel.Cells(a, 5).Value = TabSeq
           TabSeq = TabSeq + 10
           OptionClassSeq = 10
           OptionItemSeq = 10
        ElseIf wksModel.Cells(i, intcolLevel).Value = "Option Class" Then
           wksAptModel.Cells(a, 1).Value = "Option Group"
           wksAptModel.Cells(a, 3).Value = wksModel.Cells(i, 3).Value
           wksAptModel.Cells(a, 4).Value = strTabName
           StrOptGrpName = wksAptModel.Cells(a, 3).Value
           wksAptModel.Cells(a, 5).Value = OptionClassSeq
           OptionClassSeq = OptionClassSeq + 10
           OptionItemSeq = 10
        ElseIf wksModel.Cells(i, intcolLevel).Value = "Option Item" Then
           wksAptModel.Cells(a, 1).Value = "Item"
           wksAptModel.Cells(a, 2).Value = wksModel.Cells(i, 2).Value
           wksAptModel.Cells(a, 3).Value = wksModel.Cells(i, 3).Value
           wksAptModel.Cells(a, 4).Value = StrOptGrpName
           wksAptModel.Range(Cells(a, 4), Cells(a, 4)).WrapText = True
           wksAptModel.Cells(a, 5).Value = OptionItemSeq
           OptionItemSeq = OptionItemSeq + 10
        End If
        a = a + 1
    Next
    'Application.EnableEvents = True
    WkbAptModel.SaveAs Filename:=strDirPath & strParentBundle, FileFormat:=52
    WkbAptModel.Close SaveChanges:=False
    WrkModel.Close SaveChanges:=False
    Unload Me
    
    End Sub
    


    Tuesday, November 7, 2017 11:57 PM
  • Hi Rakesh3235,

    Sorry for my mistake. I have tested both 51 and 52 and they worked well for me. I have also tested the code in your last reply and did not reproduce your issue.

    Could you use SaveAs option to save the file as xlsx file manually? I did not find strDirPath in the code. If the strDirPath is empty, the file will be saved in the same path as the workbook where you run the code. Have you checked the path?

    Besides, you said this issue occurs with recently Office update, I would suggest you revert Office to your previous version or update to latest version.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 8, 2017 1:56 AM
  • Hi Rakesh3235,

    I checked the code you provided and a run-time error occurs in the line
    "strFileName = Me.TxtPath.Value"


    I'm wondering why "Me." is placed before TxtPath in VBA code.
    (I suppose it is not needed.)
    Provide where is "TxtPath" in your sheets or code.

    Regards,

    Ashidacchi


    • Edited by Ashidacchi Wednesday, November 8, 2017 2:09 AM
    Wednesday, November 8, 2017 2:08 AM
  • additional information about FileFormat:

    Ashidacchi

    Wednesday, November 8, 2017 2:14 AM