Rory Downey presented the following explanation :
I am recieving 10 different spreadsheets from customers which all contain the
information I need. The issue arises that in that I only need 7 columns from
each workbook, but they are not in the same order. Is there anyway to sort
this?
I can collate the data into one workbook if this will help. I get these
reports on a monthly basis so I would love a macro to help fix this.
Thanks
First thing I'd do is give everyone sending you these workbooks a
template. That way all your workbooks will be the same. Use trainng
discipline if necessary, otherwise you're going to be pulling your hair
out because code needs structured data to work efficiently on that
data.
Second thing I'd do is use ADODB to read the data from each file
without having to physically open every workbook to get the data. This
requires that structure I mentioned previously.
Template design:
Make sure the columns have headings (also known as DataFields) in row
1.
Only provide enough columns for the expected data, 1 column per field.
Break each piece of info into the smallest element it can be.
For example...
Elmer E Fudd
..can be parsed into 3 bits of data:
Lastname, Firstname, Initial.
123 My Street, MyTown, MyZip, MyState/Prov, MyCountry
..can be parsed into 5 bits of data.
Once you have all your template requirements designed, distribute a
copy to each user with an instruction sheet next to the data sheet.
Meanwhile, you can physically reorder the columns in your existing
files so they are matched for data 'type' column-for-column. Once
reordered you can save them to a specified folder where you will store
their respective month's files. Once stored then you can walk through
them with ADODB and pull the data into your consolidation workbook that
is also created from a template you pre-designed for this repetitive
task.
IMO, it would be better if the template you distribute outputs the data
to a CSV so your users aren't sending their workbooks to you every
month. In fact, your templates can have sheets for each month of a
given calendar (or fiscal) year and this is how you could archive the
original data. If users just send you a CSV then that's less storage
space on disk, smaller files for more email-friendliness, and it's
easier/faster to work with CSVs than using ADODB. Your consolidation
template can also have a sheet for each month of a calendar (or fiscal)
year so it matches what users have. Your considation template can
contain the code to process your monthly task. Similarly, the template
you give to users can have the code to output their monthly data to
CSVs. (Optionally, both can be serviced by an addin so the workbooks
have no macros) Macros can automate the entire process such that after
processing a month's data the workbooks can be 'set' for the next
month's input so there's only one month being worked on at a time.
(Or.., you can just have 13 sheets visible all the time in case
updating/editing a non-current month is necessary)
Bottom line is you need a plan that structures the task for efficient
productivity, accuracy, and repeatable integrity...
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
