Is it possible to import Word documents into Power Query?

[olivierhbh]

Hello all,

Do you know if there is a way to import word documents into Power Query?

I could save them as HTML and then import them but that would require a manual step for each of them so I'm looking for a different approach.

Thanks,
Olivier.

 

[Matt Allington]

I don't believe this is possible without doing the manual step you mention. You could write some VBA to first convert the doc, but that would not be a trivial task.

 

[Macropod]

The VBA code to save a document in HTML format is indeed trivial:

Sub Demo()
With ActiveDocument
  .SaveAs FileName:=Split(.FullName, ".doc")(0) & ".htm", FileFormat:=wdFormatHTML, AddToRecentFiles:=False
End With
End Sub

As I don't use Power Query, though, I don't know what automation tools it provides such that it could both automate Word to open & convert the document, then open the resulting html file. If it give access to the standard Excel VBA model, even that isn't especially involved.

 

[Matt Allington]

Putting the vba in the word doc is trivial, but presumably more work than just saving the word file as html. Adding vba into the Excel workbook and making the process of selecting the required doc automaticly without adding an additional step (requirement in the op) is a lot more work.

 

[Macropod]

The corresponding Excel code, assuming a known filename, would be:

Dim wrdApp As New Word.Application, wrdDoc As Word.document, StrFlNm As String
StrFlNm = "C:\" & Environ("UserName") & "Documents\Some Document"
Set wrdDoc = wrdApp.Documents.Open(Filename:=StrFlNm & ".doc", AddToRecentFiles:=False)
With wrdDoc
  .SaveAs Filename:=StrFlNm & ".htm", FileFormat:=wdFormatHTML, AddToRecentFiles:=False
  .Close False
End With
wrdApp.Quit
Set wrdDoc = Nothing: Set wrdApp = Nothing

of course, you could also pass StrFlNm to the code as a parameter or have the user choose the file with a filepicker. None of this is exactly a challenging task.

You can then import the html file (StrFlNm & ".htm") into Power Query.

PS: Early binding assumed (i.e. an Excel VBA reference to Word is required).

 

[olivierhbh]

Hello,

Thanks for your answers, indeed maybe I could have a VBA script that would convert all files from a folder into Html files, I'll look into it and will let you know.

Thanks again!
Olivier.