Re: Hoo Yeah!!

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ed (ed_millis_at_NO_SPAM.yahoo.com)
Date: 11/03/04 

Date: Wed, 3 Nov 2004 10:10:36 -0700

My program takes 22 database "dump" files and processes them. The dumps
consist of all the individual documents in a reports series strung together
end-to-end as one long doc; my process splits them into the individual
report docs. (No, the database can not spit out an entire individual report
as an electronic file - only as a printed file.) The dumps range in size
from 33kb to 22Mb. The smallest dump only has 4 reports in its series; the
largest one has over 2500. The individual report docs generally range from
6 kb to 80 kb; only three are over 100 kb.

Along with separating the reports, the text string searches I have (just!)
incorporated search each individual report for 18 specific data points which
populate an Excel spread***, which serves as an index to the entire mass
of reports, and allows filtering and searching to find specific reports..
After today's run, there are 14,284 separate reports.

To get the data points, I use macro with a loop with Select Case; a loop
counter sets the Case number. The whole macro is run with each new report
document created. I'm sure there are more streamlined ways of doing this,
but I'm not a real programmer. Also, although my "customers" enjoy the
availability and functionality of this, I have to sneak my work into dead
spots in the work load because it's not something the higher-ups have given
as a time-chargeable task. I originally created this for my own use, as I
have to write the system-level reports based on these individual reports.
When I shared it, they liked it - now I'm stuck with it and get phone calls
if it's not updated, but can't charge time to it. Go figure!

Here's my code after the Select Case for grabbing the text strings. The
Case selected sets strFind and lenInfo.

Ed

lenFind = Len(strFind)

posHere = InStr(1, strNewDoc, strFind, vbTextCompare)

On Error Resume Next
If lenInfo > 0 Then
    strInfo = Mid(strNewDoc, posHere + lenFind + lenXtra, lenInfo)
    strInfo = trim(strInfo)
Else
    strInfo = Mid(strNewDoc, posHere - Abs(lenInfo), Abs(lenInfo))
    strInfo = trim(strInfo)
End If
On Error GoTo 0

' Write info to report table workbook
objWkbk.Worksheets("Sheet1").Cells(ThisRow, cntLoop).Value = strInfo

cntLoop = cntLoop + 1

Loop While cntLoop < 19

"Schmidt" <sss@online.de> wrote in message
news:uk3qYKcwEHA.1308@TK2MSFTNGP09.phx.gbl...
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> schrieb im Newsbeitrag
> news:Op8lmubwEHA.1400@TK2MSFTNGP11.phx.gbl...
> > Using InStr, Left, Right, and Mid, my time to process 14,000 documents
> went
> > from 3 1/2 hours to under 45 minutes!! **THANK YOU!!**
>
> May I ask, what's your average filesize and could you post some code, how
> your typical processing is done now (including the FileRead).
> Would think, 45 Minutes should be ok for an average filesize of
> 500K-1MB -but if the files are more in a range of around 50-100K, then 45
> minutes would be a bit much IMO.
>
> Olaf
>
>

Quantcast