VBA, How To write huge amount of data in to a table

Charles Kenyo
#1 / 3

How To write huge amount of data in to a table

Hi,

The protocol in this newsgroup is to include the code in the message text
rather than as an attachment. Among other good reasons for following this
protocol is that some of the best answers in these newsgroups come from
people who have their newsreader software set to not ever download messages
that have attachments. Thought you might like to know.
--
Charles Kenyon

Word New User FAQ & Web Directory:
<URL: http://www.*-*-*.com/ ;

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide)
<URL: http://www.*-*-*.com/ ;

See also the MVP FAQ: <URL: http://www.*-*-*.com/ ; which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

Quote:

> hi,
> i am facing a problem when creating a document in word, the problem is
> that my code get's data from an oracle database & this data is inserted
into
> a word document which contains 3 columns & as many rows as returned from
the
> database the columns are of fixed size, when i run the code there are 2500
> plus records returned from the database ,once i get this data i start to
> write in in each cell of the table cell by cell this operation takes a
long
> time.. to complete to generate about 200 pages it takes almost 4 hrs which
> is a long time, is there any way that i can reduce this time

> thanks

> Aloysius Martis
> ps : attaching the code

Sun, 16 May 2004 16:40:49 GMT

Mark Tangar
#2 / 3

How To write huge amount of data in to a table

Typically, sluggish behavior when writing to tables increases geometrically
with the size of the table, so the first I'd try is chopping up your task
into, say, 10 or 20 tables -- making sure they're separated by at least
one blank paragraph (otherwise they don't really stay un-joined) -- then
push them together afterward by removing the blank paragraphs between.
This assumes you're filling the table one row at a time. (I don't have
the time to look through all your code to be certain of that.)

It's a bit clumsy but it might work.

-- See the MVP FAQ at http://www.mvps.org/word ------------------------
--------------- "Life is nothing if you're not obsessed." --John Waters
-----------------------------------------------------------------------
Please reply only to the newsgroup.

Quote:

> hi,
> i am facing a problem when creating a document in word, the problem is
> that my code get's data from an oracle database & this data is inserted into
> a word document which contains 3 columns & as many rows as returned from the
> database the columns are of fixed size, when i run the code there are 2500
> plus records returned from the database ,once i get this data i start to
> write in in each cell of the table cell by cell this operation takes a long
> time.. to complete to generate about 200 pages it takes almost 4 hrs which
> is a long time, is there any way that i can reduce this time

> thanks

> Aloysius Martis
> ps : attaching the code

> 'On Error GoTo ehExecuteSQL
> Dim objword As Word.Application
> Dim oDoc As Word.Document
> Dim alphaCount As Integer
> Dim iOtel_linecount As Integer
> 'Start a new document in Word
> Set objword = CreateObject("Word.Application")
> objword.Visible = True
> Set oDoc = objword.Documents.Add

> Set db = New ADODB.Connection

> db.ConnectionString = Data_Conn
> db.Open

> strQry = "select count(count(upper(substr(nickname,1,1)))) - 1 CNT from as_mt_addressbook where " & _
> "user_id=" & User_ID & " and upper(w_country) = upper('india') group by rollup(upper(substr(nickname,1,1)))"
> Set rs = New ADODB.Recordset
> rs.CursorLocation = 3
> rs.Open strQry, db, 3, 2, 1
> If rs.RecordCount > 0 Then
> If rs(0) > 0 Then
> alphaCount = rs(0)
> Else
> alphaCount = 1
> End If
> Else
> alphaCount = 1
> End If
> rs.Close
> Set rs = Nothing

> Set rs = New ADODB.Recordset
> rs.CursorLocation = 3

> 'qry = "SELECT NICKNAME,W_PHONE,H_PHONE,OTEL1,OTEL2,OTEL3,OTEL4,W_PAGER,W_FAX,CELLULAR,H_FAX,H_PHONE2 FROM AS_MT_ADDRESSBOOK WHERE USER_ID=1"
> rs.Open qry, db, 3, 2, 1

> If rs.RecordCount <> 0 Then

> j = rs.RecordCount
> 'writing first page info ex :username
> oDoc.PageSetup.DifferentFirstPageHeaderFooter = True
> With oDoc.Sections(1).Headers(wdHeaderFooterFirstPage)
> .Range.InsertAfter User_Name & vbCr & _
> " NAME OFFICE HOME"
> .Range.Paragraphs.Alignment = wdAlignParagraphRight
> End With
> With oDoc.Sections(1).Footers(wdHeaderFooterFirstPage)
> .Range.Text = "Date: " & Date & " Page: 1"
> End With

> 'WRITING THE HEADER
> With oDoc.Sections(1)
> .Headers(wdHeaderFooterPrimary).Range.Text = "Telephone Diary OWNER:" & User_Name & "" & vbCr & "" & _
> "" & vbCr & "--------------------------------------------------------------------------------------------------------" & _
> " NAME BUSINESS HOME " & _
> "" & vbCr & "--------------------------------------------------------------------------------------------------------"
> .Headers(wdHeaderFooterPrimary).Range.Bold = True

> End With

> 'WRITING THE FOOTER
> With oDoc.Sections(1).Footers(wdHeaderFooterPrimary)

> .Range.Text = "Date: " & Date & " Page:"
> .PageNumbers.Add PageNumberAlignment:=wdAlignPageNumberRight
> End With

> 'Set oDoc = ActiveDocument
> 'Range:=oDoc.Range(Start:=0, End:=0), NumRows:=j,
> Set otable = oDoc.Tables.Add( _
> Range:=oDoc.Range(Start:=0, End:=0), NumRows:=((j * 2) + (alphaCount)), _
> NumColumns:=3)
> icount = 1
> otable.Rows.AllowBreakAcrossPages = False
> otable.AutoFormat Format:=wdTableFormatSimple1, _
> ApplyBorders:=False, ApplyFont:=True, ApplyColor:=True
> 'For Each ocell In otable.Range.Cells
> 'oCell.Range.InsertAfter "Cell " & iCount
> ' icount = icount + 1
> 'Next ocell
> otable.Columns(1).PreferredWidth = InchesToPoints(2)
> otable.Columns(2).PreferredWidth = InchesToPoints(2.5)
> otable.Columns(3).PreferredWidth = InchesToPoints(2.5)

> 'otable.AutoFormat ApplyBorders:=True, ApplyFont:=True, ApplyColor:=True

> If oDoc.Tables.Count >= 1 Then
> icount = 0
> For j = 0 To (rs.RecordCount - 1)
> icount = icount + 1
> If checkCharFilter(Mid(Trim(UCase(rs("NICKNAME"))), 1, 1)) <> True Then
> 'With oDoc.Tables(1).Cell(Row:=icount, Column:=1)
> ' .SetWidth ColumnWidth:=130, _
> ' RulerStyle:=wdAdjustFirstColumn
> With oDoc.Tables(1).Cell(Row:=icount, Column:=1).Range
> '.Font.Name = Font_Type
> '.Font.Size = CInt(Font_Size)
> '.Bold = False
> '.Paragraphs.Alignment = wdAlignParagraphRight
> .Delete
> .InsertAfter Text:=" "
> End With
> 'End With
> 'With oDoc.Tables(1).Cell(Row:=icount, Column:=2)
> ' .SetWidth ColumnWidth:=170, _
> ' RulerStyle:=wdAdjustFirstColumn
> With oDoc.Tables(1).Cell(Row:=icount, Column:=2).Range
> ' .Font.Name = Font_Type
> ' .Font.Size = CInt(Font_Size)
> ' .Bold = True
> ' .Paragraphs.Alignment = wdAlignParagraphRight
> .Delete
> .InsertAfter Text:=" "
> End With
> 'End With
> 'With oDoc.Tables(1).Cell(Row:=icount, Column:=3)
> ' .SetWidth ColumnWidth:=170, _
> ' RulerStyle:=wdAdjustFirstColumn
> With oDoc.Tables(1).Cell(Row:=icount, Column:=3).Range
> .Font.Name = Font_Type
> .Font.Size = CInt(Font_Size)
> .Bold = True
> .Paragraphs.Alignment = wdAlignParagraphRight
> .Delete
> .InsertAfter Text:=sCharFilter
> End With
> 'End With
> icount = icount + 1
> End If ' if checkfilter
> 'With oDoc.Tables(1).Cell(Row:=icount, Column:=1)
> ' .SetWidth ColumnWidth:=130, _
> ' RulerStyle:=wdAdjustFirstColumn

> With oDoc.Tables(1).Cell(Row:=icount, Column:=1).Range
> .Delete
> .InsertAfter Text:=rs("nickname")
> End With
> ' End With

> OTel_Disp = " "
> iOtel_linecount = 0
> If Trim(rs("w_phone")) <> "" Then
> OTel_Disp = OTel_Disp & "DL:" & rs("w_phone") & ", "

> End If
> If Trim(rs("OTEL1")) <> "" Then
> OTel_Disp = OTel_Disp & rs("OTEL1") & ", "
> End If
> If Trim(rs("OTEL2")) <> "" Then
> OTel_Disp = OTel_Disp & rs("OTEL2") & ", "
> End If
> If Trim(rs("OTEL3")) <> "" Then
> OTel_Disp = OTel_Disp & rs("OTEL3") & ", "
> End If
> If Trim(rs("OTEL4")) <> "" Then
> OTel_Disp = OTel_Disp & rs("OTEL3") & ", "
> End If
> If Trim(rs("SPLINT")) <> "" Then
> 'If rs("SPLINT") <> Null Then
> OTel_Disp = OTel_Disp & "SEC:" & rs("SPLINT") & ", "
> 'End If
> End If
> olen = Len(OTel_Disp)
> If Trim(OTel_Disp) <> "" Then
> OTel_Disp = Left(OTel_Disp, olen - 2)
> End If

> 'olen = Len(OTel_Disp)
> If Trim(OTel_Disp) <> "" Then
> If Trim(rs("W_PAGER")) <> "" Then
> OTel_Disp = OTel_Disp & vbCr & "PAGER: " & rs("W_PAGER")
> End If
> Else
> If Trim(rs("W_PAGER")) <> "" Then
> OTel_Disp = "PAGER: " & rs("W_PAGER")
> End If
> End If
> If Trim(OTel_Disp) <> "" Then
> If Trim(rs("W_FAX")) <> "" Then
> OTel_Disp = OTel_Disp & vbCr & "FAX: " & rs("W_FAX") & ""
> End If
> Else
> If Trim(rs("W_FAX")) <> "" Then
> OTel_Disp = "FAX: " & rs("W_FAX") & ""
> End If
> End If
> If Trim(OTel_Disp) <> "" Then

...

read more »

Sun, 16 May 2004 17:48:42 GMT

ibby
#3 / 3

How To write huge amount of data in to a table

Hi Aloysius,

I think the fastest way of achieving this is to insert the raw text into the
document, with fields/records delimited, then using the ConvertToTable
method to convert it to a table.

--
Hope this helps.
ibby

Please post replies or follow-ups to the **newsgroup** so that participants
may benefit or contribute.

Quote:

> hi,
> i am facing a problem when creating a document in word, the problem is
> that my code get's data from an oracle database & this data is inserted
into
> a word document which contains 3 columns & as many rows as returned from
the
> database the columns are of fixed size, when i run the code there are 2500
> plus records returned from the database ,once i get this data i start to
> write in in each cell of the table cell by cell this operation takes a
long
> time.. to complete to generate about 200 pages it takes almost 4 hrs which
> is a long time, is there any way that i can reduce this time

> thanks

> Aloysius Martis
> ps : attaching the code

Sun, 16 May 2004 19:52:33 GMT