Create a VBA macro to parse the provided sample Word document to Excel

[Boky]

Hello guys,

I have tried to find a solution but un succeeded so far. I need to copy the entire text from a Word file to an Excel file, Sheet1 using VBA macros and I need to follow this set of rules:

* Heading 1 needs to be UPPERCASE
* Heading 2 and 3 need to be bold and underlined.

I really appreciate any help you can provide. I'm new here with VBA so I very appreciate your help.

 

[Macropod]

Simply change the relevant Heading Style definitions in Word before copying the content.

 

[Boky]

Thanks. And I also need the VBA code to copy all the text from Word tonExcel.

 

[Macropod]

You would need to provide far more information than you have so far before anyone could answer this. For example:
• Is the code being run from Word, or from Excel?
• Are the Word document and the Excel workbook already open? If so, are both active?
• Where in the Excel workbook are the data to be pasted (i.e. which worksheet -if not the active one - and what cell address)?

A web search will turn up plenty of code for any of the above scenarios.

 

[Boky]

* The code will run from Excel.
* The Word document can be clised and the Excel is open.
* The data should be pasted in active Sheet1 in A column.

Sorry if I'm less detailed, but I'm new here and I have job interview in a 4 days. I didn't know that I will get this kind of assignment when I applied.

 

[Macropod]

You say the data should be pasted into cell A1. All the data, or is that just the starting address? Ordinarily, each Word paragraph would go into a separate row (in the same column). If it's all to go in cell A1, is that as a multi-line text string, as an embedded Word object, a picture, or something else?

 

[Boky]

Cell A1 is the starting address. First paragraph goes to A1, second to A2, third to A3...

 

[Macropod]

For example:

Sub GetWordData()
'Note: this code requires a reference to the Word object model. See under the VBE's Tools|References.
Application.ScreenUpdating = False
Const strFile As String = "Document path & filename"
Dim wdApp As New Word.Application, wdDoc As Word.Document, WkSht As Worksheet, c As Long, r As Long
Set WkSht = ActiveSheet
Set wdDoc = wdApp.Documents.Open(Filename:=strFile, AddToRecentFiles:=False, Visible:=False)
With wdDoc
  .Styles(wdStyleHeading1).Font.Allcaps = True
  .Styles(wdStyleHeading2).Font.Bold = True
  .Styles(wdStyleHeading2).Font.Underline = wdUnderlineSingle
  .Styles(wdStyleHeading3).Font.Bold = True
  .Styles(wdStyleHeading3).Font.Underline = wdUnderlineSingle
  .Range.Copy
  WkSht.Paste Destination:=WkSht.Range("A1")
  .Close SaveChanges:=False
End With
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
Application.ScreenUpdating = True
End Sub

 

[Boky]

Thanks a lot. I will try the code when I get home.

 

[Boky]

I got the error msg.

Run-time error: '5174'.
Application-defined or object-defined error

Any suggestions on how to solve this?