Click here to Skip to main content
15,919,434 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted

I would use the Excel macro capability. You may need to add the developer menu by going to Excel Optons by clicking the button on the panel after clicking the Microsoft Office button. It is in the popular category

This will allow you to record your button clicks (Record Macro on Developer tab). You can then edit the VBA code to make it more flexible. This code may also help in writing C# code using the office interopt.

Note: there is an example or running a macro from C# on the following link:

http://support.microsoft.com/kb/306683[^]
 
Share this answer
 
v2
Comments
abupsman 2-Mar-12 17:16pm    
This is a great bit, unfortunatly will not help me. There is one item I forgto to add, the resulting report cannot have Macro's in it. the end reciever does not want them in the sheet in any form.
I suppose I could add them to the report, run it, then delete them before e-mailing the results out.
Clifford Nelson 2-Mar-12 17:40pm    
Then just see how it is done if done with VBA. Then might be able to recreate it in C#.
Due to a number of issues with security and the complexity and requirments of the destination xlsx file, I went the long route.
Because the format of the source document cannot change (violation of business rules if they add or drop a column), I built a small "ripping" program that counts the number of lines in each section, then uploads the sections to SQL.
Once it's there, I use stored procedures called by a program built earlier that takes resuklts from a stored procedure and plugs it into the correct sheet and location on my reporting template xlsx file.
This keeps the tab order as needed and only adds a few extra steps to the automated process.
Thanks for the suggestions, I will be able to use on another project coming up this summer.
Don
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900