Open Word doc in Excel and save as text

[Viper147]

Hi all,

As a noob with VBA I am trying to expand on my newly found skills, and recently came across an exercise where I had to save a Word document as text. The text file then has to be opened, the contents copied and pasted in an Excel sheet. Whilst I got the last part of the code right (in a separate module), I have been struggling with the first part of opening the Word document and saving it as text. I copied code from a previous post related to the same subject matter and amended it to my needs, but am still stuck. I get a "Type mismatch" error on the red line of code below. Any assistance with this would be greatly appreciated.

Sub CreateText()
Dim psWorkbookCurrentWorkingDirectory As String
Dim psNextFullChecklistFileName As String
Dim psNextChecklistFileName As String
Dim wdApplication As Object
[COLOR=#ff0000]Set wdDocument = New Workbook.Document[/COLOR]
 
    On Error Resume Next
    Set wdApplication = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
                     Set wdApplication = CreateObject("Word.Application")
    End If
    On Error GoTo 0
 
    Set wdDocument = wdApplication.Documents.Open(Range("Web_folder") & Range("Query_file") & ".docx")
 
    wdApplication.Visible = True
 
    wdDocument.SaveAs Filename:=Range("Quote_folder") & Range("Quote_file") & ".txt", _
                      FileFormat:=wdFormatText, LockComments:=False, Password:="", _
                      AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
                      EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
                      SaveAsAOCELetter:=False, Encoding:=1252, InsertLineBreaks:=False, _
                      AllowSubstitutions:=False, LineEnding:=wdCRLF
 
    wdDocument.Close
 
    wdApplication.Quit
 
    Set wdApplication = Nothing
    Set wdDocument = Nothing
 
End Sub

 

[Macropod]

You should be using:
Dim wdDocument As Object
That said, if all you want to do is harvest the text from a document for insertion into Excel, there is no need save the document as a text file beforehand - something as simple as a copy/paste or insertion of the document's text directly into Excel will usually suffice.

 

[Viper147]

Hi Paul,
Thank you for your prompt response. I amended the code as you suggested but get a "Type mismatch" error on the same line of code.
I initially tried the path of copying the text directly from Word but struggled to get it in the right format.

 

[Viper147]

Found the code that I initially used to copy directly from Word (see below). The last error I got was "User-defined type not defined" on the red line of code below. That was when I decided to rather try saving the Word as text and opening the text file from Excel. If it will be easier to fix the code below then that will obviously be the shorter route. Any ideas?

Sub wordopen()
[COLOR=#ff0000]Dim Word As New Word.Application[/COLOR]
Dim WordDoc As New Word.Document
Dim Doc As String
Dim Fname2 As String
Dim MyBook As Workbook
Set MyBook = ActiveWorkbook
Doc = Range("Web_folder") & Range("Query_file") & ".docx"
Set WordDoc = Word.Documents.Open(Doc)
Word.Selection.WholeStory
Word.Selection.Copy
MyBook.Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues
WordDoc.Close
Word.Quit
End Sub

 

[Macropod]

I amended the code as you suggested but get a "Type mismatch" error on the same line of code.

Corrected.

 

[Viper147]

Hi Paul, amended the first block of code as you suggested but still getting the same error.
All things considered I would still rather have the second block of code as it is the easiest solution, as you also indicated. Any solutions on how to get this to work?
Thanks for your assistance.

 

[Macropod]

To use the code in post 4, you need to set a VBA reference to Word. In the VBE, that's done via Tools|References and scrolling down till you find the Word object library and selecting it. You also need to change:
Dim WordDoc As New Word.Document
to:
Dim WordDoc As Word.Document
you should also replace:
Word.Selection.WholeStory
Word.Selection.Copy
with:
WordDoc.Range.Copy

 

[Viper147]

Hi Paul,
Made the changes you suggested but I am still getting a

"User-defined type not defined" error on the same red line of code as mentioned in post 4.​

 

[Macropod]

Try changing your Word references to WordApp. For example:
Dim WordApp As New Word.Application
…
Set WordDoc = WordApp.Documents.Open(Doc)

 

[Viper147]

Tried the amendments but I still get the same error. Below is the code as it is now.

Sub Wordopen()
Dim WordApp As New Word.Application
Dim WordDoc As Word.document
Dim Doc As String
Dim Fname2 As String
Dim MyBook As Workbook
Set MyBook = ActiveWorkbook
Doc = Range("Web_folder") & Range("Query_file") & ".docx"
Set WordDoc = WordApp.Documents.Open(Doc)
WordDoc.Range.Copy
MyBook.Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues
WordDoc.Close
WordApp.Quit
End Sub