Choose File To SaveAs

[John]

Hi there,

I'm having a bit of trouble with a Save As file dialog. The problem is that
I'm creating a copy of a workbook and it's the new copy that I want to "save
as", but the original workbook is the one getting saved! Can anyone tell me
how to ensure that the copy gets saved and ond not the other way round?

Thanks in advance

John


Code as follows:

Public Sub SaveInvoice()
Dim FD As FileDialog
Dim wkbNewInvoice As Workbook
Dim wkbCurrent As Workbook

Set wkbCurrent = ThisWorkbook
Set wkbNewInvoice = Application.Workbooks.Add
wkbCurrent.Worksheets("Invoice").Copy Before:=wkbNewInvoice.Sheets(1)
Set FD = Application.FileDialog(msoFileDialogSaveAs)

With FD
'.Filters.Add "*.xls", 1
.InitialView = msoFileDialogViewDetails
.InitialFileName =
wkbCurrent.Worksheets("Variables").Range("InvoicesPath").Value
If .Show Then
.Execute
End If
End With

End Sub

 

[Nigel]

If you open a workbook and then use SaveAs you save the open book as the new
name - it then becomes the open book.

So the sequence might be....

Open Book1
SaveAs Book2
Open Book1

You now have two workbooks open Book1 the original and Book2 the
copy........

Code.....

Workbooks.Open Filename:="C:\Book1.xls"
ActiveWorkbook.SaveAs Filename:="C:\Book2.xls"
Workbooks.Open Filename:="C:\Book1.xls"

 

[JLGWhiz]

Be sure that when your code executes the Save As, that your code has the
revised data in the active worksheet/workbook. The Save As will apply to the
last worksheet/workbook that the code activated or selected.

 

[Tom Ogilvy]

Looking at the code, isn't that the workbook that he says he wants to save
but says doesn't get saved.

 

[John]

Thanks very much for your help you guys.

I agree with Tom, that the code did make the new workbook the active one,
but just to make sure I tried adding:

wkbNewInvoice.Activate

.....before the "Set FD = App......" line.

This had mixed results. Sometimes as you step through the code it saved the
new workbook and other times the original! Not sure if I need some knid of
DoEvents in there?

In any case I've now opted to use an InputBox and the SaveAs method as Nigel
was suggesting. At least I know for sure which one I'm referencing.

Anyway, thanks once again to you all for your help.

Best regards

John

 

[Tom Ogilvy]

Inputbox would be clumsy

Dim fName as String
fname = Application.GetSaveAsFileName
if fName <> "False" then
activeworkbook.SaveAs fName

See the Excel VBA help on GetSaveAsFileName for options similar to what you
used.

 

[John]

Hi Tom. Many thanks for that. I've used the "GetSaved....." as you
suggested and of course you're right, it's much neater.

Thanks for your time.

Best regards

John