Load data from a system to many Exel workbook

[BittenApple]

Hello team,
I have to download 100 reports (in Excel workbook) from one of our systems in our company. The format of the excel workbook and its data format is attached to this post.

-The column that has items are merged (I have to unmerge that column to be able to pull the data from that column and insert it in a different sheet in same workbook.

-Columns Available and Sold have hyperlinks, I have to remove hyperlinks.

Last month, I inserted the worksheet from the report from the company system into a new workbook for each customer and pulled the data into a table with functions and finally I inserted a chart on the table.

What is the best approach for these tasks that can be taken care of so quick and so fast.

Each customer has different number of rows for items. some have 7 rows and some have 5 rows and some have 10 rows.

and there a total line at the end for each table.

Regards,
BittenApple

[Rudi]

Hi,

1. Are the 100 reports you download separate workbooks or are they 100 different sheets in the same workbook?
2. Is there a reason that you have to insert the ITEMS data into a new sheet after you unmerged it? Can it not be unmerge in its original place?
3. Do the sheets that have been fixed (or processed) by the macro need to go into separate workbooks (if they were not in workbooks already)?
4. What do you mean by "pulled data into a table"? Must the data be converted into an Excel Table Object (Insert > Table)?
5. What chart do you create from the data, and must it include the Sold Rate % column?

[BittenApple]

Hello Rudi,

Please see my responses below:

1.Are the 100 reports you download separate workbooks or are they 100 different sheets in the same workbook?
I run the report for each customer, then on each run, a new workbook opens, having said that I have many, many workbooks.
I have one workbook for each run.

2.Is there a reason that you have to insert the ITEMS data into a new sheet after you unmerged it? Can it not be unmerge in its original place?
Reason for inserting the data into a new sheet is to show the data to client in a clean format and be able to insert a chart on that data. I unmerge it in its original place, if I didn't explain properly, my apology for it.

3.Do the sheets that have been fixed (or processed) by the macro need to go into separate workbooks (if they were not in workbooks already)?
Yes, I copy the sheets from the workbook from the run into a new workbook. If I want to keep in its original workbook, the work will be a way more. After inserting the sheet from the report into a template that I have built, I run macros, unmerge the cells, with functions: 1-I get the data into a new sheet again to show data clean, 2-then I insert a chart on the data.
1 and 2 are in one sheet.

4.What do you mean by "pulled data into a table"? Must the data be converted into an Excel Table Object (Insert > Table)?
I mean when I copy the sheet from the report which is in an Excel workbook into a new workbook, with functions, I transfer the data into a second sheet, and then on the result of data, I insert a chart. I don't need to convert my data into a table. (Really it is not needed.)

5.What chart do you create from the data, and must it include the Sold Rate % column? I create a column chart and the percentage that is in the workbook is a value for a column chart, and yes it must include the Sold Rate as a column chart.

Hope this is clear.

P.S.
I asked my boss to give permission to servers and I can write my SQL statements and pull whatever I want, but he didn't accept.

the best part of my life is this forum and things that I can learn from here.

BittenApple,

[Rudi]

I will look into this issue a bit later; I have a few errands to run this afternoon....

[HansV]

Yes, I copy the sheets from the workbook from the run into a new workbook. If I want to keep in its original workbook, the work will be a way more. After inserting the sheet from the report into a template that I have built, I run macros, unmerge the cells, with functions: 1-I get the data into a new sheet again to show data clean, 2-then I insert a chart on the data.

Do you want to copy the entire worksheet into the template (so that it becomes a new worksheet there), or do you want to copy the data into an already existing worksheet in the template?

[Rudi]

TX for all your detailed answers.

Here is a summary of what I understand from your answers,

1. The macro will be stored in your template.
2. When triggered it will loop through all customer workbooks (that are stored in a single folder), open the workbook, unmerge the data, and then copy the data into a new blank sheet in your template. (I assume the data to copy is always on sheet 1 of the customer workbook?).
3. Each sheet collected must have a column chart created, plotting all items and the 3 columns of numerical detail.

Does this sound about right?