Thread: Copy Specific Text from Word To Excel
View Single Post
 
Old 10-10-2023, 04:58 AM
yanyan9896 yanyan9896 is offline Windows 10 Office 2019
Novice
  
Join Date: Oct 2023
Posts: 13
yanyan9896 is on a distinguished road
Default Copy Specific Text from Word To Excel
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!
Reply With Quote