Posted: 9/6/2012 12:26:24 PM EDT
|
I've got a lot of Excel spreadsheets that I want to consolidate into one homogenous sheet.
They're disparate in their formatting. They're different in their length as far as columns and rows go. They have some empty columns and rows which I don't want to import. Each tab has a name I'd like to incorporate as a header where it will be imported into the single sheet. It's Excel 2010. I can't download anthing to my computer. It's not allowed. I can run the VBScripting for applications that's native to MS Office. I've found generic VB code to import data from all worksheets into a single excel file (thanks Google.) It kind of works but I don't know how exactly to refine the code to where it will pull in the tab names and prepend them to the line above where the data is imported for that tab. Suggestions? No, I can't get a Mac. No, I can't use Open Office.Org. No, I can't hire a coder. 
|
|
Quoted:
Take them home? No. My goal? To be able to filter all the information, and sort. In this case, my recommendation would be to insert the data into an Access or SQL database, then query out the results that you need. The excel sheets can be easily imported as individual tables that can be later "joined". |
|
Quoted:
Quoted:
Take them home? No. My goal? To be able to filter all the information, and sort. In this case, my recommendation would be to insert the data into an Access or SQL database, then query out the results that you need. The excel sheets can be easily imported as individual tables that can be later "joined". that is how I would solve the issue. using excel as a database is common and a bad idea. |
|
Aww, you guys rained on my parade. You know the funny thing? We've already got a SQL database... one of the goals of the 3rd version of the database is to input all of these spreadsheets into it and make it searchable. I was just trying to make it easy for me to do comparisons across several sheets.
I'll just do it the hard way until that version is made which might be in a year or two. |
|
Quoted:
Aww, you guys rained on my parade. You know the funny thing? We've already got a SQL database... one of the goals of the 3rd version of the database is to input all of these spreadsheets into it and make it searchable. I was just trying to make it easy for me to do comparisons across several sheets. I'll just do it the hard way until that version is made which might be in a year or two. Since you have Excel, there is a good chance you already have Access, too. Import the XLS files into it, and do the comparisons you need. It shouldn't take any more than five minutes per XLS file. |