PDA

View Full Version : Using Save As to create a new workbook



ABabeNChrist
07-16-2011, 11:55 PM
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.

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

Aflatoon
07-17-2011, 03:17 PM
I doubt it since Macs don't use backslashes as path separators. I'd suggest you replace those with Application.PathSeparator.

ABabeNChrist
07-17-2011, 05:39 PM
I doubt it since Macs don't use backslashes as path separators. I'd suggest you replace those with Application.PathSeparator.

Something like this


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