Align excel data format to use bulk pdf free software

[bamfan285]

Align excel data format to use bulk pdf free software

I track equipment in our office and who and what cubicle its assigned to in excel I attempting to automate creating a pdf for each person with their equipment.

This information needs to go into a pdf for each person, but a person will have multiple pieces of equipment (A person can have 3 to 8 pieces of equipment).

For the final product, I would have a pdf which list the equipment for that person. and Have a pdf for each person.

Bulk PDf can do the pdf side if I can align the data in the format needed.

Desired format:
Copy from original sheet to a sheet where a persons equipment is on one row.
Alice on row 1
Bob on row 2, etc.

Sample Data:

Equipment Serial Number	Persons Name	Cubicle
RandomSerial1	Alice Andrews	1
RandomSerial2	Alice Andrews	1
RandomSerial3	Bob Bones	2
RandomSerial4	Bob Bones	2
RandomSerial5	Charles Carter	3
RandomSerial6	Charles Carter	3

Desired New Sheet Example:
Row "1" Title row (Denotes Columns Letters):

(A)Equipment Serial Number	(B)Persons Name	(C)Cubicle	(D)Equipment Serial Number	(E)Persons Name	(F)Cubicle
RandomSerial1	Alice Andrews	1	RandomSerial2	Alice Andrews	1
RandomSerial3	Bob Bones	2	RandomSerial4	Bob Bones	2
RandomSerial5	Charles Carter	3	RandomSerial6	Charles Carter	3

Row "2" (will contain all of Alices Equipment):
Row "3" (will contain all of Bobs Equipment):
etc.

Its not important that information repeats in columns in the new sheet as the bulk pdf software can handle this, I just needs a singles person equipment on 1 row, a different persons equipment on row 2, etc.
Copying the row seems easier than extracting the data needed (since the bulk pdf software handles it).

Im attempting to modify this array which put everyone on a new sheets in a similar format (each equipment has its own row, as opposed to each person having a row with all their equipment). I dont know vba well enough to modify this code.
I need to compare the names and if they are the same, copy the row to a sheet such that a user is on a single row, all users on the same sheet.

Bulk PDF creates pdf based on rows, and fills the data based on columns.


Code:

Sub parse_data()
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim icol As Long
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer

    'This macro splits data into multiple worksheets based on the variables on a column found in Excel.
    'An InputBox asks you which columns you'd like to filter by, and it just creates these worksheets.

    Application.ScreenUpdating = False
    vcol = Application.InputBox(prompt:="Which column would you like to filter by?", title:="Filter column", Default:="3", Type:=1)
    Set ws = ActiveSheet
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 To lr
        On Error Resume Next
        If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
            ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
        End If
    Next

    myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
    ws.Columns(icol).Clear

    For i = 2 To UBound(myarr)
        ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
        If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
            Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
        Else
            Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
        End If
        ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
        'Sheets(myarr(i) & "").Columns.AutoFit
    Next

    ws.AutoFilterMode = False
    ws.Activate
    Application.ScreenUpdating = True
End Sub

 

[sijpie]

Here is the code for your first option, all on one sheet, each user its own row.
The username and cubicle appear in the first two columns only

Sub CompileSheetUserPerLine()
    ' Create sheet with one line for each user. Name, Cubicle, list of serial numbers
    
    Dim lRi As Long, lCi As Long, lRo As Long, lCo As Long, UBi As Long, UB1o As Long, UB2o As Long
    Dim vIn As Variant, vOut As Variant
    Dim wsInput As Worksheet, wsOut As Worksheet
    Dim sName As String, sCub As String
    Dim bDone As Boolean
    Dim colNames As Collection
    Dim iCC As Integer, j
    
    'Read the input data into array for fast processing
    Set wsInput = Sheet1    'Sheets("List of all equip")   '<<<<<< Adjust name to the sheet with the table of input data (as per your 'sample data')
    vIn = wsInput.Range("A1").CurrentRegion.Value '<<<<<< Adjust cell to left top corner of the table. This assumes there ar no totally empty rows or columns in yur table
    UBi = UBound(vIn, 1)    'number of rows in input
'' Table layout
'Equipment Serial Number Persons Name    Cubicle
'RandomSerial1          Alice Andrews   1
'RandomSerial2          Alice Andrews   1
'RandomSerial3          Bob Bones       2
'RandomSerial4          Bob Bones       2
'RandomSerial5          Charles Carter  3
    
    'Use a collection to get the unique names
    Set colNames = New Collection
    
    On Error Resume Next    ' adding a dubplicate name to a collection will create an error. We want to ignore this. The name does not get added.
    For lRi = 2 To UBi  'Skip header row
        sName = vIn(lRi, 2)
        colNames.Add sName, sName
    Next lRi
    On Error GoTo 0     'reset to default behaviour
    
    iCC = colNames.Count
    'Now can size the output array, number of rows, assume at least 3 items per person
    ReDim vOut(1 To iCC + 1, 1 To 5)
    
    UB1o = UBound(vOut, 1) 'number of rows
    UB2o = UBound(vOut, 2) 'number of columns
    
    'Add the header row
    vOut(1, 1) = "Name": vOut(1, 2) = "Cubicle"
    
    'Now go through the input table for each name in the collection and add the equipment serial numbers
    For j = 1 To iCC
        sName = colNames(j)
        lCo = 3
        vOut(j + 1, 1) = sName
        For lRi = 2 To UBi
            If vIn(lRi, 2) Like sName Then  'found line with same name
                'add cubicle nr to row
                vOut(j + 1, 2) = vIn(lRi, 3)
                If lCo > UB2o Then  'need to increase the output array number of columns
                    ReDim Preserve vOut(1 To UB1o, 1 To UB2o + 1)   'the preserve word keeps the current contents of the array in place
                    UB2o = UBound(vOut, 2)
                End If
                'add equipnr to vout
                vOut(j + 1, lCo) = vIn(lRi, 1)
                'increase lco for next column in output array
                lCo = lCo + 1
            End If
        Next lRi
    Next j
    
    'COmplete the header row
    j = 0
    For lCo = 3 To UB2o
        j = j + 1
        vOut(1, lCo) = "Equip Ser. Nr " & j
    Next lCo
    
    'Now output the output array to new sheet
    Set wsOut = Sheets.Add(after:=Sheets(Sheets.Count))
    wsOut.Name = "EqList_" & Replace(Format(Now(), "yy-mm-dd_hh:mm"), ":", "-")
    wsOut.Range("A1").Resize(UB1o, UB2o).Value = vOut
End Sub

 

[sijpie]

Do you need code for separate sheets, or will this do?

 

[bamfan285]

Sorry for the delay.

This sort of worked.
This didnt work correctly on my data, probably because Im using Columns A through I.
(On my data) That created a Name and Cubicle column which populated, but all Equip Ser. Nr columns are blank.

If I rearrange my data to only use the sample columns from the sample data is works fine. But Im not able to use that output with the pdf software.

I do like that it created it on a new sheet and left the master example data sheet intact.
Im using Columns A through I
The pdf software expects the data to duplicate/repeat, copying the whole row it would accomplish this and seems easier than "extracting the data".

Can you make this copy the entire row?

I attached some screen shots so I hope they help.

Thanks for what you have done.

 

[sijpie]

Is your 2nd illustration the output of BulkPDF?

I am wondering why you need to process the data through BulkPDF.

I can also arrange the output spreadsheet(s) in any way you like, and then print them to pdf. All in one macro. That is not difficult.

If you can post an example of the required output or confirm it is the second image, but ideally with some data in it, then I can create the macro.

I take it you want a separate output pdf for each user.

At a later stage I can add additional text, headers and footers to the pdf output. Even company logos, etc.

 

[bamfan285]

Is your 2nd illustration the output of BulkPDF?

Ive attached an example of the end product. and some Real data.

I was using bulkpdf because I could only find a Send Keys method to populate fields in the pdf with data. This didnt work and just froze the machine.

I used bulkpdf because it seemed to more easily map, populate, and create the pdf. BulkPDF actually mostly worked if I manipulated the data to match the expected format - all information in one row of data for each pdf. But I have hundreads of users, so tedious to do manually, copy and pasting the multiple rows of one user to one row only.

Ive attempted several ways to do this (like opening a pdf, writing data, saving pdf - like what you reference, but I couldnt get it work using send keys, and couldnt find other methods that I understood.

But the latest plan was to use bulkpdf as it worked if I could manipulate data.
It seemed copy/pasting then using bulkpdf would be easier than input/output to pdfs. In other words, I hoped to write vba to copy paste, because handling pdfs was out of my reach with vba. Now I know I cant even copy and paste!

The plan was to take my data, run this code to get the bulkpdf expected format of one row per user/pdf, manually populate other needed fields like the top portion of the pdf, Organization, Equipment account, Equipment custodian, username, including the MFG in the bottom portion, etc. Since all this data is the same (All in same Organization or Equipment Account) simply adding a few columns and copy/pasting was a easy fill the gaps from what I use daily (screenshot - real data), vs whats needed for the pdf which has some extra info, MFG, Org, Account, etc.

I attempted to upload the actual pdf, but that doesnt appear to be allowed.

 

[bamfan285]

Is your 2nd illustration the output of BulkPDF?

It is the raw/blank pdf that I feed into bulkpdf; the pdf I would otherwise fill out manually.
The 2nd illustration in that post is the columns in the pdf Im most interested in populating. I have uploaded the whole pdf picture which should give you a better idea of what Im trying to do.
The raw/blank pdf and the spreadsheet feed into Bulkpdf, and bulkpdf would output a pdf similar to the one I attached with all the data in it.

 

[bamfan285]

I dont have to use bulkpdf, its just one of many ways Ive tried to solve the problem. And seemed the easiest for me.

 

[sijpie]

Ok, thanks for the info. I will have a decent look at it later and see what I can do

 

[sijpie]

a few questions:
In the form there are a few items added that do not appear in the input list. A number of them have to do with the 'equipment custodian'.

1. Is that the person who has compiled the data list? ie, is the custodian the same for all people on this list?
2. Is the organisation the same for all people on the list?
3. What about the issue date? Can I ask, in the macro, to provide a date and apply that to all equipment?

I can get the information either by asking the user running the macro, or maybe it is better to do ith through an extra table.

Let me know what you think