Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #2  
Old 01-29-2021, 01:03 AM
gmayor's Avatar
gmayor gmayor is offline Copy words from word to excel Windows 10 Copy words from word to excel Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,143
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

The following should work. Create a workbook with Sheet1 having two columns and a header row. Change the path and workbook name as appropriate.
Code:
Option Explicit
'Graham Mayor - https://www.gmayor.com - Last updated - 29 Jan 2021 
Const strWB As String = "C:\Path\IDLog.xlsx"    'must exist - change as appropriate
Const strSheet As String = "Sheet1"    'Two column worksheet with header row
Sub Batch_GetID()
Dim vID As Variant
Dim strValues As String
Dim strFile As String
Dim strPath As String
Dim oDoc As Document
Dim oRng As Range
Dim i As Integer
Dim bFound As Boolean
Dim fDialog As FileDialog

    vID = Array("1d-", "2d-", "1e-", "1 d-", "2 d-", "1D-", "2D-", "1E-", "1 D-", "2 D-")

    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With fDialog
        .Title = "Select folder and click OK"
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then
            MsgBox "Cancelled By User", , "List Folder Contents"
            Exit Sub
        End If
        strPath = fDialog.SelectedItems.Item(1)
        Do Until Right(strPath, 1) = "\"
            strPath = strPath & "\"
        Loop
    End With
    strFile = Dir$(strPath & "z*.docx")
    While strFile <> ""
        Set oDoc = Documents.Open(strPath & strFile)
        bFound = False
        For i = 0 To UBound(vID)
            Set oRng = oDoc.Range
            With oRng.Find
                Do While .Execute(findText:=CStr(vID(i)) & "[0-9]{1,}", MatchWildcards:=True)
                    strValues = strFile & "', '" & oRng.Text
                    WriteToWorksheet strWB, strSheet, strValues
                    bFound = True
                    oRng.Collapse 0
                Loop
            End With
        Next i
        If bFound = False Then
            strValues = strFile & "', '" & "no matches"
            WriteToWorksheet strWB, strSheet, strValues
        End If
        oDoc.Close SaveChanges:=wdDoNotSaveChanges
        strFile = Dir$()
        DoEvents
    Wend
    MsgBox "Process complete"
lbl_Exit:
    Set fDialog = Nothing
    Set oDoc = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub


Private Function WriteToWorksheet(strWorkbook As String, _
                                  strRange As String, _
                                  strValues As String)
Dim ConnectionString As String
Dim strSQL As String
Dim CN As Object
    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                       "Data Source=" & strWorkbook & ";" & _
                       "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"
    strSQL = "INSERT INTO [" & strRange & "$] VALUES('" & strValues & "')"
    Set CN = CreateObject("ADODB.Connection")
    Call CN.Open(ConnectionString)
    Call CN.Execute(strSQL, , 1 Or 128)
    CN.Close
    Set CN = Nothing
lbl_Exit:
    Exit Function
End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
 

Tags
ms excel 2016, ms word, vba



Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy words from word to excel Copy & Paste Word Doc from Excel VBA RMerckling Excel Programming 4 04-18-2018 03:57 PM
Copy words from word to excel copy a specific words to excel list romanticbiro Word VBA 12 12-03-2014 05:12 AM
Copy data from Word into Excel chinchee Word VBA 18 05-23-2014 05:25 AM
Copy and paste words on right side of page Videolife Word 3 01-26-2013 12:54 PM
how to copy all ms word tables into excel rehan129 Word 0 01-28-2012 10:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:40 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