Excel VBA case Generate a delivery note based on an outbound order The same address is merged

This article was first published on October 10, 2023 in my eponymous ***Excel Live Learning and Practical Use (VBA Programming Practice), more articles and cases, please search and pay attention!

Worksheet copying.

Rows are inserted dynamically into the worksheet.

Merge cells in a worksheet.

Hello everyone, I am making tea in cold water, and in the past two days, I saw a help post on a forum, asking for a delivery note to be generated according to the template according to the outbound schedule, and the same address is combined into one delivery note.

Regarding the case of generating a delivery note, we have shared a [Generate delivery notes in batches and generate customer delivery notes according to the order summary table], but today this one is slightly different, let's take a look at it together:

This is the outbound schedule, which is a separate file:

This is the delivery note template to be generated, which is also a separate file:

There are a lot of lines in the template he gave, and I deleted some of them, leaving only one line, as a template, and the format is fixed. When there is more than one row of data, we insert rows as needed to avoid blank rows.

His requirements are: according to the address in the outbound schedule, according to the information of the delivery note, the outbound data is summarized, and then named after the outbound order number, and multiple outbound order numbers are connected with -.

It took a little time, and it was finally completed, so I share it with everyone:

The basic idea and implementation process.

1. Set up a user form userform1, which is used to select the outbound schedule, select the worksheet, and select the generated delivery note storage folder.

2. Put the command button of the startup form into the menu bar "Add-ins", the main reason is that if you add a command button to the template table, then you need to delete it when copying the worksheet, and if you add a worksheet and place the command button, it is a bit loose. So, this time we'll try another way.

3. We click on the "Add-ons" tab, click on "Delivery Note", click "Generate", and start the user form userform1.

4. After userform1 starts, we select the file, open the outbound detail file, and select the correct worksheet. In order not to affect the subsequent operation, we hide the open worksheet.

set wb = workbooks.open(filepath)wb.windows(1).visible = false

1) Read the data into the array arr.

2) Loop array arr, with address as key, array arrtem as itme added to the dictionary dic. ARRTEM stores detailed data.

3) Loop the key of the dictionary, take out the item to arrtem, and connect the fourth column of the "outbound order number" with "-" as the save file name.

4) Copy the "Delivery Note" template table to the new workbook, here we directly copy the worksheet and save the file.

ws.copyset wb = activeworkbookwb.s**eas s**efolder & "\" & filename

6) Set the summary formula for the total row.

.cells(5 + u + 1, 3).formula = "=sum(" & rng.address & ")"

for i = 5 to 5 + u if .cells(i, 4) 6. Worksheet sorting: In the above-mentioned cell merge centering operation, we need to arrange the same outbound order number consecutively, otherwise the result of merging and centering may not meet our expectations (although it will not be wrong), so before extracting the data, we sort the outbound schedule according to the outbound order number in ascending order. We need to perform a sorting operation in two places (one is that when we select a file, the first table is automatically selected, and the other is the change event of cmbsheets, which selects the worksheet), so we do a separate sorting process for multiple calls:

private sub sortsheet(ws as worksheet) with ws.sort .sortfields.clear .sortfields.add key:=ws.cells(1, 9), sorton:=xlsortonvalues, order:=xlascending, _dataoption:=xlsortnormal .setrange ws.usedrange .header = xlyes .matchcase = false .orientation = xltoptobottom .sortmethod = xlpinyin .apply end withend sub

7. Data verification: when we save the file, it is according to the outbound order number as the file name, and the standard of data collection is based on the address, is it possible to have different addresses for the same outbound list?Then there is a possibility that a file will be overwritten, resulting in missing data. Therefore, before generating the delivery note, a data check is done and if there is more than one address for the same outbound order, the procedure will be withdrawn.

Although such a situation is unlikely to happen, from the perspective of writing **, it has to be considered. It may seem like a bit of a flurry, but it should help us expand our thinking and make us think more holistically. Of course, this will definitely increase the workload.

Actually, the reason for this problem is that he requires the file to be saved in a way that is not 100% sure that it is the unique file name.

To solve this problem, we can use the DIC's key, which is the "address", as the file name, or add a random number that cannot be repeated after the file name, such as the current time, etc. However, then again, using an address as a file name may contain characters that do not meet the file name requirements, which is another troublesome thing.

8. When I was about to send documents to the forum, I found that the landlord's needs had changed again, and he wanted to put the "address" under the "total". Forget it, don't send it. Don't change it, that's all.

Finish the battle!For full **, see second tweet.

~end~~~

If you like it, like it, click on it, leave a comment, and share it!Thanks for the support!