PDA

View Full Version : [SOLVED] Close Word UserForm from Excel



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?

Aflatoon
10-26-2015, 02:26 AM
You could try adding:

objWord.AutomationSecurity = msoAutomationSecurityForceDisable
after the CreateObject line, and then reset it when you're finished by adding:

objWord.AutomationSecurity = msoAutomationSecurityByUI

snb
10-26-2015, 03:15 AM
If the opening of the userform is residing in the document_open event you can use documents.add: (that only triggers the document_new event)


with createobject("word.application")
for j=1 to 10
with .documents.add("G:\OF\document" & j & ".docm",,,false)
' your code
.close 0
end with
next
end with

white_flag
10-26-2015, 04:45 AM
@Aflatoon - thank you! it is working.
@snb - Your code is not working (code inside of word file is protected by an password, so I do not know what is the trigger).. when I run your code the userform is coming up.

Thank you both that you have lost time to solve this problem!!