VBA to save a worksheet to a PDF in 2007

[mortgageman]

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

 

[wigi]

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:\"

Use again Debug.Print, for instance

Debug.Print strPath & wks.Name & ".pdf"</pre>

It should not be impossible to check manually if that is correct or not.
I guess this will be the problem.

Or maybe you are not allowed to write in the said directory?

 

[Domenic]

I guess that means it has to be a typo?

More than likely...

 

[wigi]

More than likely...

You would think that typo's are spotted easily with a Debug.Print for someone approaching 2,000 posts over here. Hmmm.

 

[Domenic]

You would think that typo's are spotted easily with a Debug.Print for someone approaching 2,000 posts over here. Hmmm.

 

[mortgageman]

You would think that typo's are spotted easily with a Debug.Print for someone approaching 2,000 posts over here. Hmmm.

How would a debug.print reveal that I am not allowed to write to the directory - your suggestion above?

Gene Klein

 

[wigi]

Whether you have Write rights to the directory or not, is easily verified and should not be done with a Debug.Print: browse to the directory and try to make a new file there (a simple text file suffices).

 

[Domenic]

The following code is amended to check whether the specified path exists...

Sub CreatePDFs()
    
    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 & "\"
    
    [COLOR="Red"]If Len(Dir(strPath, vbDirectory)) = 0 Then
        MsgBox "The path does not exist!", vbExclamation
        Exit Sub
    End If[/COLOR]
    
    Set wkbSource = ActiveWorkbook
    
    For Each wks In wkbSource.Worksheets
        Select Case wks.Name
            Case "Entire Course for viewing", "Entire Course for printing"
                 'Do nothing
            Case Else
               wks.ExportAsFixedFormat xlTypePDF, strPath & wks.Name & ".pdf"
        End Select
    Next wks
    
End Sub

 

[mortgageman]

The following code is amended to check whether the specified path exists...

Sub CreatePDFs()
 
    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 & "\"
 
    [COLOR=red]If Len(Dir(strPath, vbDirectory)) = 0 Then[/COLOR]
[COLOR=red]       MsgBox "The path does not exist!", vbExclamation[/COLOR]
[COLOR=red]       Exit Sub[/COLOR]
[COLOR=red]   End If[/COLOR]
 
    Set wkbSource = ActiveWorkbook
 
    For Each wks In wkbSource.Worksheets
        Select Case wks.Name
            Case "Entire Course for viewing", "Entire Course for printing"
                 'Do nothing
            Case Else
               wks.ExportAsFixedFormat xlTypePDF, strPath & wks.Name & ".pdf"
        End Select
    Next wks
 
End Sub

You are the best Dom. Thanks

Gene Klein

 

[Domenic]

You are the best Dom. Thanks

Gene Klein

You're very welcome! Glad I could help!

 

[mortgageman]

You're very welcome! Glad I could help!

It seems that if you do not "tell" vba anything other than worksheet name, it gives you some extra pages at the end when it creates the PDF. Is there anyway to fix that? I.e can you add to the code a selection?

Gene Klein