Save As PDF Macro with a twist

[flyersny81]

I am a beginner at Excel here and i realize the potential for this program is crazy high. I need to create a macro that will allow me to save a PDF from an XL file I am creating by running a report out of Access. This report yields a workbook with 2 sheets in it. The first tab (sheet) called "Mishkon" and the second called "Women's League". I need to save these reports separately as PDFs and they need to be saved in seperate locations. The first sheet (Mishkon) needs to be saved here: \\OS\OFS\Data\DayHab\Mishkon . The second sheet needs to be saved here: \\OS\OFS\Data\DayHab\Women's League . Here is the twist... Both sheets need to take their name from their G3 cells. I have Excel 2010 and dont want to print from my Adobe PDF printer. I would like to save as a PDF so I can run it off of computers that dont have the Adobe PDF printer installed. I have searched through the forums and found some code to try out but nothing that I could really understand to change to fit my need here. Any help would be huge and greatly appreciated. I have come to this site many times before for some great advice and answers but this time I guess I have a tailored problem.

TIA

 

[flyersny81]

I was found part of the solution. This solves the save as PDF for me... but thats it. It doesnt save it in the right location, only where the XL file itself is saved, doesnt save it as the right name and doest address the 2 sheets issue.

[COLOR=Navy]Sub[/COLOR] Save_as_pdf()[COLOR=Navy]Dim[/COLOR] FSO [COLOR=Navy]As[/COLOR] [COLOR=Navy]Object[/COLOR][COLOR=Navy]Dim[/COLOR] s(1) [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR][COLOR=Navy]Dim[/COLOR] sNewFilePath [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]    [COLOR=Navy]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")    s(0) = ThisWorkbook.FullName        [COLOR=Navy]If[/COLOR] FSO.FileExists(s(0)) [COLOR=Navy]Then[/COLOR]        [COLOR=SeaGreen]'//Change Excel Extension to PDF extension in FilePath[/COLOR]        s(1) = FSO.GetExtensionName(s(0))        [COLOR=Navy]If[/COLOR] s(1) <> "" [COLOR=Navy]Then[/COLOR]            s(1) = "." & s(1)            sNewFilePath = Replace(s(0), s(1), ".pdf")                        [COLOR=SeaGreen]'//Export to PDF with new File Path[/COLOR]            ActiveSheet.ExportAsFixedFormat _                Type:=xlTypePDF, _                Filename:=sNewFilePath, _                Quality:=xlQualityStandard, IncludeDocProperties:=True, _                IgnorePrintAreas:=False, OpenAfterPublish:=True        [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]    [COLOR=Navy]Else[/COLOR]        [COLOR=SeaGreen]'//Error: file path not found[/COLOR]        MsgBox "Error: this workbook may be unsaved.  Please save and try again."    [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]        [COLOR=Navy]Set[/COLOR] FSO = [COLOR=Navy]Nothing[/COLOR][COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]</pre>

 

[flyersny81]

i suck at this posting thing. sorry.

 

[flyersny81]

then i found this which addresses the naming and the where but not the two sheets. but it has a condition that A27 needs to be something other than 0. i tried to edit it out and it errored on me.

Private Sub CreatePDF_Click()
If Range(“A27”).Value <> 0
ThenActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
       "C:\docs\" & Sheets("Summary").Range("F16").Value & ".pdf", _
       Quality:=xlQualityStandard, _
       IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
     Else Exit Sub

End If
End Sub

 

[flyersny81]

sorry... here it is:

[COLOR=Navy]Sub[/COLOR] Save_as_pdf()
[COLOR=Navy]Dim[/COLOR] FSO [COLOR=Navy]As[/COLOR] [COLOR=Navy]Object
[/COLOR][COLOR=Navy]Dim[/COLOR] s(1) [COLOR=Navy]As[/COLOR] [COLOR=Navy]String
[/COLOR][COLOR=Navy]Dim[/COLOR] sNewFilePath [COLOR=Navy]As[/COLOR] [COLOR=Navy]String
[/COLOR]   [COLOR=Navy]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
   s(0) = ThisWorkbook.FullName
       [COLOR=Navy]If[/COLOR] FSO.FileExists(s(0)) [COLOR=Navy]Then
[/COLOR]       [COLOR=SeaGreen]'//Change Excel Extension to PDF extension in FilePath
[/COLOR]       s(1) = FSO.GetExtensionName(s(0))
       [COLOR=Navy]If[/COLOR] s(1) <> "" [COLOR=Navy]Then
[/COLOR]           s(1) = "." & s(1)
           sNewFilePath = Replace(s(0), s(1), ".pdf")
                       [COLOR=SeaGreen]'//Export to PDF with new File Path
[/COLOR]           ActiveSheet.ExportAsFixedFormat _
               Type:=xlTypePDF, _
               Filename:=sNewFilePath, _
               Quality:=xlQualityStandard, IncludeDocProperties:=True, _
               IgnorePrintAreas:=False, OpenAfterPublish:=True
       [COLOR=Navy]End[/COLOR] [COLOR=Navy]If
[/COLOR]   [COLOR=Navy]Else
[/COLOR]       [COLOR=SeaGreen]'//Error: file path not found
[/COLOR]       MsgBox "Error: this workbook may be unsaved.  Please save and try again."
   [COLOR=Navy]End[/COLOR] [COLOR=Navy]If
[/COLOR]       [COLOR=Navy]Set[/COLOR] FSO = [COLOR=Navy]Nothing
[/COLOR][COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub
[/COLOR]

 

[flyersny81]

anyone?