VBA code to determine InDesign Layout

[Sammi8796]

Apparently this VBA code that I'm trying to create is too difficult for AI, so I wanted to throw this out here to you guys because you've helped me tremendously over the years. Here we go, I will attempt to explain what I'm trying to do. Please bare with me:
We are a small print shop that is trying to layout files for our digital printer so the process is quicker. These printed files, (after they are printed onto a 12x18 sheet) will then go through a series of two other steps. After they are printed, it will go through a heater, page by page. Then lastly, it will be sent through a cutting machine, page by page that will cut it down into individual pieces. I need this VBA code to put the files in alphabetical order so I can use it for the first step, which is the InDesign layout. The layout needs to be in such a way that the last step keeps the alphabetical order with "A" on top and "Z" on the bottom.

The Process:
We will use InDesign data merge to pull in the files to make the layout, which will then be exported and used to print the files digitally on our digital printer. The InDesign data merge uses a csv file that will have the names of all the files I need to layout, in a single column list. InDesign will take this list and lay it out from left to right. The VBA code will be used in another workbook that I have that contains lists of items that customers choose from. I want the VBA code to look at a "RawData" tab that lists the quantities next to the description and file names. Headers are starting on row 2, raw data underneath in columns A:D. The VBA code will create the layout and create an "output" tab, (keeping in mind that each item could have more than 1 quantity, so it will need to take that into account and list it as many times as the quantity states. Also keeping in mind how the final result should be, which is alphabetical order with A on top and Z on the bottom. ) I will then take the output of the VBA code and paste it into a csv that InDesign will data merge and create the layout.

InDesign auto layout (which is what I need the vba code to create in one list) will take the list of files and lay them out on a page in two columns with 6 rows equaling 12 on a page. It will lay them out starting from left to right and will then begin the next row directly underneath it, left to right. This will make 12 total on the page. The number of files will vary, it can be different all the time. So the vba code will need to take the total number of files and divide it by 12 to get the total pages before it can lay it out in alphabetical order.

The finished product at the digital printer will come out with page 1 on top, which will have "A" then moving through the alphabet per page with last page having "Z". Here is a visual layout of the pages and how the alphabetical order should look. Also on the left is the VBA output order to achieve this.

From the digital printer it get’s taken over to a clear coating machine where they will feed each sheet, one at a time into the machine and it will come out with the last page on top, with “Z” on top and “A” on the bottom.

After that clear coat step, it will be fed into a Duplo machine where it will be cut into individual pieces. It will be fed starting with the last page with “Z” on top and end up with the last page with “A” on top. It cuts them into two columns and drops them into the delivery that way. We need to be able to then physically stack the left column on top of the right column putting “A” on top and “Z” on the bottom. Here is a visual:

The amount of files will vary every time, but the layout method needs to remain the same to get the desired result from the last step which is the Duplo cutting machine. So you will first need to take the amount of files then divide them by 12 so you can get the amount of pages. Then lay them out in two columns, starting from the left. Remember that the order is important in order to get them in the correct alphabetical order at the end. So even though InDesign is laying them out starting in position 1 top left corner and moving over to the right and then starting a new row underneath, the file names need to be laid out starting in bottom left position of page and going up then onto the next page and going up, then reaching the end of the pages and then starting in the bottom right and going up and so on till all pages are filled.

I know that was long and confusing but I hope someone was able to follow along because I've been working on this for a couple weeks now. The VBA that AI keeps coming up with does not put it in the order I need for the end result after it's cut. Please let me know if there is anymore information I can provide.

 

[Sammi8796]

I know you guys can solve this. I also know its long, but if someone can help me out, you'd be my hero.

 

[dreid1011]

I think you've provided so much more information about your process beyond what you really need the VBA for that I am not sure I understood it all the way. So let's see if I got it.

The VBA should be generating a single column list from customer selections and sorting it alphabetically and also accounting for the qty of each item selected. Then InDesign takes this list and formats it in 6 rows by 2 columns per page. Why do we need to know how many total pages there are before the list can be sorted?

 

[Sammi8796]

I think you've provided so much more information about your process beyond what you really need the VBA for that I am not sure I understood it all the way. So let's see if I got it.

The VBA should be generating a single column list from customer selections and sorting it alphabetically and also accounting for the qty of each item selected. Then InDesign takes this list and formats it in 6 rows by 2 columns per page. Why do we need to know how many total pages there are before the list can be sorted?

The merge in InDesign places the files from left to right on the master page, 2 columns with 6 rows. Since the order in which they need to be placed needs to accommodate the end result order they should be in, after they've gone through the Duplo cutter, its not just a simple alphabetical order layout. To achieve the alphabetical order after they are cut, the files need to be laid out on the master InDesign, starting with "A", from the bottom left page and continue up. Depending on the amount of files, will determine the amount of pages (total files /12=pages) because then the files will continue being laid out in alphabetical order from the bottom right column and continue up until "Z" is on top and in the last position on the page in the right column. So the screenshot I attached should be the order that the VBA puts them in. All this in order to achieve the cut files in two stacks, which we will then place left column stack on top of the right column stack, to get in alphabetical order with "A" on top and "Z" on the bottom. This visual is the 2nd screenshot. I know this is really confusing and I appreciate you taking a crack at it. THANK YOU.

 

[Akuini]

@Sammi8796
I don't quite understand what you're trying to do, but could you post your sample data as table? preferably using XL2BB, so we can easily copy-paste it for testing?

 

[Sammi8796]

@Sammi8796
I don't quite understand what you're trying to do, but could you post your sample data as table? preferably using XL2BB, so we can easily copy-paste it for testing?

I am having a really difficult time trying to get the XL2BB add in to work. I will try to explain without it for now. The end result of what we are trying to accomplish is a bunch of printed files in alphabetical order so that there is no need for manual intervention of collating to achieve the alphabetical order. Files are pulled into InDesign with merge function and this layout is exported to a digital printer where they are printed in the order of that layout (2 columns, 6 rows on each page for a total of 12 on each page). The pages are then fed through another machine that clear coats it, which then reverses the page order once they come out of there. So now, last page is on top, where it is fed into a cutting machine, starting with last page on top, and is cut down into individual pieces into two columns. Once we stack the left column on top of the right, they should all be in alphabetical order with "A" on top and "Z" on the bottom (illustration of this is above in the "EndResult-Duplo Cutter.jpg" attachment). I need the InDesign merge function to layout the files in the order needed to achieve that final result. I have attached screenshots of each stage (with written explanations on them) in the hope it will give you an understanding of what I'm trying to achieve. This, coupled with my original descriptions should give you the information you need. I will give you the VBA code that AI has created for me, but this is wrong. It doesn't give me the result I'm expecting:

Sub CreateCorrectInDesignLayout()
Dim wsRaw As Worksheet
Dim wsOutput As Worksheet
Dim fileList As Collection
Dim totalFiles As Integer
Dim totalPages As Integer
Dim rowIndex As Long
Dim i As Long, j As Long
Dim pageIndex As Integer
Dim outputIndex As Long
Dim tempArray() As String
Dim leftColumn() As String
Dim rightColumn() As String
Dim finalArray() As String
Dim item As Variant

Set wsRaw = ThisWorkbook.Sheets("RawData")
Set wsOutput = ThisWorkbook.Sheets.Add(After:=wsRaw)
wsOutput.Name = "Output"

Set fileList = New Collection

' Get file names based on quantity
rowIndex = 3 ' Starting from row 3, assuming headers are in row 2
Do While wsRaw.Cells(rowIndex, 1).Value <> ""
For i = 1 To wsRaw.Cells(rowIndex, 1).Value
fileList.Add wsRaw.Cells(rowIndex, 2).Value & "|" & wsRaw.Cells(rowIndex, 4).Value
Next i
rowIndex = rowIndex + 1
Loop

' Sort fileList alphabetically
ReDim tempArray(fileList.Count - 1)
For i = 1 To fileList.Count
tempArray(i - 1) = fileList(i)
Next i
Call QuickSort(tempArray, LBound(tempArray), UBound(tempArray))

' Calculate total pages needed
totalFiles = UBound(tempArray) + 1
totalPages = Application.WorksheetFunction.Ceiling(totalFiles / 12, 1)

' Divide files into left and right columns
ReDim leftColumn(totalPages * 6 - 1)
ReDim rightColumn(totalPages * 6 - 1)

For pageIndex = 0 To totalPages - 1
For i = 0 To 5
If pageIndex * 12 + i < totalFiles Then
leftColumn(pageIndex * 6 + i) = tempArray(pageIndex * 12 + i)
End If
If pageIndex * 12 + 6 + i < totalFiles Then
rightColumn(pageIndex * 6 + i) = tempArray(pageIndex * 12 + 6 + i)
End If
Next i
Next pageIndex

' Combine left and right columns into final array
ReDim finalArray(totalFiles - 1)
outputIndex = 0
For i = UBound(leftColumn) To LBound(leftColumn) Step -1
If leftColumn(i) <> "" Then
finalArray(outputIndex) = leftColumn(i)
outputIndex = outputIndex + 1
End If
Next i
For i = UBound(rightColumn) To LBound(rightColumn) Step -1
If rightColumn(i) <> "" Then
finalArray(outputIndex) = rightColumn(i)
outputIndex = outputIndex + 1
End If
Next i

' Output the final ordered list
For i = 0 To UBound(finalArray)
wsOutput.Cells(i + 1, 1).Value = finalArray(i)
Next i

End Sub

Sub QuickSort(arr() As String, ByVal left As Long, ByVal right As Long)
Dim i As Long, j As Long
Dim pivot As String
Dim temp As String

i = left
j = right
pivot = arr((left + right) \ 2)

Do While i <= j
Do While arr(i) < pivot
i = i + 1
Loop
Do While arr(j) > pivot
j = j - 1
Loop
If i <= j Then
temp = arr(i)
arr(i) = arr(j)
arr(j) = temp
i = i + 1
j = j - 1
End If
Loop

If left < j Then QuickSort arr, left, j
If i < right Then QuickSort arr, i, right
End Sub

The images in the pictures I've attached aren't in the correct order or are just stock photos to give you an idea of how it should work. They aren't necessarily in the order I'm trying to achieve. However, the screenshots i've included in my original post are examples of the order in which it needs to be to achieve the desired results. Please let me know if I can clarify anything because I know how confusing this is. Its driving me absolutely mad so I can imagine how confusing it is for others. Thank you in advance.

 

[Akuini]

I am having a really difficult time trying to get the XL2BB add in to work.

As an alternative to providing your sample data, you could upload a sample workbook to a file-sharing site like Dropbox or Google Drive and share the link here. Also, ensure that the link is accessible to anyone. If there is sensitive data, please replace it with representative dummy data. And please also provide the expected results.