I have been working on a Project that uses a pre-defined .xlsx workbook with 37 tabs (yes, 37, wayyy too many!).
I gather data from multiple sources and formats (exl, xlsx, csv, txt, multiple databases, XML, etc.), collect it in a raft of SQL tables, and then extract and push data into the workbook in each tab as needed.
The issue I have is that one source sends me a single tab .xlsx file with a ton of data. This is just tab 18 from the final report file.
The issue is two things, they change the number of rows each week (minimum of 40, up to 2000). and they change some of the titles. This cuases issues with pushing data into the template.
Rather than trying to read variable data into SQL and then push it back to tab 18, I want to try to do the following:
Open both the Report.xlsx workbook, and the WeekData.xlsx workbook, then empty the entire tab 18 in the report.xlsx file and replace it entirely with the content of the single tab in the WeekData.xlsx file. This needs to copy data, formulas, formatting, charts, textboxes, everything.
The results need to stay in tab 18 (cannot change the order, or the tab name).
I'm using C#. I have looked and looked, but cannot seem to find any references to build this functionallity with a program.
Until I get this working, I'm simply opening the WeekData sheet, then opening the tab 18 in the Report, and doing a copy/Paste to get the results I need.
So easy by hand, but need to automate.
Any suggestions, examples, links that reference entire sheet moves/copies would be much appreciated.
Thanks