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()

--
Regards,
Tom Ogilvy


"Neal" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Neal]

Thanks Tom that was just what I needed to know!

"Tom Ogilvy" wrote:

> What happens with
>
> fname = Application.GetSaveAsFileName()
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Neal" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

[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" wrote:

> What happens with
>
> fname = Application.GetSaveAsFileName()
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Neal" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >

[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!

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > >
> > >
> > >
>
>
>

[Fanatik]

Try

Fname = Application.GetSaveAsFilename(fileFilter:="Microsoft 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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.
> > > >
> > > >
> > > >
> >
> >
> >