Problems with making a .PDF from Excel using VBA

[pphardikar]

Hello Every intelligent soul out there,

For the past 15 days I have tried practically every site that google has thrown up whenever I have searched for help on using VBA to create a pre-defined excel sheet with varying data into different PDF files with a filename which can be generated by concatenation of values of few of the select cells in an entirely different sheet.

My problem is as follows:

1. I use MS Excel 2003 and an Adobe Acrobat Pro Writer version 8
2. I have an excel file wherein depending on the the value of a certain variable, I have to print either the sheet TAC_CEP_Gr1 or TAC_CEP_Gr2 into a .PDF file.
3. The file name is a combination of certain cell values. These keep on differing for each different instance of printing.
4. Path name has been frozen to "D:0_PPH\" for the time being.

I have tried worksheets(SheetName).Printout option, I have tried all possible source codes that claimed to solve such an issue, I have tried everything available on the net that I could lay my hands on.

Could anyone of you tell me what exact code could help me do this - I even tried printer setting to Adobe PDF on Ne05: etc.

Regards - Prasad

 

[Worf]

Hello Prasad
See if this example is useful. If it doesn’t work with Excel 03 I can post another version later, right now I’m on an Office 07 machine.

Sub CreatePDF()         ' tested with Excel 2007
Dim what_sheet%, pn$, fn$, ss$


what_sheet = 2
Select Case what_sheet
    Case 1
        ss = "TAC1"         ' sheet name
    Case 2
        ss = "TAC2"
End Select
pn = "C:\Accounts\"          ' type your path here


' file name
fn = Sheets("Sheet1").Range("d4").Value & Sheets("Sheet1").Range("d5").Value


ThisWorkbook.Worksheets(ss).ExportAsFixedFormat Type:=xlTypePDF, filename:=pn & fn & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
    
End Sub

 

[pphardikar]

Dear Worf,

Thanks a million. I have tried this. Worksheets.ExportAsFixedFormat does not work with Excel 2003 that I have.

I shall wait for your other version.

Honestly, I have practically tried everything that google throws up when I search for the solution to my problem.

I simply do not seem to be able to get anything to work.

Should you want me to send you the code, I will do so on Monday.

Regards - Prasad

Hello Prasad
See if this example is useful. If it doesn’t work with Excel 03 I can post another version later, right now I’m on an Office 07 machine.

Sub CreatePDF()         ' tested with Excel 2007
Dim what_sheet%, pn$, fn$, ss$


what_sheet = 2
Select Case what_sheet
    Case 1
        ss = "TAC1"         ' sheet name
    Case 2
        ss = "TAC2"
End Select
pn = "C:\Accounts\"          ' type your path here


' file name
fn = Sheets("Sheet1").Range("d4").Value & Sheets("Sheet1").Range("d5").Value


ThisWorkbook.Worksheets(ss).ExportAsFixedFormat Type:=xlTypePDF, filename:=pn & fn & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
    
End Sub

 

[Worf]

I simply do not seem to be able to get anything to work.

Here is why, from the Internet:

The latest Microsoft Office supports saving files as PDF within the program. However, if you are using older versions, like Office 2003 or Office 2007, and don't plan to upgrade your Microsoft Office to Office 2010 or Office 2013, you will not be able to save files as PDF directly.

In Microsoft Office 2003, there is no such option to convert Word, Excel or PowerPoint to PDF; while in Microsoft Office 2007, you need to install an add-in before you can use the PDF converting feature.

If you have Office 03 on a personal machine, you could consider an upgrade. Another option is to use third party software to create the PDF file; the following program claims to do it (I never tested it):

Excel to PDF Converter - Convert XLS and XLSX to PDF for Free!