VBA - Saving new Excel File via SaveAs and then opening this file
Hi there,
I have a file that runs a macro on startup which generates sample sheets and then prompts for the file to be saved as a new copy. However I want the new file to be opened after the copy is made. Can anyone help me as to how I do this. Thanks.
I have a file that runs a macro on startup which generates sample sheets and then prompts for the file to be saved as a new copy. However I want the new file to be opened after the copy is made. Can anyone help me as to how I do this. Thanks.
Private Sub quoteOk_Click()
'Hide the information prompt
QuoteDialog.Hide
Filename = txtRef.Value
Dim withPath As String
'Apply values from form dialog
Set sh = Sheets("Quotation")
sh.Range("D1").Value = txtRef.Value
sh.Range("D4").Value = txtPrepared.Value
sh.Range("D5").Value = txtFor.Value
sh.Range("D3").Value = Date
withPath = "P:\Goodwin NoReVa Tech Dept\Estimating\Job Pricing\A Numbers-Enquiries\" + Filename
InputLines = txtLines.Value
ItemNo = txtLines.Value
'Do the row realted stuff
While InputLines > 1
InputLines = InputLines - 1
Sheets("Quotation").Select
Rows("13:13").Select
Selection.Copy
Selection.Cells(1, 1) = ItemNo
ItemNo = ItemNo - 1
Selection.Insert shift:=xlDown
Wend
sh.Range("A13").Value = 1
'If filename doesnt contain master, save as new file
If InStr(StrConv(FileNameNoExt(ThisWorkbook.FullName), vbUpperCase), "MASTER") Then
SaveAs (withPath)
End If
End Sub
Unless the code closes the file after it's created it should still be open, to you shouldn't need to re-open it.
In fact re-opening it might cause problems.
In fact re-opening it might cause problems.
ASKER
Oops sorry I forgot that was a customer function
Sub SaveAs(initialFilename As String)
With Application.FileDialog(msoFileDialogSaveAs)
.AllowMultiSelect = False
.ButtonName = "&Save As"
.initialFilename = initialFilename
.Title = "File Save As"
.Execute
.Show
End With
End Sub
Exactly my thoughts and hence I wanted to be sure by seeing SaveAs (withPath) :)
Sid
Sid
You are not doing any saving in that Sub()
Do you want the code for that?
Sid
Do you want the code for that?
Sid
wellso
Whatever you do you should not call a sub SaveAs - that's a Excel VBA method.
If you are having trouble with the 'real' SaveAs method naming the sub that could be what's causing the problems.
Whatever you do you should not call a sub SaveAs - that's a Excel VBA method.
If you are having trouble with the 'real' SaveAs method naming the sub that could be what's causing the problems.
If I understand correctly then you cannot (If I am not Wrong) save the 'Active Workbook' with a different name and keep it closed at the same time. The moment you do a Save As, The new copy will open.
Sid
Sid
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>>When you save a file it doesn't close
That what I said :)
Sid
That what I said :)
Sid
ASKER
I was using the custom method becuase I require the dialog to be popped up, the regular SaveAs eas just saving without prompting. I will try the suggestions now, thanks for all the help.
wellso: Just to be clear, Say if you have File A open and you do a 'Save As' the File A will be saved as File B (which will remain open) and File A will Close.
Sid
Sid
ASKER
I can see that my custom SaveAs was cuasing the newly saved sheet now to open, but as not all computers have access to the network path I need the dialog to be present, as when it is not mapped I just get a break in the script. Can anyone shed any light on this last step?
Why not use Application.SaveAs
Sid
Sid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If all the users have access/permission to the shared folder/drive then you could try using the UNC instead of the mapped drive letter.
It would look something lik this:
\\servername\sharename\fol
If you have the mapped drive yourself you should find it by right clicking or going to Map network drives...
It would look something lik this:
\\servername\sharename\fol
If you have the mapped drive yourself you should find it by right clicking or going to Map network drives...
ASKER
When I do Application.GetSaveAsFilen
>>>orignal file remains open.
That's strange as the original file automatically should close and the new "Save As" File should remain open.
Sid
That's strange as the original file automatically should close and the new "Save As" File should remain open.
Sid
Do you want to reopen the original workbook once you've created the sample sheets and saved a copy of it to another location?
imnorie: It is already open as per OP.
Sid
Sid
Sid
The original file without the sample sheets that have been created might not be open, because the file that is open is the that file plus the sample sheets.
The original file without the sample sheets that have been created might not be open, because the file that is open is the that file plus the sample sheets.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for both your assistance
Do not use '+' to concatenate :) It is for 'Addition'. For concatenating use '&' for example this line
withPath = "P:\Goodwin NoReVa Tech Dept\Estimating\Job Pricing\A Numbers-Enquiries\" + Filename
Should be
Open in new window
Can you show the code for SaveAs (withPath)?
Sid