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

[Excel_lent]

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?

 

[John_w]

I then have 100 other sheets in that excel file which have the correct layout and logo

Are these 100 sheets exactly the same, with the same layout and logo?

What file names should be given to the 100 PDFs?

 

[Excel_lent]

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]

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.

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.