Excel Spreadsheets Reformat Question

[JeanneJo]

I have a disk full of Excel files; I suspect they were created by exporting
from an accounting program to Excel to share the financial information with
others. Each of these files needs to be PDFd and Bates stamped for
production in a legal matter. The problem is this: every single one of these
Excel files is not formatted to display all of the information - in order to
get a readable page, the columns need to be reformatted to display all of the
information contained in each of them, then saved. There are literally
hundreds of these files; would there be an easier way of getting these things
formatted so all of the information is readable besides opening every single
one of them, reformatting, and then saving again?

Thanks very much.

JeanneJo.

 

[JLatham]

Probably the easiest way would be to take just one of them and record a macro
while going through it and performing all of the reformatting that has to be
done.

At that point (and assuming all of the others are generally laid out the
same way: have same sheet names that need to be worked with, and same columns
used for same purposes, etc), you'd probably come back here and ask "I have
this great recorded macro... how do I get it to work on all workbooks in a
folder?" And you'd post the macro code that was recorded.

Then someone is going to come along and show you how to 'wrap' that macro
inside of a routine that will let you identify the folder with all of the
files in it and open each file in turn and process them with the code from
your macro, and save the reformatted file.

Yes, you can get in touch with me about this at (remove spaces)
Help From @ JLatham Site.com
(but preferably not until after you've recorded the macro).

Of course, if each workbook is really unique with regards to sheet naming
and layout, then you could be in for a really long weekend.

 

[JeanneJo]

Wow - I was afraid of that. I've run into this many times and finally
decided to ask, although I was pretty certain "simple" wasn't going to be the
answer. There are, of course, a variety of types of information exported to
Excel in this particular project - usually 12 months worth of for instance
check reconciliation, receivables, income, and so forth. I'll have to delve
a little deeper into what exactly I have here - I just picked a folder and
started in.

I will contact you if I can't get this mastered on my own. I appreciate
your response, and thanks very much.

JeanneJo

 

[JLatham]

Ok, if you do need to get in touch, go ahead, just be sure to reference this
discussion and maybe even copy your initial post into the email, or provide a
link to this to remind me of what it's all about.

Of course, you could start an "Intern Program" and get some
wanna-be-a-federal-judge-someday to come in and do the work for cheap! <g>

Responding in this discussion is pretty useless - it no longer sends
notifications that responses to posts have been made!