Consulting

Results 1 to 3 of 3

Thread: Using Save As to create a new workbook

  1. #1

    Using Save As to create a new workbook

    I use this piece of code to create a new workbook (Save As) on my PC and places new workbook into a folder in ActiveWorkbook.Path\Saved Reports. I do not have a MAC to test, and was wondering if this code below will function OK using excel 2011.

    [vba]Private Sub CommandButton16_Click()
    Dim strName As String
    Dim p As Integer
    If ActiveWorkbook.Name <> "Master.xlsm" Then
    MsgBox ("A new report has already been created, you may now continue to working on existing report")
    Exit Sub
    End If
    MsgBox "Please be patient as your new report is being created"
    Sheets("Report Information Log").Unprotect
    Sheets("Report Information Log").Range("E5") = Sheets("Report Information Log").Range("E5").Value + 1
    Sheets("Report Information Log").Range("E6") = Now
    ActiveWorkbook.Save
    Sheets("Report Information Log").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
    PassWord:="", UserInterFaceOnly:=True
    Dim nwb As String

    On Error GoTo EH

    Application.EnableEvents = False
    nwb = Application.InputBox("Enter new report name", _
    "Starting a new report", Type:=2)
    If nwb = "False" Then Exit Sub
    ThisWorkbook.SaveAs Filename:=ActiveWorkbook.Path & _
    "\Saved Reports\" & nwb, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    strName = ActiveWorkbook.Name
    p = InStrRev(strName, ".")
    strName = Left(strName, p - 1)
    MsgBox "Your new report named " & strName & " is now ready."
    Application.EnableEvents = True
    EH:
    Exit Sub
    End Sub
    [/vba]

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    I doubt it since Macs don't use backslashes as path separators. I'd suggest you replace those with Application.PathSeparator.

  3. #3
    Quote Originally Posted by Aflatoon
    I doubt it since Macs don't use backslashes as path separators. I'd suggest you replace those with Application.PathSeparator.
    Something like this

    [vba]
    ThisWorkbook.SaveAs Filename:=ActiveWorkbook.Path & _
    Application.PathSeparator & nwb, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    strName = ActiveWorkbook.Name[/vba]

Posting Permissions

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