If someone gets this, they should sit on top of a mountain dressed in a flowing white robe while we peasants bow down at your feet with worthless Excel questions to The Exultant Master of Excel.
Here goes.
Every day, I download 1 to 3 Excel files DATA info from the Mantis application.
They are downloaded from the application into C:\users\Robert\downloads
The files will be numbered 1 up and named as (for example 3 files today):
rwagner(17).xml
rwagner(16).xml
rwagner(15).xml
Each of the 1 to 3 files will always have the same format (but different number of rows)
The format will be something like this (all have the same header row):
ID......Reporter.....Assigned to....blah..blah..blah
332....Joe............Tom..............blah..blah..blah
Note: Putting in the periods as spaces in case this forum will shrink the lines with spaces.
I only keep the Header (and data rows) from the first file I grab. But then I delete the header (but keep the data rows) from the subsequent files and concatenate them together into on big file.
I manually copy/paste the individual files into a larger Excel file (for example)
ID....Reporter....Assigned to...blah...blah........Header from the first file
332...Joe...........Tom.................................Data from the first file
Data........................................................Data from the second file (if there was one)
Data........................................................Data from the third file (if there was one)
Then, I go back into Mantis and create 1, or 2, or 3 Excel files for SYSTEM info and repeat the manual concatenating process.
End Result? Two concatenated Excel files (DATA workbook and SYSTEM workbooks)
Then, I open an new Workbook with my Morning Report template, copy the concatenated DATA workbook into the Morning Report Data tab, copy the concatenated SYSTEM workbook into the Morning Report SYSTEM Tab and sort both tabs with preexisting code you have already helped me with,
So far so good eh?
OK, here comes the fun part, and here is where I need some huge help:
Possibly describing the over all desired process might be better help.
I still manually start up an Mantis application and create 1, or 2 or 3 filtered excel downloads for the DATA records (using the Mantis filter function).
Your new code will concatenate the individual Excel DATA workbooks into a larger Excel workbook, delete the individual downloaded workbooks and copy the large Excel file into a new Excel Morning Report (from My Templates named Morning Report) into the DATA tab. Note: It should ask me if I want it copied into the System or the Data tabs and do so accordingly.
Then, I manually start the Mantis application again and create 1, or 2 or 3 filtered downloaded Excel workbooks for the SYSTEM records (using the Mantis filter function).
Your code will will repeat the above process but copy it into the SAME Excel Morning Report into the SYSTEM tab.
Note: The Morning Report template has preexisting code in it that sorts the DATA tab rows and copies the sorted rows into the SYSTEM AND DATA tab.
Then the preexisting code sorts the SYSTEM tab rows and copies the sorted rows into into the SYSTEM AND DATA tab.
I would place your new Concatenating and copy/paste code on top of the existing sorting code in the template.
Then, your new code will save this template as a " "current date" Morning Report xls" workbook into c:\users\Robert\Morning Reports
So, in closing, I am trying to eliminate the manual concatenation and copies (from the Download directory) by having some huge help.
Thanks ever so much in advance......this is going to be interesting
Bob
Here goes.
Every day, I download 1 to 3 Excel files DATA info from the Mantis application.
They are downloaded from the application into C:\users\Robert\downloads
The files will be numbered 1 up and named as (for example 3 files today):
rwagner(17).xml
rwagner(16).xml
rwagner(15).xml
Each of the 1 to 3 files will always have the same format (but different number of rows)
The format will be something like this (all have the same header row):
ID......Reporter.....Assigned to....blah..blah..blah
332....Joe............Tom..............blah..blah..blah
Note: Putting in the periods as spaces in case this forum will shrink the lines with spaces.
I only keep the Header (and data rows) from the first file I grab. But then I delete the header (but keep the data rows) from the subsequent files and concatenate them together into on big file.
I manually copy/paste the individual files into a larger Excel file (for example)
ID....Reporter....Assigned to...blah...blah........Header from the first file
332...Joe...........Tom.................................Data from the first file
Data........................................................Data from the second file (if there was one)
Data........................................................Data from the third file (if there was one)
Then, I go back into Mantis and create 1, or 2, or 3 Excel files for SYSTEM info and repeat the manual concatenating process.
End Result? Two concatenated Excel files (DATA workbook and SYSTEM workbooks)
Then, I open an new Workbook with my Morning Report template, copy the concatenated DATA workbook into the Morning Report Data tab, copy the concatenated SYSTEM workbook into the Morning Report SYSTEM Tab and sort both tabs with preexisting code you have already helped me with,
So far so good eh?
OK, here comes the fun part, and here is where I need some huge help:
Possibly describing the over all desired process might be better help.
I still manually start up an Mantis application and create 1, or 2 or 3 filtered excel downloads for the DATA records (using the Mantis filter function).
Your new code will concatenate the individual Excel DATA workbooks into a larger Excel workbook, delete the individual downloaded workbooks and copy the large Excel file into a new Excel Morning Report (from My Templates named Morning Report) into the DATA tab. Note: It should ask me if I want it copied into the System or the Data tabs and do so accordingly.
Then, I manually start the Mantis application again and create 1, or 2 or 3 filtered downloaded Excel workbooks for the SYSTEM records (using the Mantis filter function).
Your code will will repeat the above process but copy it into the SAME Excel Morning Report into the SYSTEM tab.
Note: The Morning Report template has preexisting code in it that sorts the DATA tab rows and copies the sorted rows into the SYSTEM AND DATA tab.
Then the preexisting code sorts the SYSTEM tab rows and copies the sorted rows into into the SYSTEM AND DATA tab.
I would place your new Concatenating and copy/paste code on top of the existing sorting code in the template.
Then, your new code will save this template as a " "current date" Morning Report xls" workbook into c:\users\Robert\Morning Reports
So, in closing, I am trying to eliminate the manual concatenation and copies (from the Download directory) by having some huge help.
Thanks ever so much in advance......this is going to be interesting
Bob
Last edited:
