Parse Data from closed workbooks, export to additional sheets
I have many workbooks that I want to extract data from, copy it to a master workbook then split that master workbook into zone workbooks. Below is the information that in relevant. I hope it all makes sense...if something doesn't make sense please help me by getting me to clarify it for you. I thank anyone willing to help with this in advance.
There will be a total of 6 zone workbooks. Determined by the cell which contains the letter "A" in each workbook contained in the directory c:\example. The zone workbooks are created from a master workbook.
The first zone workbook will contain all ranges from 00000A to 04099A.
The second zone workbook will contain all ranges from 05000A to 05046A, 06000A to 06046A, 07000A to 07046A and 08000A to 08046A.
The thrid zone workbook will contain all ranges from 05047A to 05099A, 06047A to 06099A, 07047A to 07099A, and 08047A to 08099A.
The fourth zone workbook will contain all ranges from 09000A to 09046A, 10000A to 10046A, 11000A to 11046A and 12000A to 12046A.
The fifth zone workbook will contain all ranges from 09047A to 09099A, 10047A to 10099A, 11047A to 11099A, and 12047A to 12099A.
The sixth zone workbook will contain all ranges from 13000A to 15099A.
And the file names are "xxx Rotation.xls". Where xxx is the variable portion of the file name therefore a wildcard will need to be used when dealing with the sheets in the path.
The part of each workbook that will be copied to the master sheet (the sheet that will be used to create the zone workbooks) is the cell that contains the letter "A" in the range of B3:Bx (where x equals the last row).
This data will fall into one of the ranges above.
Example: 05062A is contained in Item 1 Rotation.xls in the range B3:B33. The value is found in cell B9 this information would be copied to the master workbook.
After all workbooks have the information extracted and are listed in the master workbook this data can then be broken down into 6 zone workbooks that will then be printed and saved.
The first zone will be saved as 1 thru 4.xls
The second zone will be saved as 5 thru 8 low.xls
the third zone will be saved as 5 thru 8 high.xls
the fourth zone will be saved as 9 thru 12 low.xls
the fifth zone will be saved as 9 thru 12 high.xls
the sixth zone will be saved as 13 thru 15.xls
Last edited by randell.graybill; 05-01-2010 at 08:34 PM.
Re: Search workbooks matching wild card name
I figured out the first part, to extract the information but now I'm having trouble writing a formula that will flag the data for the different zones and the problem lies in the fact that some of the numbers have leading zeroes.
How can I get the formula to include all the correct data, this is causing me trouble due to the leading zeros.
See the new attached workbook.
Re: Parse Data from closed workbooks, export to additional sheets
I was able to resolve this issue on my own.
In order to see the solution I came up with (some directories were changed in the example posted than they are listed above). You will need to create a couple directories: C:\Rotations\05-01-2010 and C:\Example
Download the three files attached. Extract the zip file Zones.zip to to c:\Example. Extract Rotations 05-01-2010.zip to C:\Rotations\05-01-2010. Save the workbook ExampleBook.xls to any directory and run the macro ExtractLocations.
Note that some of the formatting is off in the example. But it mostly works.
Also attached is the rest of the project but that is not in 100% working condition in the examplebook (it works in the macro book I have but for some reason when imported to a new workbook it will no longer function).
In order to see what this does there is a 4th file attached: ExportReport.aspx (7).xls. There are a great deal of directories referenced in this part of the project that you will have to change / adapt to your needs. But once the directories are updated / changed, simply open the file ExportReport.aspx (7).xls and run NewRotationsMacro.
Some of the code used in this project was taken from OZGRID, some was provided by the help from exelforum (thanks DonkeyOte, and JBeaucaire, and others I'm sure I missed). And some of it was written by me from what I've learned here.
Last edited by randell.graybill; 05-01-2010 at 09:32 PM.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1