dcsimg
Results 1 to 10 of 10

Thread: Excel to pdf

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2012
    Location
    Essex
    Posts
    273

    Excel to pdf

    Hi All

    Could someone please help me to convert the code below to do excel instead of word please.
    Original Code
    Code:
    Call ConvertToPDF("C:\Users\stephenbarber\Documents\test.docx", "C:\Users\stephenbarber\Documents\Pre Manual.pdf")
    End Sub
    
    
    Public Sub ConvertToPDF(sInputPath As String, sOutputPath As String)
        'type to be saved
        Const wdFormatPDF As Integer = 17
        
        Dim wdApp As Object
        Dim wdDoc As Object
        
        'open the file
        Set wdApp = CreateObject("Word.Application")
        Set wdDoc = wdApp.Documents.Open(sInputPath)
        
        'convert it
        Call wdDoc.saveas2(sOutputPath, wdFormatPDF)
        
        'close and clean
        wdDoc.Close
        wdApp.Quit
        
        Set wdDoc = Nothing
        Set wdApp = Nothing
    End Sub
    My Attempt
    get error "object doesn't support this property or method" on line in red below

    Code:
    Public Sub ConvertToPDF2(sInputPath2 As String, sOutputPath2 As String)
          'type to be saved
        Const ExcelFormatPDF As Integer = 17
       
       Dim xl As Excel.Application
       Dim xlSheet As Excel.Worksheet
       Set xl = CreateObject("excel.Application")
       xl.Workbooks.Open (sInputPath2)
       Set xlSheet = xl.Sheets("Sheet1")
       xl.Visible = False
      
        
        'convert it
        Call xl.saveas2(sOutputPath2, ExcelFormatPDF)
        
        'close and clean
      xl.ActiveWorkbook.Close
       xl.Application.Quit
       Set xlSheet = Nothing
      
    End Sub
    Thank You
    Steve
    Last edited by sbarber007; Nov 16th, 2019 at 11:21 AM.

  2. #2
    Addicted Member
    Join Date
    Dec 2008
    Location
    Argentina
    Posts
    210

    Re: Excel to pdf

    hi, see the function ExportAsFixedFormat

    Code:
        xlSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=sOutputPath2, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    Last edited by LeandroA; Nov 16th, 2019 at 12:03 PM.
    leandroascierto.com Visual Basic 6 projects

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2012
    Location
    Essex
    Posts
    273

    Re: Excel to pdf

    @LeandroA
    Thank you for your help that worked, but for some reason when converted using the code above the excel page goes cross two pages in pdf file, if I do it manually in excel it saves in the pdf file has 1 Page is there anything I can do in your code to prevent this.

    Regards
    Steve

  4. #4
    Frenzied Member
    Join Date
    Mar 2008
    Posts
    1,146

    Re: Excel to pdf

    Excel's Macro Recorder is your friend. Have you tried it yet?

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2012
    Location
    Essex
    Posts
    273

    Re: Excel to pdf

    @Magic Ink

    Interesting where do I start with that

    Steve

  6. #6
    Frenzied Member
    Join Date
    Mar 2008
    Posts
    1,146

    Re: Excel to pdf

    Excel - Developer menu/ Ribbon* - Click 'Record Macro'
    Manually carry out the actions required
    When done, Drop the Developer menu/ Ribbon again - Click 'Stop Recording' - Click 'Macros' (the Macros dialog opens) - Select the Macro name required (probably Macro1 on your first try) - Click 'Edit' (the code recorded is displayed)
    Adapt the recorded code to run in vb6.

    *If the Developer menu/ Ribbon is not displayed drop the Excel File menu/ Ribbon - Select Options (the options dialog is displayed) - Click 'Customize Ribbon' - In the 'Main tabs' section ensure the 'Developer' check box is ticked.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2012
    Location
    Essex
    Posts
    273

    Re: Excel to pdf

    hi

    I have copied the contents of the recorded macro from Excel to my vb6 code when using my code the converted PDF file the page size is bigger than if I do it manually by using Adobe PDF printer drive and the page size is fine, is there away of changing the code to use the adobe printer driver to save the PDF.

    Regards
    steve

  8. #8
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,503

    Re: Excel to pdf


  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2012
    Location
    Essex
    Posts
    273

    Re: Excel to pdf

    Hi All

    I am trying convert the excel file to PDF via code, but the PDF file is not saving the same has if I choose Adobe PDF in excel if you look at the attached images you see the page size is different and PDF Producer are different, so I want to change the code around so it uses ACTIVEPRINTER="ADOBE PDF".
    Code:
    Public Sub ConvertToPDF2(sInputPath2 As String, sOutputPath2 As String)
          'type to be saved
        Const ExcelFormatPDF As Integer = 17
    
       Dim xl As Excel.Application
       Dim xlSheet As Excel.Worksheet
       Set xl = CreateObject("excel.Application")
       xl.Workbooks.Open (sInputPath2)
       Set xlSheet = xl.Sheets("Spare parts 1")
       xl.Visible = True
      'convert it
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
        Application.PrintCommunication = True
        ActiveSheet.PageSetup.PrintArea = "$A$1:$T$20"
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .LeftHeader = "&20&F"
            .CenterHeader = "&""TKTypeBold,Regular""&20Spare parts list"
            .RightHeader = "&""Busso-Extended,Normal""&36&K00B0F0APTL"
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.7)
            .RightMargin = Application.InchesToPoints(0.7)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperA4
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = False
            .FitToPagesWide = False
            .FitToPagesTall = 1
            .PrintErrors = xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = False
            .EvenPage.LeftHeader.Text = ""
            .EvenPage.CenterHeader.Text = ""
            .EvenPage.RightHeader.Text = ""
            .EvenPage.LeftFooter.Text = ""
            .EvenPage.CenterFooter.Text = ""
            .EvenPage.RightFooter.Text = ""
            .FirstPage.LeftHeader.Text = ""
            .FirstPage.CenterHeader.Text = ""
            .FirstPage.RightHeader.Text = ""
            .FirstPage.LeftFooter.Text = ""
            .FirstPage.CenterFooter.Text = ""
            .FirstPage.RightFooter.Text = ""
        End With
        
        Application.PrintCommunication = True
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    
          xlSheet.ExportAsFixedFormat _
          Type:=xlTypePDF, _
          fileName:=sOutputPath2, _
          Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, _
          IgnorePrintAreas:=False, _
          OpenAfterPublish:=True
    
       ' close and clean
       xl.ActiveWorkbook.Close
       xl.Application.Quit
       Set xlSheet = Nothing
      
    End Sub
    Name:  Correct Using Adobe PDF in Excel.jpg
Views: 29
Size:  16.4 KBName:  Wrong with Code.jpg
Views: 30
Size:  15.4 KB
    Last edited by sbarber007; Nov 19th, 2019 at 03:40 AM.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,636

    Re: Excel to pdf

    i found this

    I use Excel 2010 and had this problem. I fixed it by changing the printer in Excel's dropdown list on the Print section of the File tab. My default printer is a Konica Minolta copy machine. I changed it to Local Printer (which doesn't actually exist) and it worked! I now have the VBA code returning an 8.5x11 PDF! Now I don't have to use our clunky old laptop with an old Office to get the right size.

    Note that picking the Adobe PDF printer did not fix the problem. Just try messing with the different printer choices, or make a fake printer in your computer's Devices and Printers and use that. I hope it works for others with the same issue!
    shareimprove this answer
    answered Apr 16 '13 at 17:27
    Crystal
    6122 bronze badges

    1
    Xerox 7835 does the same thing. HP 5100, Fax, XPS, OneNote printers don't. – Winand Oct 30 '14 at 7:09
    Just in case, all printers can be found in SOFTWARE\Microsoft\Windows NT\CurrentVersion\Devices – Winand Nov 10 '14 at 7:22
    Been beating my head against the wall for three days on this one. Setting the default printer to MS XPS or Fax works for me. I've verified it on 4 machines, 3 OSs (WIn 7Pro 64, Server 2008 sp2, Server 2012), 2 versions of Excel (201/2016) – wayneh May 24 '16 at 20:46
    from https://stackoverflow.com/questions/...t-in-excel-vba
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width