Consulting

Results 1 to 5 of 5

Thread: Save file to a different folder, add to file name & change from .xlsm to .xlsx

  1. #1
    VBAX Regular
    Joined
    Jun 2005
    Posts
    95
    Location

    Save file to a different folder, add to file name & change from .xlsm to .xlsx

    Hello everyone,

    Long time no post...

    I have a file where I am copying a range of cells, pasting and it to a new workbook. What I want to do is save that workbook in another folder with the same name as the original file with " - Mapping File" added to the new file name, then change the new file from .xlsm to .xlsx. I am so close with what I have but it's doing something wonky (yes I said wonky)!

    ActiveWorkbook.SaveAs "S:\Recruiting Information\Field Recruiters\Area Books\Mapping Files\" & fileName & " - Mapping File", FileFormat:=xlOpenXMLWorkbook
    This is doing everything I want it to do except the final file name is coming out as
    "Lake Michigan Region School List.xlsm - Mapping File" (which is not even an Excel file, probably because of " - Mapping File" after the extension)

    and it should be "Lake Michigan Region School List - Mapping File.xlsx" and a regular Excel file. Can anyone help??? Thank you in advance.
    Last edited by macropod; 01-09-2019 at 02:15 AM.

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    That's as simple as:
    With ActiveWorkbook
      .SaveAs "S:\Recruiting Information\Field Recruiters\Area Books\Mapping Files\" & Split(.Name,".xls")(0) & " - Mapping File.xlsx", FileFormat:=xlOpenXMLWorkbook
    End With
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Regular
    Joined
    Jun 2005
    Posts
    95
    Location
    Well that worked... sort of. It did change the extension from .xlsm to .xlsx and put the " - Mapping File" at the end but it did not rename the file with "Lake Michigan Region School List" before the " - Mapping File". It named it "Book1 - Mapping File". I am sure it has something to do with my code. Please see below... thank you for your reply. Sorry it took me so long to respond but when I went to reply yesterday I was locked out from posting new threads or replying because I changed my email address. Have a great day!

        Dim fileName As String
            fileName = ActiveWorkbook.Name
        ActiveSheet.Unprotect Password:=""
            Range("M6:M506,O6:O506,Q6:Q506,W6:AB506").SpecialCells(xlCellTypeVisible).Select
                ActiveSheet.Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True _
                    , AllowInsertingHyperlinks:=False, AllowFiltering:=True
                        ActiveSheet.EnableSelection = xlNoRestrictions
                Selection.Copy
            Workbooks.Add
                Range("A1").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                            Application.CutCopyMode = False
                With ActiveWorkbook
                  .SaveAs "S:\Recruiting Information\Field Recruiters\Area Books\Mapping Files\" & Split(.Name, ".xls")(0) & " - Mapping File.xlsx", FileFormat:=xlOpenXMLWorkbook
                End With

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Your previous code lacked the 'fileName' context given in your latest post. Accordingly, I assumed that was just your attempt to retrieve the existing file's name. Given that's not the case, you can use:
    ActiveWorkbook.SaveAs "S:\Recruiting Information\Field Recruiters\Area Books\Mapping Files\" & Split(fileName, ".xls")(0) & " - Mapping File.xlsx", FileFormat:=xlOpenXMLWorkbook
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Regular
    Joined
    Jun 2005
    Posts
    95
    Location
    You are nothing short of AWESOME!!! Worked perfectly. Thank you so much!

Posting Permissions

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