white_flag
10-26-2015, 12:58 AM
I have VBA that is looping from some directory, opens Word files, copies some text, puts the copied text into Excel, and then closes Word. The code works but I have some files that inside have some UserForms. When that type of document starts a pop up UserForm opens. Until I close that form the code is waiting for closure. So I am trying (without success) to close that pop up UserForm. My code:
Sub copy_from_word_to_excel(ByVal aFilename As String)
Dim objWord As Object
Dim objDoc As Object
Dim i As Integer
Dim objLoop As Object
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open(aFilename, ReadOnly:=False)
objWord.Visible = False
For Each objLoop In objDoc.VBA.UserForms
If TypeOf objLoop Is UserForm Then Unload objLoop
Next objLoop
objDoc.ReadOnlyRecommended = False
objDoc.RemovePersonalInformation = True
j = j + 1
ThisWorkbook.ActiveSheet.Cells(j, 1).Value = aFilename
ThisWorkbook.ActiveSheet.Hyperlinks.Add Anchor:=Cells(j, 1), Address:=aFilename, TextToDisplay:=aFilename
ThisWorkbook.ActiveSheet.Cells(j, 2).Value = objDoc.Paragraphs(1).Range.Text
ThisWorkbook.ActiveSheet.Cells(j, 3).Value = objDoc.Paragraphs(2).Range.Text
ThisWorkbook.ActiveSheet.Cells(j, 4).Value = objDoc.Paragraphs(32).Range.Text
ThisWorkbook.ActiveSheet.Cells(j, 5).Value = objDoc.Paragraphs(33).Range.Text
ThisWorkbook.ActiveSheet.Cells(j, 6).Value = objDoc.Paragraphs(34).Range.Text
objDoc.Close SaveChanges:=False
Set objDoc = Nothing
objWord.Quit
Set objWord = Nothing
End Sub
Code is stopping and wait to close the word user form. How can the code ignore this word userform or to close the userform?
Sub copy_from_word_to_excel(ByVal aFilename As String)
Dim objWord As Object
Dim objDoc As Object
Dim i As Integer
Dim objLoop As Object
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open(aFilename, ReadOnly:=False)
objWord.Visible = False
For Each objLoop In objDoc.VBA.UserForms
If TypeOf objLoop Is UserForm Then Unload objLoop
Next objLoop
objDoc.ReadOnlyRecommended = False
objDoc.RemovePersonalInformation = True
j = j + 1
ThisWorkbook.ActiveSheet.Cells(j, 1).Value = aFilename
ThisWorkbook.ActiveSheet.Hyperlinks.Add Anchor:=Cells(j, 1), Address:=aFilename, TextToDisplay:=aFilename
ThisWorkbook.ActiveSheet.Cells(j, 2).Value = objDoc.Paragraphs(1).Range.Text
ThisWorkbook.ActiveSheet.Cells(j, 3).Value = objDoc.Paragraphs(2).Range.Text
ThisWorkbook.ActiveSheet.Cells(j, 4).Value = objDoc.Paragraphs(32).Range.Text
ThisWorkbook.ActiveSheet.Cells(j, 5).Value = objDoc.Paragraphs(33).Range.Text
ThisWorkbook.ActiveSheet.Cells(j, 6).Value = objDoc.Paragraphs(34).Range.Text
objDoc.Close SaveChanges:=False
Set objDoc = Nothing
objWord.Quit
Set objWord = Nothing
End Sub
Code is stopping and wait to close the word user form. How can the code ignore this word userform or to close the userform?