ecalid
03-05-2024, 08:14 AM
Good afternoon all,
I am in the process of creating a macro which looks for a prescribed piece of text in a browsed word document; I have managed to get the macro to locate the text but I need it to move backwards by 2 cells, essentially cut and paste.
It's almost like Excel doesn't recognise that there is a table in the word document and just defaults to the first character of the document.
ChatGTP isn't helping very much so I am wondering if anybody has any experience with this?
Private Sub BrowseButton_Click()
Dim selectedRow As Long
Dim searchValue As String
Dim filePath As String
Dim wordApp As Object
Dim wordDoc As Object
Dim foundRange As Object
Dim insertionText As String
' Get the selected row and search value
selectedRow = ActiveCell.Row
searchValue = ActiveSheet.Cells(selectedRow, 4).Value
' Browse for Word document
With Application.fileDialog(msoFileDialogFilePicker)
.Title = "Select Word Document"
.Filters.Add "Word Documents", "*.docx", 1
If .Show = -1 Then
filePath = .SelectedItems(1)
Else
Exit Sub ' User canceled
End If
End With
' Open Word document
Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Open(filePath)
' Search for the value in the Word document
If wordDoc.Content.Find.Execute(FindText:=searchValue) Then
' Get the found range
Set foundRange = wordDoc.Content
' Copy the found value
foundRange.Copy
Dim startPosition As Long
startPosition = foundRange.Start
' Set the selection to the start position
wordApp.Selection.SetRange Start:=startPosition, End:=startPosition
' Simulate pressing "Tab" back twice
Application.SendKeys "{TAB 2}"
' Add onto any existing text
insertionText = ActiveSheet.Cells(selectedRow, 4).Value
wordApp.Selection.TypeText insertionText
' Display the result in a MsgBox
MsgBox "Value found in Word document '" & filePath & "' and inserted at location: " & wordApp.Selection.Start & " - " & wordApp.Selection.End
Else
MsgBox "Value not found in the Word document '" & filePath & "'."
End If
End Sub
Could anybody please help me with this?
Many thanks in advance.
I am in the process of creating a macro which looks for a prescribed piece of text in a browsed word document; I have managed to get the macro to locate the text but I need it to move backwards by 2 cells, essentially cut and paste.
It's almost like Excel doesn't recognise that there is a table in the word document and just defaults to the first character of the document.
ChatGTP isn't helping very much so I am wondering if anybody has any experience with this?
Private Sub BrowseButton_Click()
Dim selectedRow As Long
Dim searchValue As String
Dim filePath As String
Dim wordApp As Object
Dim wordDoc As Object
Dim foundRange As Object
Dim insertionText As String
' Get the selected row and search value
selectedRow = ActiveCell.Row
searchValue = ActiveSheet.Cells(selectedRow, 4).Value
' Browse for Word document
With Application.fileDialog(msoFileDialogFilePicker)
.Title = "Select Word Document"
.Filters.Add "Word Documents", "*.docx", 1
If .Show = -1 Then
filePath = .SelectedItems(1)
Else
Exit Sub ' User canceled
End If
End With
' Open Word document
Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Open(filePath)
' Search for the value in the Word document
If wordDoc.Content.Find.Execute(FindText:=searchValue) Then
' Get the found range
Set foundRange = wordDoc.Content
' Copy the found value
foundRange.Copy
Dim startPosition As Long
startPosition = foundRange.Start
' Set the selection to the start position
wordApp.Selection.SetRange Start:=startPosition, End:=startPosition
' Simulate pressing "Tab" back twice
Application.SendKeys "{TAB 2}"
' Add onto any existing text
insertionText = ActiveSheet.Cells(selectedRow, 4).Value
wordApp.Selection.TypeText insertionText
' Display the result in a MsgBox
MsgBox "Value found in Word document '" & filePath & "' and inserted at location: " & wordApp.Selection.Start & " - " & wordApp.Selection.End
Else
MsgBox "Value not found in the Word document '" & filePath & "'."
End If
End Sub
Could anybody please help me with this?
Many thanks in advance.