Hi,

Using code I would like to open the save as dialog box, change the
path to which it saves and ideally change the save as type to PDF and
use the ThisWorkbook.Name

So far I've managed to open the dialog with the below but not sure how
to do the changing the path and file ext or if even possible for 2003.

Application.Dialogs(xlDialogSaveAs).Show

Any help would be appreciated

Danny

As far as I know, Excel 2003 has no ability to save files as a pdf.
The following saves the file in the specified folder, as a template (.xlt), under the name of
Danny...

Application.Dialogs(xlDialogSaveAs).Show Arg1:="D:\Icons\Arrows\Danny", Arg2:=17 'template
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Data Rows add-in: Custom Shading, Deleting, Inserting)




"VBA Noob"
wrote in message
...
Hi,

Using code I would like to open the save as dialog box, change the
path to which it saves and ideally change the save as type to PDF and
use the ThisWorkbook.Name

So far I've managed to open the dialog with the below but not sure how
to do the changing the path and file ext or if even possible for 2003.

Application.Dialogs(xlDialogSaveAs).Show

Any help would be appreciated

Danny

On Aug 15, 7:42*pm, "Jim Cone" wrote:
As far as I know, Excel 2003 has no ability to save files as a pdf.
The following saves the file *in the specified folder, as a template (.xlt), under the name of
Danny...

Application.Dialogs(xlDialogSaveAs).Show Arg1:="D:\Icons\Arrows\Danny", Arg2:=17 'template
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(Data Rows add-in: Custom Shading, Deleting,

Thanks for the reply Jim,

Maybe I've asked the wrong question here. My main goal is for the
excel workbook to be converted to PDF and saving would be a bonus.
Could I call the print dialog box and change printer name to their
version of PDF with something like Application.ActivePrinter = "?"

Do I need to known the version?

Danny
Danny

VBA Noob presented the following explanation :
On Aug 15, 7:42*pm, "Jim Cone" wrote:
As far as I know, Excel 2003 has no ability to save files as a pdf.
The following saves the file *in the specified folder, as a template (.xlt),
under the name of Danny...

Application.Dialogs(xlDialogSaveAs).Show Arg1:="D:\Icons\Arrows\Danny",
Arg2:=17 'template --
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(Data Rows add-in: Custom Shading, Deleting,

Thanks for the reply Jim,

Maybe I've asked the wrong question here. My main goal is for the
excel workbook to be converted to PDF and saving would be a bonus.
Could I call the print dialog box and change printer name to their
version of PDF with something like Application.ActivePrinter = "?"

Do I need to known the version?

Danny
Danny

You need to know the name and port. It might be easier to prompt the
user to set the active printer to the PDF printer the first time. This
will let you store the value for this user in the Registry via
SaveSetting, and retrieve it later via GetSetting. Have GetSetting
return a 'default' dummy string if the registry key is empty (or
doesn't exist). Testing for this will let your code determine if you
can set ActivePrinter or prompt the user to set it. Normally, I'd load
the ActivePrinter into a variable (sCurrentPrinter) at startup so I can
reset it after my code runs the PrintToPDF procedure.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Aug 15, 9:39*pm, GS wrote:
VBA Noob presented the following explanation :





On Aug 15, 7:42 pm, "Jim Cone" wrote:
As far as I know, Excel 2003 has no ability to save files as a pdf.
The following saves the file in the specified folder, as a template (.xlt),
under the name of Danny...

Application.Dialogs(xlDialogSaveAs).Show Arg1:="D:\Icons\Arrows\Danny",
Arg2:=17 'template --
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(Data Rows add-in: Custom Shading, Deleting,

Thanks for the reply Jim,

Maybe I've asked the wrong question here. *My main goal is for the
excel workbook to be converted to PDF and saving would be a bonus.
Could I call the print dialog box and change printer name to their
version of PDF with something like Application.ActivePrinter = "?"

Do I need to known the version?

Danny
Danny

You need to know the name and port. It might be easier to prompt the
user to set the active printer to the PDF printer the first time. This
will let you store the value for this user in the Registry via
SaveSetting, and retrieve it later via GetSetting. Have GetSetting
return a 'default' dummy string if the registry key is empty (or
doesn't exist). Testing for this will let your code determine if you
can set ActivePrinter or prompt the user to set it. Normally, I'd load
the ActivePrinter into a variable (sCurrentPrinter) at startup so I can
reset it after my code runs the PrintToPDF procedure.

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Garry,

Will try it tomorrow at work

Danny

Another option is to display a list of available printers and have the user choose the printer to
use.
Selecting an item changes the active printer...
'---
Sub PrinterSelectionDemo()
Dim bChoice As Boolean
bChoice = Application.Dialogs(xlDialogPrinterSetup).Show(Act ivePrinter)
If Not bChoice Then
MsgBox "User cancelled"
Else
MsgBox ActivePrinter
End If
End Sub

--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)

Jim Cone laid this down on his screen :
Another option is to display a list of available printers and have the user
choose the printer to use.
Selecting an item changes the active printer...
'---
Sub PrinterSelectionDemo()
Dim bChoice As Boolean
bChoice = Application.Dialogs(xlDialogPrinterSetup).Show(Act ivePrinter)
If Not bChoice Then
MsgBox "User cancelled"
Else
MsgBox ActivePrinter
End If
End Sub

Nice solution, Jim! Never thought of it myself but this clearly is the
best solution in the end. Thanks for sharing this...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Aug 15, 11:08*pm, GS wrote:
Jim Cone laid this down on his screen :

Another option is to display a list of available printers and have the user
choose the printer to use.
Selecting an item changes the active printer...
'---
Sub PrinterSelectionDemo()
*Dim bChoice As Boolean
*bChoice = Application.Dialogs(xlDialogPrinterSetup).Show(Act ivePrinter)
*If Not bChoice Then
* * MsgBox "User cancelled"
*Else
* *MsgBox ActivePrinter
*End If
End Sub

Nice solution, Jim! Never thought of it myself but this clearly is the
best solution in the end. Thanks for sharing this...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Jim,

I came up with this idea but user then wanted the tick whole workbook
option but can tweak you're code to do that.
If Application.ActivePrinter Like "*pdf*" Then
ActiveWorkbook.PrintOut Copies:=1
Else
Application.Dialogs(xlDialogPrint).Show
End If

Thanks again to both of you.

Danny

VBA Noob formulated on Tuesday :
On Aug 15, 11:08*pm, GS wrote:
Jim Cone laid this down on his screen :

Another option is to display a list of available printers and have the user
choose the printer to use.
Selecting an item changes the active printer...
'---
Sub PrinterSelectionDemo()
*Dim bChoice As Boolean
*bChoice = Application.Dialogs(xlDialogPrinterSetup).Show(Act ivePrinter)
*If Not bChoice Then
* * MsgBox "User cancelled"
*Else
* *MsgBox ActivePrinter
*End If
End Sub

Nice solution, Jim! Never thought of it myself but this clearly is the
best solution in the end. Thanks for sharing this...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Jim,

I came up with this idea but user then wanted the tick whole workbook
option but can tweak you're code to do that.
If Application.ActivePrinter Like "*pdf*" Then
ActiveWorkbook.PrintOut Copies:=1
Else
Application.Dialogs(xlDialogPrint).Show
End If

Thanks again to both of you.

Danny

Why not just show the dialog at all times? Then the user can choose
printer AND what to print all in one place!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Aug 16, 6:25*pm, GS wrote:
VBA Noob formulated on Tuesday :





On Aug 15, 11:08*pm, GS wrote:
Jim Cone laid this down on his screen :

Another option is to display a list of available printers and have the user
choose the printer to use.
Selecting an item changes the active printer...
'---
Sub PrinterSelectionDemo()
*Dim bChoice As Boolean
*bChoice = Application.Dialogs(xlDialogPrinterSetup).Show(Act ivePrinter)
*If Not bChoice Then
* * MsgBox "User cancelled"
*Else
* *MsgBox ActivePrinter
*End If
End Sub

Nice solution, Jim! Never thought of it myself but this clearly is the
best solution in the end. Thanks for sharing this...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Jim,

I came up with this idea but user then wanted the tick whole workbook
option but can tweak you're code to do that.
*If Application.ActivePrinter Like "*pdf*" Then
* * * * ActiveWorkbook.PrintOut Copies:=1
* * Else
* * * * Application.Dialogs(xlDialogPrint).Show
* * End If

Thanks again to both of you.

Danny

Why not just show the dialog at all times? Then the user can choose
printer AND what to print all in one place!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Garry,

The whole workbook needs to be printed as a PDF. Ideally I would
automatically want it to select the users PDF printer but different
users may have different versions so not sure if this is possible?
Jim's solution is close enough as whatever printer they select I will
code it so it will automatically priint the entire workbook so save
the user one last click.

Danny

Danny