Save As Macro Script

What happens with

fname = Application.GetSaveAsFileName()

--
Regards,
Tom Ogilvy


"Neal" <Neal@discussions.microsoft.com> wrote in message
news:43130EF6-7F55-4C8D-9DA8-8D766E0053AD@microsoft.com...
> 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.

Thanks Tom that was just what I needed to know!

"Tom Ogilvy" wrote:

> What happens with
>
> fname = Application.GetSaveAsFileName()
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Neal" <Neal@discussions.microsoft.com> wrote in message
> news:43130EF6-7F55-4C8D-9DA8-8D766E0053AD@microsoft.com...
> > 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.
>
>
>

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" <Neal@discussions.microsoft.com> wrote in message
> news:43130EF6-7F55-4C8D-9DA8-8D766E0053AD@microsoft.com...
> > 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.
>
>
>

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" <Neal@discussions.microsoft.com> wrote in message
news:551505AE-84D3-4CC9-89B1-0E5E2BF8D3FC@microsoft.com...
> 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" <Neal@discussions.microsoft.com> wrote in message
> > news:43130EF6-7F55-4C8D-9DA8-8D766E0053AD@microsoft.com...
> > > 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.
> >
> >
> >

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" <Neal@discussions.microsoft.com> wrote in message
> news:551505AE-84D3-4CC9-89B1-0E5E2BF8D3FC@microsoft.com...
> > 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" <Neal@discussions.microsoft.com> wrote in message
> > > news:43130EF6-7F55-4C8D-9DA8-8D766E0053AD@microsoft.com...
> > > > 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.
> > >
> > >
> > >
>
>
>

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" <Neal@discussions.microsoft.com> wrote in message
> > news:551505AE-84D3-4CC9-89B1-0E5E2BF8D3FC@microsoft.com...
> > > 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" <Neal@discussions.microsoft.com> wrote in message
> > > > news:43130EF6-7F55-4C8D-9DA8-8D766E0053AD@microsoft.com...
> > > > > 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.
> > > >
> > > >
> > > >
> >
> >
> >