ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Normal.dot Error (https://www.excelbanter.com/excel-programming/426724-normal-dot-error.html)

John
Normal.dot Error
 
Not sure if this is the right forum for this, so appologies in advance.

I have built and Excel spreadsheet that opens up several Word.doc's and
extracts information stored in various variables in the document. However,
when the code gets to the point where it needs to close the Word application
I get an error saying it can not close Word because Normal.dot is still using
it. When I clear the error I get another error asking if I want to save
changes to the global template.

Any ideas? Using Excel/Word 2003, see code below

Sub CommandCheck()

Dim Counter As Long
Dim wdApp As Word.Application, wdDoc As Word.Document
Dim Dest As Workbook
Dim Source As Word.Document
Dim nextFile As Variant
Sheets("Sheet1").Select
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))

Const MyDir As String = "C:\Documents and Settings\KOWCHAJD\My
Documents\Reports\Accountability Model\Templates\"
Application.ScreenUpdating = False
Application.EnableEvents = False
NewFile = True
nextFile = Dir(MyDir & "*.doc")
Set wdApp = CreateObject("Word.Application")
'wdApp.Visible = True
Do While nextFile < ""
Set wdDoc = wdApp.Documents.Open(MyDir & nextFile)
If NewFile Then
For Each A In RngColA
If (A & ".doc") = nextFile Then
Range("C" & A.Row) =
wdApp.Documents(nextFile).Variables("TextBox1Text" ).Value
Range("C" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("TextBox2Text" ).Value
Range("C" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("TextBox3Text" ).Value
Range("C" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("TextBox4Text" ).Value
Range("C" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("TextBox5Text" ).Value
Range("C" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("TextBox6Text" ).Value
Range("C" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("TextBox7Text" ).Value
Range("C" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("TextBox8Text" ).Value
Range("D" & A.Row) =
wdApp.Documents(nextFile).Variables("Green1BackCol or").Value
Range("D" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("Green2BackCol or").Value
Range("D" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("Green3BackCol or").Value
Range("D" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("Green4BackCol or").Value
Range("D" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("Green5BackCol or").Value
Range("D" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("Green6BackCol or").Value
Range("D" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("Green7BackCol or").Value
Range("D" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("Green8BackCol or").Value
Range("E" & A.Row) =
wdApp.Documents(nextFile).Variables("Red1BackColor ").Value
Range("E" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("Red2BackColor ").Value
Range("E" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("Red3BackColor ").Value
Range("E" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("Red4BackColor ").Value
Range("E" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("Red5BackColor ").Value
Range("E" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("Red6BackColor ").Value
Range("E" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("Red7BackColor ").Value
Range("E" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("Red8BackColor ").Value
End If
Next A
End If
wdDoc.Close
On Error Resume Next
wdApp.Quit
nextFile = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Done"

End Sub

Barb Reinhardt
Normal.dot Error
 
I'd try

WdApp.DisplayAlerts = FALSE 'before the known alerts are displayed and
WdApp.DisplayAlerts = TRUE 'after the alerts

Be aware that disabling alerts can allow you to overwrite files you don't
want to, etc., so use them judiciously.

HTH,
Barb Reinhardt


"John" wrote:

Not sure if this is the right forum for this, so appologies in advance.

I have built and Excel spreadsheet that opens up several Word.doc's and
extracts information stored in various variables in the document. However,
when the code gets to the point where it needs to close the Word application
I get an error saying it can not close Word because Normal.dot is still using
it. When I clear the error I get another error asking if I want to save
changes to the global template.

Any ideas? Using Excel/Word 2003, see code below

Sub CommandCheck()

Dim Counter As Long
Dim wdApp As Word.Application, wdDoc As Word.Document
Dim Dest As Workbook
Dim Source As Word.Document
Dim nextFile As Variant
Sheets("Sheet1").Select
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))

Const MyDir As String = "C:\Documents and Settings\KOWCHAJD\My
Documents\Reports\Accountability Model\Templates\"
Application.ScreenUpdating = False
Application.EnableEvents = False
NewFile = True
nextFile = Dir(MyDir & "*.doc")
Set wdApp = CreateObject("Word.Application")
'wdApp.Visible = True
Do While nextFile < ""
Set wdDoc = wdApp.Documents.Open(MyDir & nextFile)
If NewFile Then
For Each A In RngColA
If (A & ".doc") = nextFile Then
Range("C" & A.Row) =
wdApp.Documents(nextFile).Variables("TextBox1Text" ).Value
Range("C" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("TextBox2Text" ).Value
Range("C" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("TextBox3Text" ).Value
Range("C" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("TextBox4Text" ).Value
Range("C" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("TextBox5Text" ).Value
Range("C" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("TextBox6Text" ).Value
Range("C" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("TextBox7Text" ).Value
Range("C" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("TextBox8Text" ).Value
Range("D" & A.Row) =
wdApp.Documents(nextFile).Variables("Green1BackCol or").Value
Range("D" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("Green2BackCol or").Value
Range("D" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("Green3BackCol or").Value
Range("D" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("Green4BackCol or").Value
Range("D" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("Green5BackCol or").Value
Range("D" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("Green6BackCol or").Value
Range("D" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("Green7BackCol or").Value
Range("D" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("Green8BackCol or").Value
Range("E" & A.Row) =
wdApp.Documents(nextFile).Variables("Red1BackColor ").Value
Range("E" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("Red2BackColor ").Value
Range("E" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("Red3BackColor ").Value
Range("E" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("Red4BackColor ").Value
Range("E" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("Red5BackColor ").Value
Range("E" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("Red6BackColor ").Value
Range("E" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("Red7BackColor ").Value
Range("E" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("Red8BackColor ").Value
End If
Next A
End If
wdDoc.Close
On Error Resume Next
wdApp.Quit
nextFile = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Done"

End Sub

John
Normal.dot Error
 
Sorry...didn't work

"Barb Reinhardt" wrote:

I'd try

WdApp.DisplayAlerts = FALSE 'before the known alerts are displayed and
WdApp.DisplayAlerts = TRUE 'after the alerts

Be aware that disabling alerts can allow you to overwrite files you don't
want to, etc., so use them judiciously.

HTH,
Barb Reinhardt


"John" wrote:

Not sure if this is the right forum for this, so appologies in advance.

I have built and Excel spreadsheet that opens up several Word.doc's and
extracts information stored in various variables in the document. However,
when the code gets to the point where it needs to close the Word application
I get an error saying it can not close Word because Normal.dot is still using
it. When I clear the error I get another error asking if I want to save
changes to the global template.

Any ideas? Using Excel/Word 2003, see code below

Sub CommandCheck()

Dim Counter As Long
Dim wdApp As Word.Application, wdDoc As Word.Document
Dim Dest As Workbook
Dim Source As Word.Document
Dim nextFile As Variant
Sheets("Sheet1").Select
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))

Const MyDir As String = "C:\Documents and Settings\KOWCHAJD\My
Documents\Reports\Accountability Model\Templates\"
Application.ScreenUpdating = False
Application.EnableEvents = False
NewFile = True
nextFile = Dir(MyDir & "*.doc")
Set wdApp = CreateObject("Word.Application")
'wdApp.Visible = True
Do While nextFile < ""
Set wdDoc = wdApp.Documents.Open(MyDir & nextFile)
If NewFile Then
For Each A In RngColA
If (A & ".doc") = nextFile Then
Range("C" & A.Row) =
wdApp.Documents(nextFile).Variables("TextBox1Text" ).Value
Range("C" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("TextBox2Text" ).Value
Range("C" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("TextBox3Text" ).Value
Range("C" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("TextBox4Text" ).Value
Range("C" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("TextBox5Text" ).Value
Range("C" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("TextBox6Text" ).Value
Range("C" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("TextBox7Text" ).Value
Range("C" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("TextBox8Text" ).Value
Range("D" & A.Row) =
wdApp.Documents(nextFile).Variables("Green1BackCol or").Value
Range("D" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("Green2BackCol or").Value
Range("D" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("Green3BackCol or").Value
Range("D" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("Green4BackCol or").Value
Range("D" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("Green5BackCol or").Value
Range("D" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("Green6BackCol or").Value
Range("D" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("Green7BackCol or").Value
Range("D" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("Green8BackCol or").Value
Range("E" & A.Row) =
wdApp.Documents(nextFile).Variables("Red1BackColor ").Value
Range("E" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("Red2BackColor ").Value
Range("E" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("Red3BackColor ").Value
Range("E" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("Red4BackColor ").Value
Range("E" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("Red5BackColor ").Value
Range("E" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("Red6BackColor ").Value
Range("E" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("Red7BackColor ").Value
Range("E" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("Red8BackColor ").Value
End If
Next A
End If
wdDoc.Close
On Error Resume Next
wdApp.Quit
nextFile = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Done"

End Sub

John
Normal.dot Error
 
Here is the exact error:

This file or application is in use by another application or user
C:\...\Normal.dot

If I clear through the error I get asked if I want to save changes to the
global template.

"Barb Reinhardt" wrote:

I'd try

WdApp.DisplayAlerts = FALSE 'before the known alerts are displayed and
WdApp.DisplayAlerts = TRUE 'after the alerts

Be aware that disabling alerts can allow you to overwrite files you don't
want to, etc., so use them judiciously.

HTH,
Barb Reinhardt


"John" wrote:

Not sure if this is the right forum for this, so appologies in advance.

I have built and Excel spreadsheet that opens up several Word.doc's and
extracts information stored in various variables in the document. However,
when the code gets to the point where it needs to close the Word application
I get an error saying it can not close Word because Normal.dot is still using
it. When I clear the error I get another error asking if I want to save
changes to the global template.

Any ideas? Using Excel/Word 2003, see code below

Sub CommandCheck()

Dim Counter As Long
Dim wdApp As Word.Application, wdDoc As Word.Document
Dim Dest As Workbook
Dim Source As Word.Document
Dim nextFile As Variant
Sheets("Sheet1").Select
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))

Const MyDir As String = "C:\Documents and Settings\KOWCHAJD\My
Documents\Reports\Accountability Model\Templates\"
Application.ScreenUpdating = False
Application.EnableEvents = False
NewFile = True
nextFile = Dir(MyDir & "*.doc")
Set wdApp = CreateObject("Word.Application")
'wdApp.Visible = True
Do While nextFile < ""
Set wdDoc = wdApp.Documents.Open(MyDir & nextFile)
If NewFile Then
For Each A In RngColA
If (A & ".doc") = nextFile Then
Range("C" & A.Row) =
wdApp.Documents(nextFile).Variables("TextBox1Text" ).Value
Range("C" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("TextBox2Text" ).Value
Range("C" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("TextBox3Text" ).Value
Range("C" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("TextBox4Text" ).Value
Range("C" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("TextBox5Text" ).Value
Range("C" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("TextBox6Text" ).Value
Range("C" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("TextBox7Text" ).Value
Range("C" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("TextBox8Text" ).Value
Range("D" & A.Row) =
wdApp.Documents(nextFile).Variables("Green1BackCol or").Value
Range("D" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("Green2BackCol or").Value
Range("D" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("Green3BackCol or").Value
Range("D" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("Green4BackCol or").Value
Range("D" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("Green5BackCol or").Value
Range("D" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("Green6BackCol or").Value
Range("D" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("Green7BackCol or").Value
Range("D" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("Green8BackCol or").Value
Range("E" & A.Row) =
wdApp.Documents(nextFile).Variables("Red1BackColor ").Value
Range("E" & A.Row).Offset(1, 0) =
wdApp.Documents(nextFile).Variables("Red2BackColor ").Value
Range("E" & A.Row).Offset(2, 0) =
wdApp.Documents(nextFile).Variables("Red3BackColor ").Value
Range("E" & A.Row).Offset(3, 0) =
wdApp.Documents(nextFile).Variables("Red4BackColor ").Value
Range("E" & A.Row).Offset(4, 0) =
wdApp.Documents(nextFile).Variables("Red5BackColor ").Value
Range("E" & A.Row).Offset(5, 0) =
wdApp.Documents(nextFile).Variables("Red6BackColor ").Value
Range("E" & A.Row).Offset(6, 0) =
wdApp.Documents(nextFile).Variables("Red7BackColor ").Value
Range("E" & A.Row).Offset(7, 0) =
wdApp.Documents(nextFile).Variables("Red8BackColor ").Value
End If
Next A
End If
wdDoc.Close
On Error Resume Next
wdApp.Quit
nextFile = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Done"

End Sub


All times are GMT +1. The time now is 05:30 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com