Copy Specific Text from Word To Excel

yanyan9896 · #1 10-10-2023, 04:58 AM

Hi everyone,

I have a VBA code that currently allows me to copy the selected text from Word to a designated Excel file. However, I'm struggling to modify the code to copy specific text from Word to Excel instead. I'm not sure how to write the code to achieve this.

Here's the code I have so far:

Code:

Sub CopyTextToExcel()

    Dim objExcel As Object
    Dim objWorkbook As Object
    Dim objWorksheet As Object
    Dim strText As String
    Dim rngDestination As Object
    
    ' Create a new Excel application
    Set objExcel = CreateObject("Excel.Application")
    
    ' Open an existing workbook or create a new one
    Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Desktop\Template.xlsx") ' Replace with the actual path
    
    ' Set the destination worksheet
    Set objWorksheet = objWorkbook.Worksheets("LIST") ' Replace with the actual sheet name
    
    ' Set the destination range
    Set rngDestination = objWorksheet.Range("A2") ' Replace with the actual cell range
    
    ' Get the specific text from Word
    strText = ActiveDocument.Range.Text


    ' Copy the text to Excel
    rngDestination.value = strText
    
    ' Save and close the workbook
    objWorkbook.Save
    objWorkbook.Close
    
    ' Quit Excel
    objExcel.Quit
    
    ' Clean up the objects
    Set rngDestination = Nothing
    Set objWorksheet = Nothing
    Set objWorkbook = Nothing
    Set objExcel = Nothing
    
    End With
    
End Sub

I would like to modify the code to copy specific text from Word to Excel based on certain criteria.

Here are the 100 names I want the macro to copy:
(If you would like to help provide the code, just told me which line i need to place those names is OK)

Mr. Anderson
Miss Baker
Dr. Carter
Prof. Davis
President Edwards
Secretary Foster
Mr. Grayson
Miss Harris
Dr. Ingram

Prof. Johnson
President Kelly
Secretary Lawson
Mr. Mitchell
Miss Nelson
Dr. Owens
Prof. Parker
President Quinn
Secretary Reynolds
Mr. Smith
Miss Thompson
Dr. Underwood
Prof. Walker
President Xavier
Secretary Young
Mr. Adams
Miss Bennett
Dr. Clarke
Prof. Davis
President Evans
Secretary Foster
Mr. Greene
Miss Hughes
Dr. Irwin
Prof. Jenkins
President King
Secretary Lee
Mr. Marshall
Miss Nguyen
Dr. Oliver
Prof. Patel
President Quinn
Secretary Ross
Mr. Simmons
Miss Taylor
Dr. Underwood
Prof. Vasquez
President White
Secretary Young
Mr. Allen
Miss Brooks
Dr. Campbell
Prof. Davis
President Edwards
Secretary Foster
Mr. Gray
Miss Hernandez
Dr. Ingram
Prof. Jackson
President Kelly
Secretary Lawson
Mr. Morgan
Miss Phillips
Dr. Reed
Prof. Scott
President Thompson
Secretary Turner
Mr. Wallace
Miss Adams
Dr. Barnes
Prof. Carter
President Davis
Secretary Evans
Mr. Foster
Miss Garcia
Dr. Hughes
Prof. Johnson
President King
Secretary Lewis
Mr. Mitchell
Miss Nelson
Dr. Owens
Prof. Parker
President Quinn
Secretary Reynolds
Mr. Simmons
Miss Taylor
Dr. Underwood
Prof. Vasquez
President White
Secretary Young
Mr. Allen
Miss Brooks
Dr. Campbell
Prof. Davis
President Edwards
Secretary Foster
Mr. Grayson
Miss Harris
Dr. Ingram
Prof. Johnson

For example, Font.Name = "Times New Roman", Font.Size = 14, Font.Bold = True.

Could someone please guide me on how to modify the code to achieve this? Any help or suggestions would be greatly appreciated.

Thank you in advance for your assistance!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to copy specific font text from Word to Excel?	syl3786	Word VBA	2	07-24-2023 06:26 PM
How to copy text from Word to Excel according to a list?	syl3786	Word VBA	6	04-09-2023 08:01 AM
Need to copy specific formated text from Word and paste from clipboard into a console application	Userx	Word VBA	0	10-09-2020 05:58 AM
Linking Specific text fields in PP to specific cells in an Excel table	GWRW1964	PowerPoint	0	02-26-2018 07:37 AM
copy a specific words to excel list	romanticbiro	Word VBA	12	12-03-2014 05:12 AM