Application.Screenupdating does not work

[francozola25]

Hello

I am having a problem with Application.Screenupdating = False does not seem to work.

When my macro runs i get a message Word is updating the field codes in the document. My macro is updating the field codes of the documents.

I have tried setting screenupdating, displayalerts, displaystatusbar = false but i still get the message and it shows the macro going to the next doc.

I have tried moving the screenupdating, displayalerts, displaystatusbar to different parts of the doc but i still get this.


Sub UpdateStoryRanges(CurrentDoc As Document)
'Loop to update the main body of the document
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayStatusBar = False

For Each oStory In CurrentDoc.StoryRanges
'Update the all the FormFields
oStory.Fields.Update
If oStory.StoryType < wdMainTextStory Then
'Loop through main body of document until all fields are updated
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
Set oStory = Nothing
End Sub

[malik641]

What is oStory and how is it declared?
Are you using office 2007 and when you run the code you are running in debug mode? (that doesn't let you turn off screen updating...at least I haven't found a way to do so for debuggin in 2007)

[francozola25]

Hey Malik thanks for your reply.

Just to confirm i am working in office 2003 and currently not running in debug mode.

oStory is a range of values for updating the formfields in a document

Please view code,

What happens is Update() is called at the beginning, it asks to input a title, the title is updated for the formfields in the current document ie. Call UpdateStoryRanges(ActiveDocument). What happens next is that Call SheetPrintOut is called which takes the current document file name, searches an excel spreadsheet for the document file name value, then offsets and creates a list called forms of the offsetted value ExFNList. These forms are then opened in word like so Set bDoc = Documents.Open("C:\forms\" & ExcelFN & ".Doc"). Once the forms are opened the formfields are then updated and the forms are printed off.

What happens though is that the screenupdating works for the excel part, it then when it gets to word for opening the forms, shows opening the forms and a message appears on the status bar, stating word is updating fields in the current document. I can't get rid of this, it is driving me mad.

Please se code as mentioned.

Sub Update()
Dim Title As String
Dim frmTitle As UserForm1
Dim oStory As Range
Application.ScreenUpdating = False
Set frmTitle = New UserForm1
With frmTitle
.Show
ActiveDocument.BuiltInDocumentProperties("Title").Value = .Title.Text
End With
Unload frmTitle
Set frmTitle = Nothing
Call UpdateStoryRanges(ActiveDocument)
Call SheetPrintOut
Application.ScreenUpdating = True
End Sub
Sub SheetPrintOut()

Dim aDoc As Document
Dim bDoc As Document
Dim oXL2 As Excel.Application
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oWB2 As Excel.Workbook
Dim oDoc As Word.Application
Dim oSheet As Excel.Worksheet
Dim c As Excel.Range
Dim FirstAddress As String
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String
Dim WordFN As String
Dim ExcelFN As String
Dim aCol As Integer
Dim ExFnList As String

Application.ScreenUpdating = False

Set oDoc = New Word.Application
oDoc.ScreenUpdating = False

'Application.StatusBar = "We are done!"
'Application.OnTime Now + TimeSerial(0, 0, 10), "ClearStatusBar"

Set aDoc = ActiveDocument
'Word document name open (aDoc) is declared as WordFn
WordFN = Left(aDoc.Name, Len(aDoc.Name) - 4)
'Open index.xls contains a match for WordFn
'Application.ScreenUpdating = False
WorkbookToWorkOn = "C:\forms\index.xls"
'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
oXL.ScreenUpdating = False
End If
On Error GoTo Err_Handler
'If you want Excel to be visible, you could add the
'line: oXL.Visible = True here; but your code will
'run faster if you don't make it visible
'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)
Set oSheet = oWB.Worksheets(1)
'Find last cell with data in column A.
RowI = oSheet.Cells(oSheet.Rows.Count, "A").End(xlUp).Row
With oSheet.Range("a1:a" & RowI)
Set c = .Find(WordFN, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Do
aCol = aCol + 1
ExcelFN = c.Offset(0, aCol).Value
ExFnList = ExFnList & ExcelFN & "|"
If ExcelFN <> "" Then
'Declare new doc as bDoc
Set bDoc = Documents.Open("C:\forms\" & ExcelFN & ".Doc")
bDoc.BuiltInDocumentProperties("Title") = _
aDoc.BuiltInDocumentProperties("Title")
'Update the Title Fields
Call UpdateStoryRanges(bDoc)
'Application.StatusBar = "Printing Form"
'Application.OnTime Now + TimeSerial(0, 0, 10), "ClearStatusBar"
bDoc.PrintOut
bDoc.Close savechanges:=wdDoNotSaveChanges
'Application.StatusBar = Left$("Printing " & ExcelFN & " for Doc " & WordFN, 300)
'Application.OnTime Now + TimeSerial(0, 0, 10), "ClearStatus"

End If
Loop Until ExcelFN = ""

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
With UserForm2
.TextBox1 = "Document " & WordFN & " has the following forms attached: " & Chr(10) & Replace(ExFnList, "|", "
.Show
End With
Else
MsgBox ("Document " + WordFN + " does not contain additional Forms")
End If
End With
If ExcelWasNotRunning Then
oXL.Quit
Else
oWB.Close
End If
'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing
'quit
Exit Sub
Err_Handler:
'MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, "Error: " _
'& Err.Number
MsgBox Err.Description, vbCritical, "Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing
oDoc.ScreenUpdating = True
End Sub
Sub UpdateStoryRanges(CurrentDoc As Document)
Application.ScreenUpdating = False
For Each oStory In CurrentDoc.StoryRanges
oStory.Fields.Update
If oStory.StoryType < wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
Set oStory = Nothing
Application.ScreenUpdating = True
End Sub

[malik641]

Hey francozola,

You should use "Option Explicit" at the top of your code. I'm finding too many variables that are not defined. These undefined variables become Variants and are not easily managable.

[malik641]

I'm not sure about the screen updating. It works for me when I test "UpdateRanges()" sub. But I have found some other issues.

You are creating a NEW instance of Word.Application here:

Set oDoc = New Word.Application
oDoc.ScreenUpdating = False

This is in SheePrintOut() sub procedure.

When you use the 'New' keyword it's like clicking Start->All Programs->Microsoft Office->Microsoft Office Word 2003

If you modified the code like this:

Set oDoc = New Word.Application
oDoc.ScreenUpdating = False
oDoc.Visible = True
You would see that there is an entirely new window. And you are not updating the oDoc document in your code...nor do you have it visible, so you wouldn't see it. Plus you never "Close" the document and "Exit" the application. It just sits in memory not doing anything except using up your resources.

Like I said in the previous post, please place "Option Explicit" at the top of the module and click Debug->Compile and you will notice a bunch of items VBA wants to argue about. Always use Option Explicit in your code. You can have it automatically inserted by clicking Tools->Options->Editor->Require Variable Declaration

If you want please attach a workbook that I can fully test your code on. The screen updating is working fine for me with my test document.

[francozola25]

Hi Malik

When you say the screen updating works fine for you, do you mean you don't get a message in the status bar saying Word is updating the fields in this document, the document will not look fully opened but the title of the document will appear at the top. Word will open stating the documents name but the document itself will not and that message of updating appears on the statusbar.

I am doing this as i wish to put in place a progress bar for when it finds the documents and updates them.

[francozola25]

Hey Malik

Please see attachment. Hopefully it will open for you as a template.

What is takes into consideration is that the is a file called index.xls on the C:Drive

and

There are form that are in c:\forms\

I want to implement a progress bar for the opening of the forms

[malik641]

franzozola,

If it's the StatusBar that is not working, then use:

application.StatusBar = "Please wait..."
' or to reset it
application.StatusBar = ""

Try this at different points in the code. Probably right after:
oStory.Fields.Update


But I can't test this code because I don't have everything you do. I would like to test the code for you, but I'm going to need everything that is necessary. I placed Normal.dot in the C drive and I made a folder called 'forms' in the C directory with a blank workbook called "index.xls" and I still had a problem running the code. I receive the error:


Doc Normal cannot be found. Forms will have to be manually inserted and printed.

And I still don't know why you are creating a whole new word application when it doesn't seem to do anything.

[francozola25]

Hey Malik

No the status bar is working but i will try knock it off.

You see you have a document opened named May2008.doc
What happens is you call the macro Update
It will then conduct a find on the following path C:\index.xls
A search is done on column C for May2008, the document that is open at that given time.
Column D,E,F etc are the names of forms say D5- travel expenses, training expenses, end of quarterly expenses etc.

These are also the names of work docs found in c:\forms\travel expenses.doc, c:\forms\training expenses.

When the macro is called for May2008, May2008 is found in say C5 and offsetting of cells will take place. Everytime it offsets it opens the cell value say c:\forms\travel expenses ; if travel expenses is in the cell. It will then print off these and update any of the fields.

You might need to create a Word doc called May2008, then go into C:\index.xls and put say May2008 in C5, you will also have to create word docs called travel expenses.doc, training expenses.doc in c:\forms. Then place the names of travel expenses in say D5 and training expenses in F5.

The macro should open these and print off.