Question on organizing large amounts of small Excel Files

  • Thread starter Thread starter BigNate
  • Start date Start date
B

BigNate

I have a question regarding how I can best organize large amounts of
excel worksheets so that they are more managable. I am using Excel
2002, and my problem is that the tool logs for the machine that I work
with are getting out of hand. The machine stores process information
in Comma Delimited form that we download into folders on our network
drive. Usually we generate about 1500 logs per year. Currently, my
Engineer has me open each individual file and format it. I created a
small micro for this to make it easier, but it is still time consuming.
Is there a way to apply a micro to large amounts of files without
actually having to open them each? Also, once the process log
worksheet is formatted correctly, it is hyperlinked to a central work
list. Is there a way to make Excel automatically update the central
worklist, or is a database a better option at this point. I have a
good working knowlege of Excel, and a basic knowlege of programming
with visual basic (if, then, loops, ect). Any suggestions would be
greatly apprecited. Thanks
 
Hi BigNate
As I understand it, your machine generates comma delimited files
(CSV's) about 4 times a day. I also presume that these files are being
named using a certain format and that they are being sent to a specific
folder.

Now
If the name is constant like 'Report1.CSV', 'Report2.CSV' ...etc.
then you macro could be something along these lines.

Sub OpenMyToolFiles()
On Error GoTo ErrHandler
MyPath = "C:\Test\"

For i = 1 To 10 'Because you shouldnt have more than 10 files per
day
MyFile = "Report" & i & ".csv"
Workbooks.OpenText Filename:=MyFile, Origin:=437, StartRow:=1
_
, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1),
Array(2, 1), Array(3 _
, 1), Array(4, 1)), TrailingMinusNumbers:=True

******************
DO ALL YOUR FORMATTING ETC HERE.

YOU MAY WISH TO COPY THE DATA FROM THE FILE REPORTx.CSV INTO A MAIN
'DATABASE'.


******************
Next i

ErrHandler:
If Err.Number = 1004 Then Exit Sub ' Filename could not be found

End Sub

Hope this helps you
 
Thanks so much liddlem. That code worked perfect. I added on a few
variables to make it so that we could more easily access info from
different locations, but what you provided helped me so much. Thanks
again-
Nate
 
Back
Top