Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Macro Script
I am building an Excel spreadsheet that will be used to produce Invoices and
Delivery Notes via a web based IntraNet site. Since the usual toolbars and menus are not available in the Internet Explorer embedded Excel; I have been creating macros to allow the users to Save and Print. The print commands work fine but I am not able to create a macro that will present the user with a Save As dialogue box. The alternative solution I thought up was to install a PDF writing printer driver on all of the workstations and write a macro that would automatically change the printer selection and initiate a print out on click, resulting in a Save As dialogue box that woud allow the user to save the spreadsheet in PDF format to the location of their choice. Sub Save_IDN() ' ' Save_IDN Macro ' Macro recorded 25/03/2006 by Neal ' Dim STDprinter As String STDprinter = Application.ActivePrinter Application.ActivePrinter = "IDN on CPW2:" ' change printer Sheets(Array("Invoice", "DeliveryNote")).Select Sheets("Invoice").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' prints the active sheet Application.ActivePrinter = "Canon PIXMA iP1500 on Ne00:" ' change back to standard printer End Sub Although this works fine on locally stored spreadsheets, it doesn't when the file is launched from the web. Does anybody know how I can get my PDF system to work or alternatively achieve a Save As dialogue box. Many thanks in advance for any replies. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Macro Script
What happens with
fname = Application.GetSaveAsFileName() -- Regards, Tom Ogilvy "Neal" wrote in message ... I am building an Excel spreadsheet that will be used to produce Invoices and Delivery Notes via a web based IntraNet site. Since the usual toolbars and menus are not available in the Internet Explorer embedded Excel; I have been creating macros to allow the users to Save and Print. The print commands work fine but I am not able to create a macro that will present the user with a Save As dialogue box. The alternative solution I thought up was to install a PDF writing printer driver on all of the workstations and write a macro that would automatically change the printer selection and initiate a print out on click, resulting in a Save As dialogue box that woud allow the user to save the spreadsheet in PDF format to the location of their choice. Sub Save_IDN() ' ' Save_IDN Macro ' Macro recorded 25/03/2006 by Neal ' Dim STDprinter As String STDprinter = Application.ActivePrinter Application.ActivePrinter = "IDN on CPW2:" ' change printer Sheets(Array("Invoice", "DeliveryNote")).Select Sheets("Invoice").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' prints the active sheet Application.ActivePrinter = "Canon PIXMA iP1500 on Ne00:" ' change back to standard printer End Sub Although this works fine on locally stored spreadsheets, it doesn't when the file is launched from the web. Does anybody know how I can get my PDF system to work or alternatively achieve a Save As dialogue box. Many thanks in advance for any replies. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Macro Script
Thanks Tom that was just what I needed to know!
"Tom Ogilvy" wrote: What happens with fname = Application.GetSaveAsFileName() -- Regards, Tom Ogilvy "Neal" wrote in message ... I am building an Excel spreadsheet that will be used to produce Invoices and Delivery Notes via a web based IntraNet site. Since the usual toolbars and menus are not available in the Internet Explorer embedded Excel; I have been creating macros to allow the users to Save and Print. The print commands work fine but I am not able to create a macro that will present the user with a Save As dialogue box. The alternative solution I thought up was to install a PDF writing printer driver on all of the workstations and write a macro that would automatically change the printer selection and initiate a print out on click, resulting in a Save As dialogue box that woud allow the user to save the spreadsheet in PDF format to the location of their choice. Sub Save_IDN() ' ' Save_IDN Macro ' Macro recorded 25/03/2006 by Neal ' Dim STDprinter As String STDprinter = Application.ActivePrinter Application.ActivePrinter = "IDN on CPW2:" ' change printer Sheets(Array("Invoice", "DeliveryNote")).Select Sheets("Invoice").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' prints the active sheet Application.ActivePrinter = "Canon PIXMA iP1500 on Ne00:" ' change back to standard printer End Sub Although this works fine on locally stored spreadsheets, it doesn't when the file is launched from the web. Does anybody know how I can get my PDF system to work or alternatively achieve a Save As dialogue box. Many thanks in advance for any replies. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Macro Script
I have just noticed, when I Save using that, it doesn't actually save
anywhere! Also it only lets you save as All Files, is there a way to make it save in Excel format and actually create a saved copy of the file? Thanks for your help so far Neal "Tom Ogilvy" wrote: What happens with fname = Application.GetSaveAsFileName() -- Regards, Tom Ogilvy "Neal" wrote in message ... I am building an Excel spreadsheet that will be used to produce Invoices and Delivery Notes via a web based IntraNet site. Since the usual toolbars and menus are not available in the Internet Explorer embedded Excel; I have been creating macros to allow the users to Save and Print. The print commands work fine but I am not able to create a macro that will present the user with a Save As dialogue box. The alternative solution I thought up was to install a PDF writing printer driver on all of the workstations and write a macro that would automatically change the printer selection and initiate a print out on click, resulting in a Save As dialogue box that woud allow the user to save the spreadsheet in PDF format to the location of their choice. Sub Save_IDN() ' ' Save_IDN Macro ' Macro recorded 25/03/2006 by Neal ' Dim STDprinter As String STDprinter = Application.ActivePrinter Application.ActivePrinter = "IDN on CPW2:" ' change printer Sheets(Array("Invoice", "DeliveryNote")).Select Sheets("Invoice").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' prints the active sheet Application.ActivePrinter = "Canon PIXMA iP1500 on Ne00:" ' change back to standard printer End Sub Although this works fine on locally stored spreadsheets, it doesn't when the file is launched from the web. Does anybody know how I can get my PDF system to work or alternatively achieve a Save As dialogue box. Many thanks in advance for any replies. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Macro Script
If you type GetSaveASFileName in a module, highlight it and hit F1, then
read the help - all your questions will be answered. No, it does not save at all. It gives you the power to use the SaveAs command to have more control over the situation. That is why I use fname = Application.GetSaveAsFilename() ' then Activeworkbook.SaveAs fName, xlWorkbook -- Regards, Tom Ogilvy "Neal" wrote in message ... I have just noticed, when I Save using that, it doesn't actually save anywhere! Also it only lets you save as All Files, is there a way to make it save in Excel format and actually create a saved copy of the file? Thanks for your help so far Neal "Tom Ogilvy" wrote: What happens with fname = Application.GetSaveAsFileName() -- Regards, Tom Ogilvy "Neal" wrote in message ... I am building an Excel spreadsheet that will be used to produce Invoices and Delivery Notes via a web based IntraNet site. Since the usual toolbars and menus are not available in the Internet Explorer embedded Excel; I have been creating macros to allow the users to Save and Print. The print commands work fine but I am not able to create a macro that will present the user with a Save As dialogue box. The alternative solution I thought up was to install a PDF writing printer driver on all of the workstations and write a macro that would automatically change the printer selection and initiate a print out on click, resulting in a Save As dialogue box that woud allow the user to save the spreadsheet in PDF format to the location of their choice. Sub Save_IDN() ' ' Save_IDN Macro ' Macro recorded 25/03/2006 by Neal ' Dim STDprinter As String STDprinter = Application.ActivePrinter Application.ActivePrinter = "IDN on CPW2:" ' change printer Sheets(Array("Invoice", "DeliveryNote")).Select Sheets("Invoice").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' prints the active sheet Application.ActivePrinter = "Canon PIXMA iP1500 on Ne00:" ' change back to standard printer End Sub Although this works fine on locally stored spreadsheets, it doesn't when the file is launched from the web. Does anybody know how I can get my PDF system to work or alternatively achieve a Save As dialogue box. Many thanks in advance for any replies. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Macro Script
Is there anyway to make the default extension .xls? Currently Any File is the
only option available and the people who will be using this will struggle to append .xls to the filename. Also is it possible to use the value saved in a field say the Invoice number and automatically have the Save As dialogue insert INVOICENUMBER.xls as the default filename, perhaps even to a preset location, say Invoices on C: Thanks for you help so far! "Tom Ogilvy" wrote: If you type GetSaveASFileName in a module, highlight it and hit F1, then read the help - all your questions will be answered. No, it does not save at all. It gives you the power to use the SaveAs command to have more control over the situation. That is why I use fname = Application.GetSaveAsFilename() ' then Activeworkbook.SaveAs fName, xlWorkbook -- Regards, Tom Ogilvy "Neal" wrote in message ... I have just noticed, when I Save using that, it doesn't actually save anywhere! Also it only lets you save as All Files, is there a way to make it save in Excel format and actually create a saved copy of the file? Thanks for your help so far Neal "Tom Ogilvy" wrote: What happens with fname = Application.GetSaveAsFileName() -- Regards, Tom Ogilvy "Neal" wrote in message ... I am building an Excel spreadsheet that will be used to produce Invoices and Delivery Notes via a web based IntraNet site. Since the usual toolbars and menus are not available in the Internet Explorer embedded Excel; I have been creating macros to allow the users to Save and Print. The print commands work fine but I am not able to create a macro that will present the user with a Save As dialogue box. The alternative solution I thought up was to install a PDF writing printer driver on all of the workstations and write a macro that would automatically change the printer selection and initiate a print out on click, resulting in a Save As dialogue box that woud allow the user to save the spreadsheet in PDF format to the location of their choice. Sub Save_IDN() ' ' Save_IDN Macro ' Macro recorded 25/03/2006 by Neal ' Dim STDprinter As String STDprinter = Application.ActivePrinter Application.ActivePrinter = "IDN on CPW2:" ' change printer Sheets(Array("Invoice", "DeliveryNote")).Select Sheets("Invoice").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' prints the active sheet Application.ActivePrinter = "Canon PIXMA iP1500 on Ne00:" ' change back to standard printer End Sub Although this works fine on locally stored spreadsheets, it doesn't when the file is launched from the web. Does anybody know how I can get my PDF system to work or alternatively achieve a Save As dialogue box. Many thanks in advance for any replies. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Macro Script
Try
Fname = Application.GetSaveAsFilename(fileFilter:="Microso ft Excel Files (*.xls), *.xls") "Neal" wrote: Is there anyway to make the default extension .xls? Currently Any File is the only option available and the people who will be using this will struggle to append .xls to the filename. Also is it possible to use the value saved in a field say the Invoice number and automatically have the Save As dialogue insert INVOICENUMBER.xls as the default filename, perhaps even to a preset location, say Invoices on C: Thanks for you help so far! "Tom Ogilvy" wrote: If you type GetSaveASFileName in a module, highlight it and hit F1, then read the help - all your questions will be answered. No, it does not save at all. It gives you the power to use the SaveAs command to have more control over the situation. That is why I use fname = Application.GetSaveAsFilename() ' then Activeworkbook.SaveAs fName, xlWorkbook -- Regards, Tom Ogilvy "Neal" wrote in message ... I have just noticed, when I Save using that, it doesn't actually save anywhere! Also it only lets you save as All Files, is there a way to make it save in Excel format and actually create a saved copy of the file? Thanks for your help so far Neal "Tom Ogilvy" wrote: What happens with fname = Application.GetSaveAsFileName() -- Regards, Tom Ogilvy "Neal" wrote in message ... I am building an Excel spreadsheet that will be used to produce Invoices and Delivery Notes via a web based IntraNet site. Since the usual toolbars and menus are not available in the Internet Explorer embedded Excel; I have been creating macros to allow the users to Save and Print. The print commands work fine but I am not able to create a macro that will present the user with a Save As dialogue box. The alternative solution I thought up was to install a PDF writing printer driver on all of the workstations and write a macro that would automatically change the printer selection and initiate a print out on click, resulting in a Save As dialogue box that woud allow the user to save the spreadsheet in PDF format to the location of their choice. Sub Save_IDN() ' ' Save_IDN Macro ' Macro recorded 25/03/2006 by Neal ' Dim STDprinter As String STDprinter = Application.ActivePrinter Application.ActivePrinter = "IDN on CPW2:" ' change printer Sheets(Array("Invoice", "DeliveryNote")).Select Sheets("Invoice").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' prints the active sheet Application.ActivePrinter = "Canon PIXMA iP1500 on Ne00:" ' change back to standard printer End Sub Although this works fine on locally stored spreadsheets, it doesn't when the file is launched from the web. Does anybody know how I can get my PDF system to work or alternatively achieve a Save As dialogue box. Many thanks in advance for any replies. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save as Visual Script Help | Excel Discussion (Misc queries) | |||
Save Excel File every wednesday VB Script Help | Excel Programming | |||
when i save xls file, debug script is running and canno't save fil | Excel Discussion (Misc queries) | |||
VBA Script for copy and paste to save | Excel Discussion (Misc queries) | |||
VB Script file save as with date | Excel Programming |