Consulting

Results 1 to 6 of 6

Thread: VBA save as function as new workbook

  1. #1
    VBAX Regular
    Joined
    Dec 2018
    Posts
    11
    Location

    VBA save as function as new workbook

    Hello all,
    I have been using this code to save only specific sheet from the workbook. However, I now need to be able to save the entire workbook and save it as macro enabled file .xlsm

    HTML Code:
    Sub MakeFolder()
      Dim strVehNum As String, strPathDefault As String, strFolderTestInfo As String
      Dim strFolderPics As String, strFolderServiceComments As String
      Dim strFolderPrintouts As String, strPath As String, strGroup As String, FSO As New FileSystemObject
      Dim fn As String
      
      strVehNum = Range("B1") ' assumes vehicle number in B1
      strGroup = Range("B11") ' assumes group in B11
       strPath = "G:\03 PROJECTS\AUTOS\"
       strFolderTestInfo = "Test info"
      strFolderPics = "Pics"
      strFolderServiceComments = "Service comments"
      strFolderPrintouts = "Printouts"
      
      If Not FSO.FolderExists(strPath & strVehNum & "-" & strGroup) Then
        FSO.CreateFolder strPath & strVehNum & "-" & strGroup
        FSO.CreateFolder strPath & strVehNum & "-" & strGroup & "\" & strFolderTestInfo
        FSO.CreateFolder strPath & strVehNum & "-" & strGroup & "\" & strFolderPics
        FSO.CreateFolder strPath & strVehNum & "-" & strGroup & "\" & strFolderServiceComments
        FSO.CreateFolder strPath & strVehNum & "-" & strGroup & "\" & strFolderPrintouts
      End If
      
       fn = strPath & strVehNum & "-" & strGroup & "\" & strFolderTestInfo & "\TP " & strVehNum & ".xls"
      If Dir(fn) <> "" Then Exit Sub
    
    
      With Workbooks.Add(xlWBATWorksheet)
        ThisWorkbook.Worksheets("Test Plan").Copy after:=.Sheets(1)
        Application.DisplayAlerts = False
        .Sheets(1).Delete
        Application.DisplayAlerts = True
        On Error Resume Next
        Kill fn
        .SaveAs fn, xlExcel8
        .Close False
      End With
    End Sub

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Hi IvanTs8!
    Not sure, but you can try it:
    change
    .SaveAs fn, xlExcel8
    into
    .SaveAs fn, xlOpenXMLWorkbookMacroEnabled

  3. #3
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    and this line:
    fn = strPath & strVehNum & "-" & strGroup & "" & strFolderTestInfo & "\TP " & strVehNum & ".xlsm"

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    This code is sufficient:

    Sub M_snb()
       c00 = "03_PROJECTS\AUTOS\" & [B1] & "_" & [B11] & "\Test_Info\Pics\Service_comments\Printouts"
       c01 = "\TP_" & [B11] & ".xlsb"
       If Dir("G:\" & c00, 16) = "" Then CreateObject("shell.application").Namespace("G:").NewFolder c00
      
       If Dir("G:\" & c00 & c01) = "" Then ThisWorkbook.SaveAs "G:\" & c00 & c01, 50
    End Sub

  5. #5
    VBAX Regular
    Joined
    Dec 2018
    Posts
    11
    Location
    Quote Originally Posted by 大灰狼1976 View Post
    Hi IvanTs8!
    Not sure, but you can try it:
    change
    .SaveAs fn, xlExcel8
    into
    .SaveAs fn, xlOpenXMLWorkbookMacroEnabled
    Thanks! that worked to save the file as Macro Enabled file! I also added an array with the sheets i need copied and saved.
    Works great now!

    HTML Code:
        With Workbooks.Add(xlWBATWorksheet)
        ThisWorkbook.Sheets(Array("RTA", "Fahrzeugliste")).Copy after:=.Sheets(1)
        Application.DisplayAlerts = False
        .Sheets(1).Delete
        Application.DisplayAlerts = True
        On Error Resume Next
        Kill fn
        .SaveAs fn, xlOpenXMLWorkbookMacroEnabled
        .Close False
      End With
    End Sub

  6. #6
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    You're welcome

Posting Permissions

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