turn off echo/prevent visible activity or screen refresh

[muyBN]

I'm running a macro in Word 2003 that really chews up memory with the 100 or
so DB records I'm processing with it. I'm thinking that if I turn off screen
refresh (sometimes called echo, I believe) while it's running then back on
again when it's finished, this might speed up the macro. Any thoughts on this
idea and how to do it?

A little background as a lead-in to a secondary question: In this macro
project, I'm data-mining a Webpage saved as plain text or copied/pasted as
Edit-Special-Unformatted Text into a Word doc. I then search out certain key
caption terms (such as "Address:") then retrieve the value following them and
put them into a Word table (for manipulating); then to an Access table (for
permanent storage and query formation); and a CSV file (to produce a mailing
list). The pattern is pretty consistent from one web page to another on the
site I use, so the mining itself isn't that complicated.

The problem I find is that when I finally retrieve data from the table for
insertion into the database and the CSV file, I find that it takes much, much
longer (in run-time) to do this with ranges as compared to using Word's
Search/Find command.
(It's more convenient using cell reference ranges but it forever to process.)

Any thoughts on this? Does anyone else find the same problem with using
ranges? I have 1 GB of RAM and suppose that getting more memory could be an
answer but I wanted to see if there's anything I could do under differently
under my present constraints. Also, you may ask why I don't just import my
table or CSV file into Access. I will be giving this a try again, but I just
remember in the past that Access has given me a lot of errors when trying to
do this with a Word table or a CSV file. Therefore, I prefer to just write it
to a Word table then insert the row values with SQL.

If I'm doing the above-described procedures primitively, let me know. Thanks
in advance for any help.

 

[Klaus Linke]

Hi Bryan,

Don't use Word for this, and especially don't use Word tables.

The slow thing is accessing the content of table cells through the object
model, not the screen refresh (Application.ScreenUpdating) or Find/Replace.

You could read the text file into a string or a 2-dimensional array and
manipulate it there, from what you write, and that would be incredibly much
faster.
Then if you have cleaned it up, you can stick the data into Access.

Regards,
Klaus

 

[Bryan]

Thanks, Klaus. That was an appropriate and specific answer on the refresh isse.

I've taken a lot of time in the last couple of days to study this and came
up with a conclusion similar to yours. Had to dig and dig to find how to
write a CSV file to Access, and came up with this as the SQL statement to be
executed:

strSQL = "INSERT INTO [" & strTable & "] " & _
"SELECT * " & _
"FROM [Text;HDR=YES;DATABASE=" & strPath & ";].[" & strMailFile & "]"

Is this similar to what you would have done? I'm curious to know what you
mean by "don't use Word for this" since I feel that Word has the best
features for manipulating raw, unorganized text. Did you mean more
particularly not to use Word tables? Probably so since that seems to be the
thing that's taking the most time. At any rate, I've found that using
searches then "strVariable=Selection" runs much faster than getting values
from ranges.

I'm intrigued with your statement "You could read the text file into a
string or a 2-dimensional array and manipulate it there, from what you write,
and that would be incredibly much faster." If you can explain a little more
about this, I'd appreciate it. Maybe noting down some of my "slow code" below
for you to bounce off from would help you to do this, if you have time.
(First of all, I've got 45 fields to potentially fill for each record, so it
was a real challenge even DIMming all the related variables at the beginning
of the procedure.)

with selection
.SelectRow
intColumns = .Tables(1).Rows(1).Cells.count
intCnt = 0
blnUseRecord = True
intWorkCol = 1
intWorkRow = 2
intFieldRow = 1
End With
'search each cell for variable entry

While intWorkRow <= intRows
Documents("mbWork.doc").Activate
With ActiveDocument
While intWorkCol < intColumns
strSelectCase =
Left(.Tables(1).Rows(1).Cells(intWorkCol).Range,
Len(.Tables(1).Rows(1).Cells(intWorkCol).Range) - 2)
Documents("mbWork.doc").Activate
Select Case strSelectCase
Case "Lead_ID"
strLead_ID = mbGetCellText(strLead_ID,
"strLead_ID", strLead_ID, intWorkCol, intWorkRow, intFieldRow)
Case "List_Date"
strList_Date = mbGetCellText(strLead_ID,
"strList_Date", strList_Date, intWorkCol, intWorkRow, intFieldRow)
Case "Date"
.TypeText "List_Date"
strList_Date = mbGetCellText(strLead_ID,
"strList_Date", strList_Date, intWorkCol, intWorkRow, intFieldRow)
Case "Data_Source"
strData_Source = mbGetCellText(strLead_ID,
"strData_Source", strData_Source, intWorkCol, intWorkRow, intFieldRow)
Case "Area_ST"
strArea_ST = mbGetCellText(strLead_ID,
"strArea_ST", strArea_ST, intWorkCol, intWorkRow, intFieldRow)
Case "Area_Name"
strArea_Name = mbGetCellText(strLead_ID,
"strArea_Name", strArea_Name, intWorkCol, intWorkRow, intFieldRow)
Case "Description_Ad"
strDescription_Ad = mbGetCellText(strLead_ID,
"strDescription_Ad", strDescription_Ad, intWorkCol, intWorkRow, intFieldRow)
[up to a total of 45 cases]

.... End Select, etc

Hopefully this will give you some idea what I'm up against.

 

[Klaus Linke]

I don't know anything about SQL or Access, so you might better ask that in
another group (... or hope that some knowledgeable person jumps in here).

Is this similar to what you would have done? I'm curious to know what
you mean by "don't use Word for this" since I feel that Word has the
best features for manipulating raw, unorganized text.

One might think so, but I don't really see anything in Word that would help
you with your task -- except maybe seeing the data in the Word document as
they are manipulated, which makes it a bit easier to write and debug your
code. But Word is very slow in handling large tables, which you probably
want to do.
Maybe Excel might be better, but I don't know much about that either.

Did you mean more particularly not to use Word tables?

Those especially. If you would look closely where your code spends the most
time, it would probably be the calls to retrieve the cell contents (Function
mbGetCellText).

I'd just open the text file using "Open" or the Scripting.FileSystemObject

Const constFileNameData As String = "\\Server\data\myFolder\myTextFile.txt"
....
Dim vData
Dim myFSO
Set myFSO = CreateObject("Scripting.FileSystemObject")

Dim myTextStream
Set myTextStream = myFSO.OpenTextFile(constFileNameData, ForReading, True)

vData = myTextStream.ReadAll()
myTextStream.Close

Once you have the text file in a string or variant, you can use all the
string functions on it (Split, Filter, Join, Replace, Instr, Mid, Left, Trim
....).
If you can't then insert the data directly into Access, you'd write a
cleaned-up CSV file back to disk, and open that in Access.

Again, I think you'll probably find people who have done that kind of thing
in the Access groups (maybe Excel groups, too).

Regards,
Klaus

 

[Klaus Linke]

I'd just open the text file using "Open" or the Scripting.FileSystemObject

What's meant is the VBA Open, not Word's Document.Open:
Open "\\Server\data\myFolder\myTextFile.txt" For Binary As #iFile

Klaus