prefect
01-12-2016, 02:30 AM
Hi there,
I am using Word and Excel 2013 and have the following problem.
I have a large Word file consisting of news articles about exchange-listed companies. I need to extract key data (date of article and company tickers) to Excel. I have a macro that exports sentences from MS Word that contain a certain keyword to a separate Excel file, but I need to export sentences that contain not one possible keyword, but one of several possible keywords, e.g. "January" and/or "NYSE" and/or "NASDAQ" etc.
Here's the code for exporting sentences with one keyword (in this case "shall"), courtesy of lucas from this forum. How do I update this code to export sentences that contain not one, but one of several possible keywords? I.e. I need the macro to search for more than one possible keyword for exporting the sentence to Excel.
Option Explicit
Sub FindWordCopySentence()
Dim appExcel As Object
Dim objSheet As Object
Dim aRange As Range
Dim intRowCount As Integer
intRowCount = 1
Set aRange = ActiveDocument.Range
With aRange.Find
Do
.Text = "shall" ' the word to look for
.Execute
If .Found Then
aRange.Expand Unit:=wdSentence
aRange.Copy
aRange.Collapse wdCollapseEnd
If objSheet Is Nothing Then
Set appExcel = CreateObject("Excel.Application")
'Change the file path to match the location of your test.xls
Set objSheet = appExcel.workbooks.Open("C:\temp\test.xls").Sheets("Sheet1")
intRowCount = 1
End If
objSheet.Cells(intRowCount, 1).Select
objSheet.Paste
intRowCount = intRowCount + 1
End If
Loop While .Found
End With
If Not objSheet Is Nothing Then
appExcel.workbooks(1).Close True
appExcel.Quit
Set objSheet = Nothing
Set appExcel = Nothing
End If
Set aRange = Nothing
End Sub
I have tried making changes to this code so far nothing has worked. Any help would be appreciated.
Best regards,
Prefect
I am using Word and Excel 2013 and have the following problem.
I have a large Word file consisting of news articles about exchange-listed companies. I need to extract key data (date of article and company tickers) to Excel. I have a macro that exports sentences from MS Word that contain a certain keyword to a separate Excel file, but I need to export sentences that contain not one possible keyword, but one of several possible keywords, e.g. "January" and/or "NYSE" and/or "NASDAQ" etc.
Here's the code for exporting sentences with one keyword (in this case "shall"), courtesy of lucas from this forum. How do I update this code to export sentences that contain not one, but one of several possible keywords? I.e. I need the macro to search for more than one possible keyword for exporting the sentence to Excel.
Option Explicit
Sub FindWordCopySentence()
Dim appExcel As Object
Dim objSheet As Object
Dim aRange As Range
Dim intRowCount As Integer
intRowCount = 1
Set aRange = ActiveDocument.Range
With aRange.Find
Do
.Text = "shall" ' the word to look for
.Execute
If .Found Then
aRange.Expand Unit:=wdSentence
aRange.Copy
aRange.Collapse wdCollapseEnd
If objSheet Is Nothing Then
Set appExcel = CreateObject("Excel.Application")
'Change the file path to match the location of your test.xls
Set objSheet = appExcel.workbooks.Open("C:\temp\test.xls").Sheets("Sheet1")
intRowCount = 1
End If
objSheet.Cells(intRowCount, 1).Select
objSheet.Paste
intRowCount = intRowCount + 1
End If
Loop While .Found
End With
If Not objSheet Is Nothing Then
appExcel.workbooks(1).Close True
appExcel.Quit
Set objSheet = Nothing
Set appExcel = Nothing
End If
Set aRange = Nothing
End Sub
I have tried making changes to this code so far nothing has worked. Any help would be appreciated.
Best regards,
Prefect