Excel Help

suadeo

At my job, there is a program that we use for calculating energy savings and it can export all the data to a excel spreadsheet. But the workbook is very ugly and has a lot of useless information. So, I was wondering if there is a way in excel that you can import a file into a currently formated workbook? This way, we just import it into the workbook and it populates/formats all the data for us. I was going to make a program in C# that drops all the data into another workbook, but I wanted to see if there was a way with excel. Thanks.

Desktop Hippie

I don't know about exporting part of the information to an existing workbook, but you could always link the cells in the formatted workbook to the "ugly" workbook using a function so it displays the information you want.

suadeo

Thats what I thought about, but this will be used for multiple jobs. About 5-10 a week. Which means (I think), I would have to change reference 5-10 times a week. Perhaps it will be easier just to code a small program.

focused7

Could you just open the old workbook and Copy / Paste Special Values into the new workbook format?

whuppins

If Excel macros fall under your definition of "coding a small program", then it's not a hard thing to do. I can't give specific code samples because you haven't given details about the layouts of your export files and desired final product, but pretty much any Excel command (including formatting if need be) can be automated via VBA.

The easiest, most basic solution would be to create a 'master' workbook. One sheet would be the 'template', containing no actual data but formatted exactly the way you want; sums and other formula logic can be put in and ready to go.

You can then add a button to the workbook that does the following:

- Pop up a standard Windows dialog box prompting the user to select the export file to be formatted
- Open the export file, move the values in column X on the export to column Y on the master workbook
- Save a copy of the 'template' sheet, now with filled values, as a separate workbook
- Close the 'master' and export files

This can be done very quickly and easily in the Excel VBA editor -- as little as 4 or 5 lines of code depending on how many blocks of data need to be moved from one sheet to the other. If you don't mind writing a little bit more code, you can even do slick things like having it automatically identify files to be formatted and doing the whole thing without you even having to click a button.

I can give you examples of code or even write the thing for you if you give me an idea of what you're getting and how you want it to be formatted. PM me if you like; I love working with Excel automation.

suadeo

focused7 wrote: »

Could you just open the old workbook and Copy / Paste Special Values into the new workbook format?

Me and another guy just talked about this. This is the way I am going to do it since looking over the data, not all the excel sheets layout the same (about 6 different types). Thanks for the suggestions!