VBA to save a worksheet to a PDF in 2007

mortgageman

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
I tried to use the macro recorder for this. I have a workbook with 7 sheet. Lets call the sheets one, two, three, four, five, six, and seven.

I want to save 6 of these sheets as PDFs. Lets say the sheets that I want to save are two, three, ..., seven. I do not want the save as prompt as part of the macro (here is where I run into problems with the macro recorder). I just want to run the macro and have them all saved.
(Even better, I would like them saved to sub directory "My Online Files" - but that is a minor point that I could live without).

Thanks for any help here

Gene Klein
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
The following macro will create a PDF file for each worksheet in the active workbook, except sheet "one", and name each PDF file after it's corresponding sheet name...

Code: 
[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] CreatePDFs()

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] wkbSource [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    
    strPath = "C:\My Online Files\"
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
    
    [color=darkblue]Set[/color] wkbSource = ActiveWorkbook
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] wks [color=darkblue]In[/color] wkbSource.Worksheets
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] wks.Name
            [color=darkblue]Case[/color] "one"
                [color=green]'Do nothing[/color]
            [color=darkblue]Case[/color] [color=darkblue]Else[/color]
                wks.ExportAsFixedFormat xlTypePDF, strPath & wks.Name & ".pdf"
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
    [color=darkblue]Next[/color] wks
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]
 
Upvote 0
Domenic said:
The following macro will create a PDF file for each worksheet in the active workbook, except sheet "one", and name each PDF file after it's corresponding sheet name...

Code: 
[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] CreatePDFs()[/FONT]
 
[FONT=Courier New]   [COLOR=darkblue]Dim[/COLOR] strPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Courier New]   [COLOR=darkblue]Dim[/COLOR] wkbSource [COLOR=darkblue]As[/COLOR] Workbook[/FONT]
[FONT=Courier New]   [COLOR=darkblue]Dim[/COLOR] wks [COLOR=darkblue]As[/COLOR] Worksheet[/FONT]
 
[FONT=Courier New]   strPath = "C:\My Online Files\"[/FONT]
 
[FONT=Courier New]   [COLOR=darkblue]If[/COLOR] Right(strPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] strPath = strPath & "\"[/FONT]
 
[FONT=Courier New]   [COLOR=darkblue]Set[/COLOR] wkbSource = ActiveWorkbook[/FONT]
 
[FONT=Courier New]   [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] wks [COLOR=darkblue]In[/COLOR] wkbSource.Worksheets[/FONT]
[FONT=Courier New]       [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] wks.Name[/FONT]
[FONT=Courier New]           [COLOR=darkblue]Case[/COLOR] "one"[/FONT]
[FONT=Courier New]               [COLOR=green]'Do nothing[/COLOR][/FONT]
[FONT=Courier New]           [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR][/FONT]
[FONT=Courier New]               wks.ExportAsFixedFormat xlTypePDF, strPath & wks.Name & ".pdf"[/FONT]
[FONT=Courier New]       [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR][/FONT]
[FONT=Courier New]   [COLOR=darkblue]Next[/COLOR] wks[/FONT]
 
[FONT=Courier New][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]
Click to expand...

Uh Wow - Thanks.

Gene Klein
 
Upvote 0
Dom - I don't understand the need for this line:
Code: 
[COLOR=#00008b]If[/COLOR] Right(strPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] strPath = strPath & "\"

given that you have defined strPath already with this line:
Code: 
strPath = "C:\My Online Files\"


Gene Klein







Domenic said:
The following macro will create a PDF file for each worksheet in the active workbook, except sheet "one", and name each PDF file after it's corresponding sheet name...

Code: 
[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] CreatePDFs()[/FONT]
 
[FONT=Courier New]   [COLOR=darkblue]Dim[/COLOR] strPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Courier New]   [COLOR=darkblue]Dim[/COLOR] wkbSource [COLOR=darkblue]As[/COLOR] Workbook[/FONT]
[FONT=Courier New]   [COLOR=darkblue]Dim[/COLOR] wks [COLOR=darkblue]As[/COLOR] Worksheet[/FONT]
 
[FONT=Courier New]   strPath = "C:\My Online Files\"[/FONT]
 
[FONT=Courier New]   [COLOR=darkblue]If[/COLOR] Right(strPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] strPath = strPath & "\"[/FONT]
 
[FONT=Courier New]   [COLOR=darkblue]Set[/COLOR] wkbSource = ActiveWorkbook[/FONT]
 
[FONT=Courier New]   [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] wks [COLOR=darkblue]In[/COLOR] wkbSource.Worksheets[/FONT]
[FONT=Courier New]       [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] wks.Name[/FONT]
[FONT=Courier New]           [COLOR=darkblue]Case[/COLOR] "one"[/FONT]
[FONT=Courier New]               [COLOR=green]'Do nothing[/COLOR][/FONT]
[FONT=Courier New]           [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR][/FONT]
[FONT=Courier New]               wks.ExportAsFixedFormat xlTypePDF, strPath & wks.Name & ".pdf"[/FONT]
[FONT=Courier New]       [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR][/FONT]
[FONT=Courier New]   [COLOR=darkblue]Next[/COLOR] wks[/FONT]
 
[FONT=Courier New][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]
Click to expand...
 
Upvote 0
Uh Wow - Thanks.
Click to expand...

You're very welcome!

Dom - I don't understand the need for this line:
Code: 
[COLOR=#00008b]If[/COLOR] Right(strPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] strPath = strPath & "\"

given that you have defined strPath already with this line:
Code: 
strPath = "C:\My Online Files\"
Click to expand...

That's there simply to make sure that there's a backslash at the end of the path, just in case someone forgets to put one in -- whether the path is hard coded, as in this example, or taken from a cell reference.
 
Upvote 0
Help Dom - The code is bombing on this line:

Code: 
wks.ExportAsFixedFormat xlTypePDF, strPath & wks.Name & ".pdf"

I made some minor changes, directory names and such - I don't think that is the reason - of course I could be wrong. I reproduced the entire code below

Gene Klein

mortgageman said:
Dom - I don't understand the need for this line:
Code: 
[COLOR=#00008b]If[/COLOR] Right(strPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] strPath = strPath & "\"

given that you have defined strPath already with this line:
Code: 
strPath = "C:\My Online Files\"


Gene Klein
Click to expand...
Code: 
Sub CreatePDFs()
    'Can only be run in 2007 or higher
    Dim strPath As String
    Dim wkbSource As Workbook
    Dim wks As Worksheet
    
    strPath = "C:\My Documents\Broward College\MAT 1033 Online Class Documents\"
    
    
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    
    Set wkbSource = ActiveWorkbook
    
    For Each wks In wkbSource.Worksheets
        Select Case wks.Name
            Case "Entire Course for viewing"
                'Do nothing
            Case "Entire Course for printing"
                 'Do nothing
            Case Else
               wks.ExportAsFixedFormat xlTypePDF, strPath & wks.Name & ".pdf"
        End Select
    Next wks
    
End Sub
 
Upvote 0
What is the name of the worksheet when it bombs?
 
Upvote 0
So it looks like the problem does not lie in the sheet name, as Wigi suspected. I'm assuming that the path is correct, right? Which error do you get? Can you be specific?
 
Upvote 0
Domenic said:
So it looks like the problem does not lie in the sheet name, as Wigi suspected. I'm assuming that the path is correct, right? Which error do you get? Can you be specific?
Click to expand...

The error i get is:

Code: 
 Run Time Error 1004
Document not saved.  The document may be open, or an error may have been encountered when saving.

I have double checked the path for errors. However, I just checked and it does run if I change strPath to just be equal to "C:\"

I guess that means it has to be a typo?

Gene Klein
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Excel VBA Save PDF within workbook folder and send as email attachment
Replies
1
Views
311
rpaulson
rpaulson
M
  • Question
VBA to split worksheets going through motions and confirming saved in directory... but nothing there when I check!
Replies
2
Views
181
Joe4
Joe4
G
  • Question
Macro to consolidate multiple worksheets into Master based on cell data?
Replies
1
Views
319
bobaol
B
R
  • Question
Help with VBA - Save Invoice Worksheet as PDF - to Specific Folder | Filename Based on Cell Value
Replies
0
Views
132
RonBuss
R
D
  • Solved
Export and name sheets within a workbook to a text file in same directory as workbook
Replies
3
Views
551
rlv01
R

Forum statistics

Threads
1,215,734
Messages
6,126,542
Members
449,316
Latest member
sravya

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top