Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Normal.dot Error
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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





Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Normal distribution Alexandra Lopes Excel Worksheet Functions 2 December 4th 08 02:36 PM
Normal.dot Shakeel Ahmad Excel Programming 2 July 16th 07 03:50 PM
normal.dot Ron New Users to Excel 4 July 13th 06 06:48 PM
Normal Distribution littlecharva Excel Programming 3 April 2nd 04 04:39 AM
Log Normal Distribution ExcelMonkey[_47_] Excel Programming 3 January 30th 04 04:44 AM


All times are GMT +1. The time now is 09:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement