Save As Macro Script

[Neal]

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.

 

[Tom Ogilvy]

What happens with

fname = Application.GetSaveAsFileName()

 

[Neal]

Thanks Tom that was just what I needed to know!

What happens with

fname = Application.GetSaveAsFileName()

 

[Neal]

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]

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

 

[Neal]

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!

 

[Fanatik]

Try

Fname = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel Files
(*.xls), *.xls")