Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 12-21-2022, 07:16 PM
John Brito John Brito is offline Please help where to modify the macro Windows 7 64bit Please help where to modify the macro Office 2007
Novice
Please help where to modify the macro
 
Join Date: Dec 2022
Posts: 6
John Brito is on a distinguished road
Default Please help where to modify the macro

Hi All,



I have applied the below macro in a word document.

The macro should extract all the name of the person into a Excel file, however, it shows Run-time error '5941', not sure how to resolve this, May I request you to check and provide help on this please.

The output in the Excel file would be

AAA
BBB
DDD
XXX
ZZZ
KKK
RRR


I have attached the document herewith

Sub CopyNamesToExcel()
Dim oTable As Table
Dim oRng As Range
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim xlCell As Object
Dim i As Integer, j As Integer
Dim sName As String
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Sheets(1)
xlApp.Visible = True
'Set xlCell = xlSheet.Range("A1")
'xlCell.value = "Name"
'j = 2
j = 1
For Each oTable In ActiveDocument.Tables
Set oRng = oTable.Cell(1, 2).Range
oRng.End = oRng.End - 1
If InStr(1, oRng.Text, "NAMES OF PERSON") > 0 Then
sName = Replace(oRng.Text, "NAMES OF PERSON", "")
sName = Replace(sName, Chr(13), "")
Set xlCell = xlSheet.Range("A" & j)
xlCell.value = Trim(sName)
j = j + 1
End If
Next oTable
End Sub
Attached Files
File Type: docx Source.docx (15.5 KB, 4 views)
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Modify macro to move only Blue font footnotes into the Text John 4 Word VBA 11 09-01-2020 03:49 PM
Please help where to modify the macro Modify macro to only add bullets to highlighted text if it exists. 14spar15 Word VBA 2 10-30-2018 08:27 PM
Please help where to modify the macro Modify Macro to Sort Last Four Characters Phil H Excel Programming 11 06-12-2018 04:05 AM
Modify recorded macro to run until end of document peter961 Word VBA 1 09-04-2017 02:50 PM
Please help where to modify the macro how modify a macro code tarikov2006 Excel Programming 1 11-18-2016 04:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:42 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft