Need some help with VBA as it applies to Word AutoFormat

Here is the problem in a nutshell:

I launch Word thru automation.
I then populate tabular data into a document that I obtain from a
database
I format the data using the AutoFormat property off the table object
I save the document as an HTML file.

>From what I can see, the HTML file is saved, but the data looks to be
missing.

So, instead of saving it to an HTML file, I saved it to a DOC file.

Then, I see what the problem is -- the formatting has not been applied
(or completed) prior to saving the document. If I format it, or change
the formatting, I find that the data is correct.

I try this without automation, and what I find is that for a large
table, the data is populated first, and the formatting takes place
(likely) in the background, and takes some time to finish.

What is likely happening is that the save occurs before the formatting
has completed.

Any way to solve this problem, such that I can wait for some event or
some flag to be set before I initiate the save operation?

Are there properties in the Word object or its sub-objects that I can
set to achieve the desired result? Something along the lines of a
non-background formatting?

Any pointers would be a big help.

Thanks in advance.

Charles
Mon Apr 04 22:43:31 CDT 2005

I'm amazed that AutoFormat gives you a result that you find useful. If this
is a later version of Word, applying a table style might be quicker.
--
Charles Kenyon

Word New User FAQ & Web Directory: http://addbalance.com/word

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide) http://addbalance.com/usersguide

See also the MVP FAQ: http://www.mvps.org/word 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.

<shripathikamath@hotmail.com> wrote in message
news:1112670464.116484.77670@f14g2000cwb.googlegroups.com...
> Here is the problem in a nutshell:
>
> I launch Word thru automation.
> I then populate tabular data into a document that I obtain from a
> database
> I format the data using the AutoFormat property off the table object
> I save the document as an HTML file.
>
>>From what I can see, the HTML file is saved, but the data looks to be
> missing.
>
> So, instead of saving it to an HTML file, I saved it to a DOC file.
>
> Then, I see what the problem is -- the formatting has not been applied
> (or completed) prior to saving the document. If I format it, or change
> the formatting, I find that the data is correct.
>
> I try this without automation, and what I find is that for a large
> table, the data is populated first, and the formatting takes place
> (likely) in the background, and takes some time to finish.
>
> What is likely happening is that the save occurs before the formatting
> has completed.
>
> Any way to solve this problem, such that I can wait for some event or
> some flag to be set before I initiate the save operation?
>
> Are there properties in the Word object or its sub-objects that I can
> set to achieve the desired result? Something along the lines of a
> non-background formatting?
>
> Any pointers would be a big help.
>
> Thanks in advance.
>

shripathikamath
Mon Apr 04 23:12:34 CDT 2005


Charles Kenyon wrote:
> I'm amazed that AutoFormat gives you a result that you find useful.
If this
> is a later version of Word, applying a table style might be quicker.
> --
> Charles Kenyon
>

I need to make it work with Word 2000, XP, and 2003.

The speed is fine, the results are not.

Jean-Guy
Tue Apr 05 00:37:58 CDT 2005

shripathikamath@hotmail.com was telling us:
shripathikamath@hotmail.com nous racontait que :

> Here is the problem in a nutshell:
>
> I launch Word thru automation.
> I then populate tabular data into a document that I obtain from a
> database
> I format the data using the AutoFormat property off the table object
> I save the document as an HTML file.
>
>> From what I can see, the HTML file is saved, but the data looks to
>> be missing.
>
> So, instead of saving it to an HTML file, I saved it to a DOC file.
>
> Then, I see what the problem is -- the formatting has not been applied
> (or completed) prior to saving the document. If I format it, or
> change the formatting, I find that the data is correct.
>
> I try this without automation, and what I find is that for a large
> table, the data is populated first, and the formatting takes place
> (likely) in the background, and takes some time to finish.

Not likely, formatting is fairly fast, unless you have a huge table and are
applying many formatting operation. But with Auto it is usually fairly
quick.

> What is likely happening is that the save occurs before the formatting
> has completed.

I do not think this is possible. If you apply formatting, then Word will
complete the event before moving on to the next one (I mean it cannot save
while it is formatting, either it is formatting or it is saving).

There maybe something else going on here.
How are you automating Word?

In any case, have you tried with a Do Events in your code?

Or, Save it twice before closing it?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

shripathikamath
Tue Apr 05 00:55:39 CDT 2005


Jean-Guy Marcil wrote:
> shripathikamath@hotmail.com was telling us:
> shripathikamath@hotmail.com nous racontait que :
>
> > Here is the problem in a nutshell:
> >
> > I launch Word thru automation.
> > I then populate tabular data into a document that I obtain from a
> > database
> > I format the data using the AutoFormat property off the table
object
> > I save the document as an HTML file.
> >
> >> From what I can see, the HTML file is saved, but the data looks to
> >> be missing.
> >
> > So, instead of saving it to an HTML file, I saved it to a DOC file.
> >
> > Then, I see what the problem is -- the formatting has not been
applied
> > (or completed) prior to saving the document. If I format it, or
> > change the formatting, I find that the data is correct.
> >
> > I try this without automation, and what I find is that for a large
> > table, the data is populated first, and the formatting takes place
> > (likely) in the background, and takes some time to finish.
>
> Not likely, formatting is fairly fast, unless you have a huge table
and are
> applying many formatting operation. But with Auto it is usually
fairly
> quick.
>

I can only describe what I see. I can see that the table is drawn, but
empty, and then it finishes formatting a little while later.

> > What is likely happening is that the save occurs before the
formatting
> > has completed.
>
> I do not think this is possible. If you apply formatting, then Word
will
> complete the event before moving on to the next one (I mean it cannot
save
> while it is formatting, either it is formatting or it is saving).
>

So I thought. Until I put a Beep following the AutoFormat method
invocation, and I heard the Beep *long* before the table finished
formatting.

> There maybe something else going on here.
> How are you automating Word?
>

Actually, in this test, I am simply opening Word, and running a macro
from my add-in.

The addin reads a table of data from a database, and puts it into an
open document, and applies a known format (Colorful 1).

The table has approximately 500 rows. The formatting finishes within a
couple of seconds, but definitely there is a noticeable pause prior to
the table appearing in its full format.

WHen I run it in automation, I launch Word, open a document, and run
the macro from within the addin.

What gets saved appears very much like what I see just prior to the
data appearing fully formatted as described above.

If I select the table, because of the selection colors, I do see that
data is indeed (correctly) saved in the document. Just the format is
missing, and I see blank rows.



> In any case, have you tried with a Do Events in your code?
>
> Or, Save it twice before closing it?
>

Thanks, I have not, so I'll try that.


>From the mvps site, I see a suggestion for using one of the dialogs.
DO you know if something like that is faster.

Actually speed is not an issue, the 2 to 5 seconds it takes is
perfectly fine, I just need the formatting to be complete before I save
it.

I'll try your suggestions.

Thanks



> --
> Salut!
> _______________________________________
> Jean-Guy Marcil - Word MVP
> jmarcilREMOVE@CAPSsympatico.caTHISTOO
> Word MVP site: http://www.word.mvps.org

Chuck
Tue Apr 05 10:53:09 CDT 2005

If you post the code it might be easier to help?

"shripathikamath@hotmail.com" wrote:

>
> Jean-Guy Marcil wrote:
> > shripathikamath@hotmail.com was telling us:
> > shripathikamath@hotmail.com nous racontait que :
> >
> > > Here is the problem in a nutshell:
> > >
> > > I launch Word thru automation.
> > > I then populate tabular data into a document that I obtain from a
> > > database
> > > I format the data using the AutoFormat property off the table
> object
> > > I save the document as an HTML file.
> > >
> > >> From what I can see, the HTML file is saved, but the data looks to
> > >> be missing.
> > >
> > > So, instead of saving it to an HTML file, I saved it to a DOC file.
> > >
> > > Then, I see what the problem is -- the formatting has not been
> applied
> > > (or completed) prior to saving the document. If I format it, or
> > > change the formatting, I find that the data is correct.
> > >
> > > I try this without automation, and what I find is that for a large
> > > table, the data is populated first, and the formatting takes place
> > > (likely) in the background, and takes some time to finish.
> >
> > Not likely, formatting is fairly fast, unless you have a huge table
> and are
> > applying many formatting operation. But with Auto it is usually
> fairly
> > quick.
> >
>
> I can only describe what I see. I can see that the table is drawn, but
> empty, and then it finishes formatting a little while later.
>
> > > What is likely happening is that the save occurs before the
> formatting
> > > has completed.
> >
> > I do not think this is possible. If you apply formatting, then Word
> will
> > complete the event before moving on to the next one (I mean it cannot
> save
> > while it is formatting, either it is formatting or it is saving).
> >
>
> So I thought. Until I put a Beep following the AutoFormat method
> invocation, and I heard the Beep *long* before the table finished
> formatting.
>
> > There maybe something else going on here.
> > How are you automating Word?
> >
>
> Actually, in this test, I am simply opening Word, and running a macro
> from my add-in.
>
> The addin reads a table of data from a database, and puts it into an
> open document, and applies a known format (Colorful 1).
>
> The table has approximately 500 rows. The formatting finishes within a
> couple of seconds, but definitely there is a noticeable pause prior to
> the table appearing in its full format.
>
> WHen I run it in automation, I launch Word, open a document, and run
> the macro from within the addin.
>
> What gets saved appears very much like what I see just prior to the
> data appearing fully formatted as described above.
>
> If I select the table, because of the selection colors, I do see that
> data is indeed (correctly) saved in the document. Just the format is
> missing, and I see blank rows.
>
>
>
> > In any case, have you tried with a Do Events in your code?
> >
> > Or, Save it twice before closing it?
> >
>
> Thanks, I have not, so I'll try that.
>
>
> >From the mvps site, I see a suggestion for using one of the dialogs.
> DO you know if something like that is faster.
>
> Actually speed is not an issue, the 2 to 5 seconds it takes is
> perfectly fine, I just need the formatting to be complete before I save
> it.
>
> I'll try your suggestions.
>
> Thanks
>
>
>
> > --
> > Salut!
> > _______________________________________
> > Jean-Guy Marcil - Word MVP
> > jmarcilREMOVE@CAPSsympatico.caTHISTOO
> > Word MVP site: http://www.word.mvps.org
>
>

shripathikamath
Tue Apr 05 11:09:28 CDT 2005

Chuck wrote:
> If you post the code it might be easier to help?
>

Thanks, I'll give it a shot. These are the snippets. The following is
the main function.



'Method that interface with the addin to process the query and place
'the results into the document. Calls other VBA methods to do the
parsing
' and formatting of the query
Private Sub RunQuery(ByVal Qry As String)
Dim QueryData() As String
Dim start As Integer

QueryData = GetAddIn().WordQuery(g_currentServerName,
g_rawQueryString, SilentMode)
Dim i As Integer
start = 0
Application.ScreenUpdating = False
'Place the table in the report and format it
If CBool(m_includeHeadings) Then
start = 0
Else
start = 1
End If
If UBound(QueryData) > 0 Then
For i = start To UBound(QueryData)
Selection.InsertAfter (QueryData(i) & vbCr)
Next
Selection.ConvertToTable (";")
ApplyTableFormat
Beep ' <- This is audible noticeably before the formatting
completes
End If
ErrorReturn:
Application.ScreenUpdating = True
End Sub


And here is the ApplyTableFormat. The globals have been omitted

'
' Apply the selected formatting options to the table
'
Private Sub ApplyTableFormat()
Dim resultTable As Table
Set resultTable = Selection.Tables(1)

If Not resultTable Is Nothing Then
With resultTable
.AutoFormat _
Format:=AutoFormatValue, _
ApplyBorders:=CBool(UseBorder), _
ApplyShading:=CBool(UseShading), _
ApplyFont:=CBool(UseFont), _
ApplyColor:=CBool(UseColor), _
AutoFit:=CBool(UseAutoFit), _
ApplyHeadingRows:=CBool(ApplyStyleHeadingRows), _
ApplyLastRow:=CBool(ApplyStyleLastRow), _
ApplyFirstColumn:=CBool(ApplyStyleFirstColumn), _
ApplyLastColumn:=CBool(ApplyStyleLastColumn)
End With
'repeat page headings setting has no meaning if there are no
'headings to start with
If CBool(m_includeHeadings) Then
If PageHeadings Then
resultTable.Rows.Item(1).HeadingFormat = True
End If
End If
DoEvents ' <- has no effect
End If
End Sub

Chuck
Tue Apr 05 11:59:02 CDT 2005

I once ran into a somewhat similar problem in that the code seemed to run
ahead of Word's ability to process it. The following code runs in the
AutoExec of our normal.dot.

If Dir$(AUTOTEXT_PATH & "AutoText.dot") <> "" Then
'load add in
AddIns.Add _
FileName:=AUTOTEXT_PATH & _
"AutoText.dot", _
Install:=True
End If

Without the comment ('load add in) the code would execute but the add in
wouldn't be loaded yet would generate no error messages either. With the
comment the add in loads properly. I know, I know, the comment should make
no difference, but it does at least in our specific circumstances (believe me
I tested that thoroughly because I couldn't believe it myself -- it has
nothing to do with the specific normal.dot (ie its not that it's corrupt or
anything) because that happens no matter what file I tested that code in).

So even though this sounds ridiculous, perhaps you could try some "do
nothing" code after ApplyTableFormat and before the Beep just to see if that
gives Word a chance to catch up with the VBA? For instance you could assign
a variable to the contents of the last cell in the table and test its length
(eg strLastCell = len(selection.tables(1).cells.last)? Eg:

With Selection.Tables(1)
If Len(.Cell(.Rows.Count, _
.Columns.Count).Range.Text) > 2 Then
'do nothing
End If
End With

Sorry can't be of more help.

"shripathikamath@hotmail.com" wrote:

> Chuck wrote:
> > If you post the code it might be easier to help?
> >
>
> Thanks, I'll give it a shot. These are the snippets. The following is
> the main function.
>
>
>
> 'Method that interface with the addin to process the query and place
> 'the results into the document. Calls other VBA methods to do the
> parsing
> ' and formatting of the query
> Private Sub RunQuery(ByVal Qry As String)
> Dim QueryData() As String
> Dim start As Integer
>
> QueryData = GetAddIn().WordQuery(g_currentServerName,
> g_rawQueryString, SilentMode)
> Dim i As Integer
> start = 0
> Application.ScreenUpdating = False
> 'Place the table in the report and format it
> If CBool(m_includeHeadings) Then
> start = 0
> Else
> start = 1
> End If
> If UBound(QueryData) > 0 Then
> For i = start To UBound(QueryData)
> Selection.InsertAfter (QueryData(i) & vbCr)
> Next
> Selection.ConvertToTable (";")
> ApplyTableFormat
> Beep ' <- This is audible noticeably before the formatting
> completes
> End If
> ErrorReturn:
> Application.ScreenUpdating = True
> End Sub
>
>
> And here is the ApplyTableFormat. The globals have been omitted
>
> '
> ' Apply the selected formatting options to the table
> '
> Private Sub ApplyTableFormat()
> Dim resultTable As Table
> Set resultTable = Selection.Tables(1)
>
> If Not resultTable Is Nothing Then
> With resultTable
> .AutoFormat _
> Format:=AutoFormatValue, _
> ApplyBorders:=CBool(UseBorder), _
> ApplyShading:=CBool(UseShading), _
> ApplyFont:=CBool(UseFont), _
> ApplyColor:=CBool(UseColor), _
> AutoFit:=CBool(UseAutoFit), _
> ApplyHeadingRows:=CBool(ApplyStyleHeadingRows), _
> ApplyLastRow:=CBool(ApplyStyleLastRow), _
> ApplyFirstColumn:=CBool(ApplyStyleFirstColumn), _
> ApplyLastColumn:=CBool(ApplyStyleLastColumn)
> End With
> 'repeat page headings setting has no meaning if there are no
> 'headings to start with
> If CBool(m_includeHeadings) Then
> If PageHeadings Then
> resultTable.Rows.Item(1).HeadingFormat = True
> End If
> End If
> DoEvents ' <- has no effect
> End If
> End Sub
>
>

shripathikamath
Tue Apr 05 13:09:17 CDT 2005

Thanks, I'll try that

shripathikamath
Tue Apr 05 22:50:46 CDT 2005

Thanks for the suggestion, and thank you all.

Unfortunately, I still have not found a solution that works.

Basically, what I am trying to do does not sound too novel, so it bugs
me that it is not a simple fix :-)

1. Create a table with 300 to 500 rows, 5 columns
2. Format it as Colorful 1
3. Save it as an HTML file


All from automation.

The problem is that Step 2 seems to be done asynchronously, in that the
HTML file saved looks like the formatting did not complete (empirically
verified)

Yet if I put in DoEvents looping some million times after Step 2, the
formatting waits, till the looping is done.

I change Step 3 to a regular .doc file, and it appears to work just
fine.

I was sure *someone* has run across this, I am still pretty sure, just
not able to solve it...

All I need to know is if there is a way to determine when, any
formatting in progress is completed

OR

How to force a format of a table so that the call does not return till
the formatting is complete.

Thanks everyone, if you find some pointers, it'd help a lot

Chuck
Wed Apr 06 05:05:02 CDT 2005

So how about saving as a .doc file first then saving as html and then killing
the .doc file?

"shripathikamath@hotmail.com" wrote:

> Thanks for the suggestion, and thank you all.
>
> Unfortunately, I still have not found a solution that works.
>
> Basically, what I am trying to do does not sound too novel, so it bugs
> me that it is not a simple fix :-)
>
> 1. Create a table with 300 to 500 rows, 5 columns
> 2. Format it as Colorful 1
> 3. Save it as an HTML file
>
>
> All from automation.
>
> The problem is that Step 2 seems to be done asynchronously, in that the
> HTML file saved looks like the formatting did not complete (empirically
> verified)
>
> Yet if I put in DoEvents looping some million times after Step 2, the
> formatting waits, till the looping is done.
>
> I change Step 3 to a regular .doc file, and it appears to work just
> fine.
>
> I was sure *someone* has run across this, I am still pretty sure, just
> not able to solve it...
>
> All I need to know is if there is a way to determine when, any
> formatting in progress is completed
>
> OR
>
> How to force a format of a table so that the call does not return till
> the formatting is complete.
>
> Thanks everyone, if you find some pointers, it'd help a lot
>
>

shripathikamath
Wed Apr 06 13:03:20 CDT 2005

I had tried that too, but it did not work.

But finally, I found *a* solution. What I did was save the file twice,
but not in a row. I saved it after I fill the table, and again after I
format the table. The trick of saving before and after the format
seemed to help

That seems to work a 100% of the time.

Here is the complete code to reproduce the problem (need to comment
indicated line for reproducing the error), and the fix that worked for
me.

+++
Option Explicit

Sub Test()
Dim table As table

' create a new document
Dim doc As Document
Set doc = Application.Documents.Add

' create the table in the document
Application.ScreenUpdating = False
Set table = CreateTable(doc)
Application.ScreenUpdating = True

' save it as a unformatted HTML
' COMMENT THE FOLLOWING LINE OUT TO SEE IT FAIL
doc.SaveAs "c:\aaaHTML.htm", wdFormatHTML

' format it
table.AutoFormat (wdTableFormatColorful1)

' save it as a formatted HTML
doc.SaveAs "c:\aaaHTML.htm", wdFormatHTML
doc.Close True
End Sub
Function CreateTable(doc As Document) As table
Dim rows As Integer
Dim columns As Integer
Dim i As Integer, j As Integer

rows = 600 ' must be > 0
columns = 5 ' must be > 1

doc.Select
For i = 1 To rows
For j = 1 To columns - 1
Selection.InsertAfter "Cell(" & i & "," & j & ")" & ";"
Next j
Selection.InsertAfter "Cell(" & i & "," & columns & ")" & vbCr
Next i

Dim table As table

Set table = Selection.ConvertToTable(";")
Set CreateTable = table
End Function
+++


Thanks to everyone who helped