none
VBA code RRS feed

  • Question

  • Hi 

    I want to select specific column from excel sheet(say A1 and A3) and print the specific columns to PDF using Macros

    Thursday, February 8, 2018 4:41 AM

Answers

  • Hi Chetna virmani,

    You had mentioned that," from this sheet, I need 2 pdfs: Header should be same in both pdfs, (cell-A1,A2,A3,B1,B2,B3). First pdf should have data from all rows(starting from 6th) of column A, B,G. Second pdf  hould

    have data from all rows(starting from 6th) of column A, B,H. Columns can be more so please use the logic you use in your code to enter the column name"

    From you above description, I understand that now you just need to generate 2 PDF file and these 2 PDF files will contain data from all the rows in that sheet.

    So let me know, If i misunderstand anything in your above description. I will try to correct the code.

    For this requirement, As data are in huge amount, I will try to copy the desire data from sheet 1 to sheet2 and will export the sheet2 and after that clear the data in sheet 2. So that sheet will be ready for next time for code execution.

    Code:

    Sub demo2()
    
     Dim col, filename As Variant
     Dim lastrow As Long
    Dim sht, sht2 As Worksheet
    Set sht = Sheets(1)
    Set sht2 = Sheets(2)
    col = InputBox("Give me some input")
    lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    
     Worksheets("Sheet1").Range("A1:B3").Copy
     Worksheets("Sheet2").Range("A1:B3").PasteSpecial Paste:=xlPasteValues
     
    Sheets("Sheet1").Range("A5:B" & lastrow).Copy Destination:=Sheets("Sheet2").Range("A5")
    Sheets("Sheet1").Range(col & "5:" & col & lastrow).Copy Destination:=Sheets("Sheet2").Range("C5")
    filename = InputBox("Enter File Name")
    sht2.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
                "C:\Users\v-padee\Desktop\pdf data\" & filename, Quality:=xlQualityStandard, _
       IncludeDocProperties:=False, IgnorePrintAreas:=False, _
       From:=1, To:=1, OpenAfterPublish:=False
    Sheets(2).UsedRange.ClearContents
    End Sub
    

    My sheet 1 looks like below.

    When you run code, It will ask for Column Character as below.

    Then It will ask for the file name for PDF file. You can enter just a desire file name like below. Path is already stored in the code.

    Output in folder:

    Content of the PDF file looks like below.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 14, 2018 1:14 AM
    Moderator

All replies

  • Hi,

    I made a sample. 
    When you click [Print PDF] button after selecting specific column, a PDF file is written:
       

    [code]
    ' --- [Print PDF] button ---
    Private Sub btn_PrintPDF_Click()
        If IsEmpty(Selection) Then
            MsgBox "nothing is selected !!"
            Exit Sub
        End If
        ' --- PageSetup
        ActiveSheet.PageSetup.PrintArea _
            = Range(Cells(Selection(1).Row, Selection(1).Column), _
                    Cells(Selection(Selection.Count).Row, Cells(Selection.Count).Column)).Address
        ' --- PDF file name (with full path)
        Dim fileName As String
        fileName = ThisWorkbook.Path & "\test.pdf"
        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            fileName:=fileName
        ' ---
        MsgBox fileName & Chr(13) & "has been made."
    End Sub
    Please try.

    Regards,

    Ashidacchi

    Thursday, February 8, 2018 7:54 AM
  • Hi Ashidacchi

    Thanks for your reply

    But this code is giving compile errors.

    Also I want to enhance my requirement

    I need a macro which will select 2 cells(A2 and D2) and print it, then it will select A3 and D3 and create another pdf, then A4 and D4, till all record are taken into account, and every pdf made should be different.

    Thursday, February 8, 2018 2:50 PM
  • Hi Chenta virmani,

    I can execute my code successfully.
       

    Please make clear:
    (1) which error do you see? syntax error or runtime error
    (2) where (on which line) do you see the error?

    # If a created PDF file is opened, runtime error may occur. Close the PDF file.
       My code is only for basic, does not include more realistic code. If you want more, please specify it. 

    Regards,

    Ashidacchi

    Thursday, February 8, 2018 11:11 PM
  • Hi Chetna virmani,

    You had mentioned that,"I need a macro which will select 2 cells(A2 and D2) and print it, then it will select A3 and D3 and create another pdf, then A4 and D4, till all record are taken into account, and every pdf made should be different."

    Try to refer example below.

    Data in my sheet stored as below.

    Code:

    Sub demo()
    Dim i, LastRow As Long
    Dim sht As Worksheet
    Dim printRange As Range
    
    Set sht = Sheets(1)
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    For i = 2 To LastRow
    Set printRange = sht.Range("E1")
       
        printRange.Value = "Value 1 = " & sht.Range("A" & i).Value & " Value 2 = " & sht.Range("D" & i).Value
        'Debug.Print printRange.Value
        sht.Range(printRange.Address).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\Users\v-padee\Desktop\pdf data\" & i, Quality:=xlQualityStandard, _
       IncludeDocProperties:=False, IgnorePrintAreas:=False, _
       From:=1, To:=1, OpenAfterPublish:=False
    
    
    Next i
    sht.Range("E1").Clear
    End Sub

    Output:

    Note that Cell E1 I used to merge the value of 2 cells. So you can replace with any empty cell in your sheet. In my sheet E1 is empty so i use it.

    You can try to make a test with it and if you have any further question then let us know about that.

    We will try to provide you further suggestions to solve it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 9, 2018 4:19 AM
    Moderator
  • Hi Deepak

    Thanks for your reply

    but I am getting run time error every time, Document not saved.

    Also there is a additional requirement , I want all the names pdf name specific from each column,

    example:first pdf should be named with value in column A1, second should have name as of column A2, like that

    Thanks

    Friday, February 9, 2018 3:31 PM
  • Hi,

    (1) I'm afraid you need to provide error message even if it is Syntax error or Runtime error and where it occurs.

    (2) Your additional requirement wold be easy to resolve. I'm wondering if you think about/examine provided code.  "FileName:=" means file name for output. So, you can write file name (e.g. Range("A1").value) after "FileName:=". 

    Regards,

    Ashidacchi

    Sunday, February 11, 2018 4:44 AM
  • Hi Deepak/Ashidacchi

    Thanks for the code, it is working.

    I have one more requirement.

    In an excel data is there in columns A,B,C,D,F,G,H,I,J,K

    Data from Column A,B and G(All rows)-different PDF

    Data from A,B and H(All rows)-different pdf

    Data from A,B and I(all rows)different pdf

    A,B and J(all rows)-different pdf

    A,B and K(all rows)-different pdf

    The names should be taken from respective columns-G,H,I,J,K

    Please use sheet 2 of same excel for any data merger

    Sunday, February 11, 2018 3:21 PM
  • Hi,

    I'm afraid you don't think about the meaning of code and that you only do Copy&Paste provided code.

    If you try to understand code provided, you will get answer by yourself.

    Regards,

    Ashidacchi

    Monday, February 12, 2018 1:25 AM
  • Hi Chetna virmani,

    You had mentioned the new requirement.

    Now you want to create PDF files including G,H,I,J,K columns in it.

    Want to give file name from G,H,I,J,K columns according to the column on which code is executing currently.

    And use sheet2 for merging the data.

    Below is the modified example according to new requirement.

    Sub demo()
    Dim i, LastRow As Long
    Dim sht As Worksheet
    Dim printRange As Range
    Dim col As Variant
    Set sht = Sheets(1)
    col = InputBox("Give me some input")
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    For i = 2 To LastRow
    Set printRange = Sheets(2).Range("A1")
       
        printRange.Value = "Value 1 = " & sht.Range("A" & i).Value & " Value 2 = " & sht.Range("D" & i).Value & " Value 3 = " & sht.Range(col & i).Value
        Debug.Print printRange.Value
        sht.Range(printRange.Address).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\Users\v-padee\Desktop\pdf data\" & sht.Range(col & i).Value, Quality:=xlQualityStandard, _
       IncludeDocProperties:=False, IgnorePrintAreas:=False, _
       From:=1, To:=1, OpenAfterPublish:=False
    
    
    Next i
    Sheets(2).Range("A1").Clear
    End Sub
    

    When you run the code it will prompt for input. You need to insert the column on which you want to run this code. like below.

    Then you can again run the code, Input new column and run the code for other columns.

    If you don't want to give any input and want to run code on each G,H,I,J,K column then you can store column character in an array and loop through it.

    As per your requirement, now I am merging the text on sheet 2. I suggest you to increase the width of column A in sheet2 for properly displaying the value in PDF File.

    Further, I want to suggest you to refer Object model (Excel VBA reference) for better understanding of above code and for future modification in the code.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 12, 2018 1:43 AM
    Moderator
  • Hi Chetna virmani,

    Is your issue solved?

    I find that you did not follow up this thread after my last post.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 13, 2018 9:18 AM
    Moderator
  • Hi Deepak

    I am following this post.

    I have tried your code, but it is generating blank PDFs.

    I am working on the code and will let you know once it will give desired results.

    Thanks

    Tuesday, February 13, 2018 2:23 PM
  • Hi Ashidacchi

    I am not doing only copy and paste, since I am new to VBA so I have asked for help.

    I am trying to understand it but it will take some time to write a code by myself.

    Thanks for your time

    Tuesday, February 13, 2018 2:24 PM
  • Hi Deepak

    I am using this sheet, I tried the code but its not giving me the desired result.

    from this sheet, I need 2 pdfs:

    Header should be same in both pdfs, (cell-A1,A2,A3,B1,B2,B3)

    First pdf should have data from all rows(starting from 6th) of column A, B,G

    Second pdf  hould have data from all rows(starting from 6th) of column A, B,H

    Columns can be more so please use the logic you use in your code to enter the column name

    


    Tuesday, February 13, 2018 6:16 PM
  • Hi Chetna virmani,

    You had mentioned that," from this sheet, I need 2 pdfs: Header should be same in both pdfs, (cell-A1,A2,A3,B1,B2,B3). First pdf should have data from all rows(starting from 6th) of column A, B,G. Second pdf  hould

    have data from all rows(starting from 6th) of column A, B,H. Columns can be more so please use the logic you use in your code to enter the column name"

    From you above description, I understand that now you just need to generate 2 PDF file and these 2 PDF files will contain data from all the rows in that sheet.

    So let me know, If i misunderstand anything in your above description. I will try to correct the code.

    For this requirement, As data are in huge amount, I will try to copy the desire data from sheet 1 to sheet2 and will export the sheet2 and after that clear the data in sheet 2. So that sheet will be ready for next time for code execution.

    Code:

    Sub demo2()
    
     Dim col, filename As Variant
     Dim lastrow As Long
    Dim sht, sht2 As Worksheet
    Set sht = Sheets(1)
    Set sht2 = Sheets(2)
    col = InputBox("Give me some input")
    lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    
     Worksheets("Sheet1").Range("A1:B3").Copy
     Worksheets("Sheet2").Range("A1:B3").PasteSpecial Paste:=xlPasteValues
     
    Sheets("Sheet1").Range("A5:B" & lastrow).Copy Destination:=Sheets("Sheet2").Range("A5")
    Sheets("Sheet1").Range(col & "5:" & col & lastrow).Copy Destination:=Sheets("Sheet2").Range("C5")
    filename = InputBox("Enter File Name")
    sht2.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
                "C:\Users\v-padee\Desktop\pdf data\" & filename, Quality:=xlQualityStandard, _
       IncludeDocProperties:=False, IgnorePrintAreas:=False, _
       From:=1, To:=1, OpenAfterPublish:=False
    Sheets(2).UsedRange.ClearContents
    End Sub
    

    My sheet 1 looks like below.

    When you run code, It will ask for Column Character as below.

    Then It will ask for the file name for PDF file. You can enter just a desire file name like below. Path is already stored in the code.

    Output in folder:

    Content of the PDF file looks like below.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 14, 2018 1:14 AM
    Moderator
  • Hi Deepak

    It is working 

    Thanks a lot

    Wednesday, February 14, 2018 8:14 AM
  • Hi Deepak

    can you please help for looping code.

    which means there will be no need to input the column name, the code will automatically create different pdfs till last column

    please its urgent

    Monday, February 19, 2018 6:42 AM
  • Hi Chetna virmani,

    You had asked,"which means there will be no need to input the column name, the code will automatically create different pdfs till last column"

    If you see my code , I take the input from user and use it in a code.

    Further, You can try to store column character in an array and loop through array to get column character which you can use it in your code.

    Example:

    Sub demo()
        'Declaration
        Dim array_example(10)
        
        'Storing values in the array
        array_example(0) = "A"
        array_example(1) = "B"
        array_example(2) = "C"
        array_example(3) = "D"
        array_example(4) = "E"
        array_example(5) = "F"
       
        For i = 0 To 5
            Debug.Print array_example(i)
        Next
    End Sub
    

    Output:

    You just need to add this code in my previous code to loop through columns without taking input from user.

    Let us know, If you have any trouble to implement this code.

    We will try to provide you suggestions to solve it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 5, 2018 8:35 AM
    Moderator