Very slow excel file. Is there an alternative method? Converting CSV to 100 PDF files

Excel_lent

New Member
Joined
Feb 25, 2015
Messages
3
I have a CSV file sent to myself weekly which has 100 rows in it.

I need to convert each row into a single PDF which is formatted with company logo and looks pretty.

So I have set up a template excel file with a sheet called 'rawdata' where I paste the CSV into. (Roughly 50columns, 100 rows)

I then have 100 other sheets in that excel file which have the correct layout and logo, and are formatted to print in a4. Each one of these sheets pulls in data from every column of a single line in the rawdata sheet using simple equals formulas.

As you can imagine the file is huge and takes about 10mins to open and then about 15mins to save as a PDF. Not to mention the amount of times the file just crashes altogether.

Is there a quicker/better way to do this please?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Excel_lent

New Member
Joined
Feb 25, 2015
Messages
3
It should be 1 single PDF with 100 sheets.

But I guess if you have a solution that makes 100 pdfs then I could just merge them after. That's pretty quick to do in Adobe.

The 100 sheets have exactly the same layout and logo.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,002
I thought you wanted 100 PDFs, so see if this macro is any use. If you have Acrobat Pro the 100 PDFs can be merged programmatically into a single PDF with VBA and the Acrobat library.

The macro expects the sheet with the required layout and logo to be named "PDF". It imports each row of the csv file into the "PDF" sheet starting at A10 and saves the "PDF" sheet as a PDF file in the same folder as the macro workbook, with file names "001.pdf", "002.pdf", etc. I suggest you test it with a csv file containing 10 rows.
VBA Code:
Public Sub Create_PDF_Files()

    Dim csvFile As String
    Dim csvLine As String, csv As Variant
    Dim fileNum As Integer
    Dim PDFfile As String, n As Long
    
    csvFile = "C:\path\to\csv file.csv"   'CHANGE THIS
    
    n = 0
    fileNum = FreeFile
    Open csvFile For Input As #fileNum
    Do Until EOF(fileNum)
        Line Input #1, csvLine
        csv = Split(csvLine, ",")
        n = n + 1
        PDFfile = ThisWorkbook.Path & "\" & Format(n, "000") & ".pdf"
        With Worksheets("PDF")  
            With .Range("A10").Resize(1, UBound(csv) + 1)
                .ClearContents
                .Value = csv
            End With
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Loop
    Close #fileNum

End Sub
The macro could be modified to create a single PDF with 100 pages by copying and pasting each updated "PDF" sheet to another output sheet with page breaks added and then that output sheet is saved as a PDF.
 

Forum statistics

Threads
1,141,403
Messages
5,706,257
Members
421,435
Latest member
Yoga15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top