CODEWITHSUNDEEP
vbaexcelms-word
ju5tin
ju5tin

Reputation: 35

Creating an Excel File from a Word File in VBA

currently I am trying to create a macro in word that when run it looks for a few "keywords" in the document (that are essentially just titles), copy the paragraph below these keywords & then create an Excel file and paste them into separate cells in this new file, obviously saving the file once done.

Currently my code is as follows:

Sub WordToExcel()

Dim ObjExcel As Object, ObjWorkBook As Object, ObjWorksheet As Object

Set ObjExcel = CreateObject("EXCEL.APPLICATION")
Set ObjWorkBook = ObjExcel.Workbooks.Open("C:\Users\john\Desktop\test.xlsx")
Set ObjWorksheet = ObjWorkBook.Worksheets("Sheet1")

With Selection.Find
    .ClearFormatting
    .Text = "Description"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
End With
Selection.Find.Execute
Selection.MoveDown Unit:=wdParagraph, Count:=1
Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdExtend

Do Until Left(Selection.Text, Len(Selection.Text) - 1) = "Tasks and Timeframe"
    Ctr = Ctr + 1
    Selection.MoveDown Unit:=wdParagraph, Count:=1
    Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdExtend
Loop

Selection.Copy
ActiveSheet.Paste
ActiveWorkbook.Save

ObjWorkBook.Save
ObjWorkBook.Close
Set ObjWorksheet = Nothing
Set ObjWorkBook = Nothing
ObjExcel.Quit
Set ObjExcel = Nothing

End Sub

and the word document that the macro is contained in is essentially just:

Description

Here is the general description of this document. This should be copied & pasted into the new excel file.

Tasks and Timeframe

Here are the tasks/the necessary timeframe. This should also be copied & pasted into the Excel file.

Upvotes: 0

Views: 3432

Answers (1)

nicolaus-hee
nicolaus-hee

Reputation: 789

Solution: This is working for me:

(1) Remove the loop Do Until ........ Loop. The code before that already selects the paragraph you want to copy.

(2) Change

ActiveSheet.Paste
ActiveWorkbook.Save

to

ObjWorksheet.Paste
ObjWorkBook.Save

Otherwise, your Word macro doesn't know which Excel sheet you are referring to.

Result: This is what the Excel file looks like after running the macro.

enter image description here

Note: I assume that down the road, you will want to put the sentences from the Word file into specific cells in Excel and not A1 as your code is doing now. To accomplish that, add ObjWorksheet.Range("B2").Select (setting the cell to the desired target cell) before ObjWorksheet.Paste.

Upvotes: 1

Related Questions