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

Boky

New Member
Joined
Oct 27, 2022
Messages
13
Platform
  1. Windows
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,677
Simply change the relevant Heading Style definitions in Word before copying the content.
 
Upvote 0

Boky

New Member
Joined
Oct 27, 2022
Messages
13
Platform
  1. Windows
Thanks. And I also need the VBA code to copy all the text from Word tonExcel.
 
Upvote 0

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,677
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.
 
Upvote 0

Boky

New Member
Joined
Oct 27, 2022
Messages
13
Platform
  1. Windows
* 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.
 
Upvote 0

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,677
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?
 
Upvote 0

Boky

New Member
Joined
Oct 27, 2022
Messages
13
Platform
  1. Windows
Cell A1 is the starting address. First paragraph goes to A1, second to A2, third to A3...
 
Upvote 0

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,677
For example:
VBA Code:
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
 
Upvote 0

Boky

New Member
Joined
Oct 27, 2022
Messages
13
Platform
  1. Windows
I got the error msg.

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


Any suggestions on how to solve this?
 
Upvote 0

Forum statistics

Threads
1,186,997
Messages
5,961,012
Members
438,514
Latest member
AngSP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top