• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 504
  • Last Modified:

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.

 
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

Open in new window

0
wellso
Asked:
wellso
  • 10
  • 6
  • 6
3 Solutions
 
SiddharthRoutCommented:
First things first.

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

withPath = "P:\Goodwin NoReVa Tech Dept\Estimating\Job Pricing\A Numbers-Enquiries\" & Filename

Open in new window


Can you show the code for SaveAs (withPath)?

Sid
0
 
NorieCommented:
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.
0
 
wellsoAuthor Commented:
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

Open in new window

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
SiddharthRoutCommented:
Exactly my thoughts and hence I wanted to be sure by seeing SaveAs (withPath) :)

Sid
0
 
SiddharthRoutCommented:
You are not doing any saving in that Sub()

Do you want the code for that?

Sid
0
 
NorieCommented:
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.
0
 
SiddharthRoutCommented:
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
0
 
NorieCommented:
Sid

When you save a file it doesn't close.

So if there is no code to close it you don't need any code to open it.
0
 
SiddharthRoutCommented:
>>>When you save a file it doesn't close

That what I said :)

Sid
0
 
wellsoAuthor Commented:
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.
0
 
SiddharthRoutCommented:
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
0
 
wellsoAuthor Commented:
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?
0
 
SiddharthRoutCommented:
Why not use Application.SaveAs

Sid
0
 
SiddharthRoutCommented:
Sorry I mean

 Application.GetSaveAsFilename

Sid
0
 
NorieCommented:
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\folderpath

If you have the mapped drive yourself you should find it by right clicking or going to Map network drives...
0
 
wellsoAuthor Commented:
When I do  Application.GetSaveAsFilename I get the same problem as before, the sheet saves but the orignal file remains open.
0
 
SiddharthRoutCommented:
>>>orignal file remains open.

That's strange as the original file automatically should close and the new "Save As" File should remain open.

Sid
0
 
NorieCommented:
Do you want to reopen the original workbook once you've created the sample sheets and saved a copy of it to another location?
0
 
SiddharthRoutCommented:
imnorie: It is already open as per OP.

Sid
0
 
NorieCommented:
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.
0
 
wellsoAuthor Commented:
Right got it sorted. Found that if I used Application.Dialogs(xlDialogSaveAs).Show everything worked as expected, but still unsure why the above solutions didnt work. Thanks for all the help in getting me there though!
0
 
wellsoAuthor Commented:
Thanks for both your assistance
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now