Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose File To SaveAs
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 Befo=wkbNewInvoice.Sheets(1) Set FD = Application.FileDialog(msoFileDialogSaveAs) With FD '.Filters.Add "*.xls", 1 .InitialView = msoFileDialogViewDetails .InitialFileName = wkbCurrent.Worksheets("Variables").Range("Invoices Path").Value If .Show Then .Execute End If End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose File To SaveAs
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" -- Cheers Nigel "John" wrote in message ... 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 Befo=wkbNewInvoice.Sheets(1) Set FD = Application.FileDialog(msoFileDialogSaveAs) With FD '.Filters.Add "*.xls", 1 .InitialView = msoFileDialogViewDetails .InitialFileName = wkbCurrent.Worksheets("Variables").Range("Invoices Path").Value If .Show Then .Execute End If End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose File To SaveAs
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. "Nigel" wrote: 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" -- Cheers Nigel "John" wrote in message ... 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 Befo=wkbNewInvoice.Sheets(1) Set FD = Application.FileDialog(msoFileDialogSaveAs) With FD '.Filters.Add "*.xls", 1 .InitialView = msoFileDialogViewDetails .InitialFileName = wkbCurrent.Worksheets("Variables").Range("Invoices Path").Value If .Show Then .Execute End If End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose File To SaveAs
Looking at the code, isn't that the workbook that he says he wants to save
but says doesn't get saved. -- Regards, Tom Ogilvy "JLGWhiz" wrote: 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. "Nigel" wrote: 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" -- Cheers Nigel "John" wrote in message ... 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 Befo=wkbNewInvoice.Sheets(1) Set FD = Application.FileDialog(msoFileDialogSaveAs) With FD '.Filters.Add "*.xls", 1 .InitialView = msoFileDialogViewDetails .InitialFileName = wkbCurrent.Worksheets("Variables").Range("Invoices Path").Value If .Show Then .Execute End If End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose File To SaveAs
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 "John" wrote in message ... 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 Befo=wkbNewInvoice.Sheets(1) Set FD = Application.FileDialog(msoFileDialogSaveAs) With FD '.Filters.Add "*.xls", 1 .InitialView = msoFileDialogViewDetails .InitialFileName = wkbCurrent.Worksheets("Variables").Range("Invoices Path").Value If .Show Then .Execute End If End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose File To SaveAs
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. -- Regards, Tom Ogilvy "John" wrote in message ... 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 "John" wrote in message ... 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 Befo=wkbNewInvoice.Sheets(1) Set FD = Application.FileDialog(msoFileDialogSaveAs) With FD '.Filters.Add "*.xls", 1 .InitialView = msoFileDialogViewDetails .InitialFileName = wkbCurrent.Worksheets("Variables").Range("Invoices Path").Value If .Show Then .Execute End If End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose File To SaveAs
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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "John" wrote in message ... 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 "John" wrote in message ... 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 Befo=wkbNewInvoice.Sheets(1) Set FD = Application.FileDialog(msoFileDialogSaveAs) With FD '.Filters.Add "*.xls", 1 .InitialView = msoFileDialogViewDetails .InitialFileName = wkbCurrent.Worksheets("Variables").Range("Invoices Path").Value If .Show Then .Execute End If End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allowing user to choose to accept saveas option | Excel Discussion (Misc queries) | |||
Confused here Prevent Saving File - but allow ONLY File SAVEAS Met | Excel Discussion (Misc queries) | |||
How can I make File-Save , File-SaveAs Menu disabled? | Excel Programming | |||
Saveas csv file | Excel Programming | |||
Saveas with name of another file | Excel Programming |