How to replace 1 entire sheet in an .xlsx workbook

Question

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 2-Mar-12 8:30am

abupsman

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Clifford Nelson · Answer 1 · 2012-03-02T08:51:00

Solution 1

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[^]

Posted 2-Mar-12 8:51am

Clifford Nelson

Updated 2-Mar-12 8:56am

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#.

abupsman · Answer 2 · 2012-03-19T04:17:00

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