#1
|
|||
|
|||
Need help with VBA Macro to copy data from Word document to Excel file
Hi all,
I am new to VBA and need help with creating a macro that can perform the following tasks: 1.Pop out a box for user to browse for a file (specifically an Excel file) 2.Find specific names in a Microsoft Word document 3.Copy those names 4.Paste them in the designated Excel file Here's the code I have: Code:
Sub CopyNamesFromWordToExcel() ' Declare variables Dim wordApp As Object Dim wordDoc As Object Dim excelApp As Object Dim excelWbk As Object Dim excelWks As Object Dim nameRange As Range Dim name As String ' Create instances of Word and Excel applications Set wordApp = CreateObject("Word.Application") Set excelApp = CreateObject("Excel.Application") ' Open the Word document Set wordDoc = wordApp.Documents.Open("C:\Path\To\Your\WordDocument.docx") ' Select the range of cells containing the names Set nameRange = wordDoc.Range(Start:=wordDoc.Content.Start, _ End:=wordDoc.Content.End) ' Loop through each cell in the range For Each cell In nameRange.Cells ' Check if the cell contains a name If InStr(cell.Text, "Name: ") > 0 Then ' Extract the name from the cell text name = Mid(cell.Text, InStr(cell.Text, "Name: ") + Len("Name: "), _ InStr(cell.Text, vbCrLf) - InStr(cell.Text, "Name: ") - Len("Name: ")) ' Add the name to the Excel sheet Set excelWbk = excelApp.Workbooks.Add Set excelWks = excelWbk.Worksheets(1) excelWks.Cells(excelWks.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = name End If Next cell ' Close the Word document wordDoc.Close False ' Clean up Set wordDoc = Nothing Set wordApp = Nothing Set excelWks = Nothing Set excelWbk = Nothing Set excelApp = Nothing End Sub Thanks in advance for your help! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copy highlighted words from Word Document to designated Excel file | syl3786 | Word VBA | 2 | 07-26-2023 11:56 PM |
Copy Filtered Data to new excel file s | theexpat | Excel Programming | 0 | 02-18-2016 10:24 AM |
a macro that can copy data from copy.xls to our current excel macro.xls based on criteria: | udhaya | Excel Programming | 1 | 11-12-2015 10:12 AM |
copy data from outlook attached excel file into a local one | s_samira_21 | Outlook | 4 | 09-17-2014 11:46 PM |
How to copy automatically data from Excel file to Word file? | fuchsd | Word | 6 | 10-25-2011 05:52 AM |