#1
|
|||
|
|||
VBA Copy Text issues
Hi community,
I encounter some difficulties in writing a VBA to do the following steps: Copy text from a Word Document to an Excel file according to an Excel List 1. Browse an Excel file for copying text from the active Word document to it. 2. After selected the Excel file, the VBA will find text according to the Excel List and then copy those text to the Excel file. 3. Open the Excel file copied with specific text. The system keep saying "xlsWB1 = "D:\databases\ENG.xlsx"" Object variable not set (Error 91). What should I do to resolve this issue? Your help will be greatly appreciated. Code:
Option Explicit Private xlWB1 As String Private xlWB2 As String Private xlSheet As String Sub CopyText_from_Word_to_Excel() Dim EXL As Object Dim xlsWB1 As Object Dim xlsWB2 As Object Dim xlsPath As String Dim oDoc As Document Dim oRng As Range xlsWB1 = "D:\databases\ENG.xlsx" xlWB2 = BrowseForFile("Select Workbook", True) If Not xlWB2 = vbNullString Then xlSheet = "sheet1" Set EXL = CreateObject("Excel.Application") Set oDoc = ActiveDocument Set oRng = oDoc.Range With oRng.Find .ClearFormatting .Replacement.ClearFormatting .Font.Name = "Times New Roman" .Font.Bold = True Do While .Execute() If oRng.Text = xlsWB1 Then WriteToWorksheet xlWB1, xlSheet, oRng.Text End If Loop End With lbl_Exit: Exit Sub End If Set xlsWB2 = EXL.Workbooks.Open(xlWB2) EXL.Visible = True 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 Private Function BrowseForFile(Optional strTitle As String, Optional bExcel As Boolean) As String Dim fDialog As FileDialog On Error GoTo err_Handler Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = strTitle .AllowMultiSelect = False .Filters.Clear If bExcel Then .Filters.Add "Excel workbooks", "*.xls,*.xlsx,*.xlsm" Else .Filters.Add "Word documents", "*.doc,*.docx,*.docm" End If .InitialView = msoFileDialogViewList If .Show <> -1 Then GoTo err_Handler: BrowseForFile = fDialog.SelectedItems.item(1) End With lbl_Exit: Exit Function err_Handler: BrowseForFile = vbNullString Resume lbl_Exit End Function |
#2
|
||||
|
||||
You have a mix-up with missing variables and incorrect variable types.
Note that it is good practice to add Option Explicit to the top of your modules, which will make it easier to spot such issues. Your code doesn't read values from either workbook for use in the search. You need to read the values from the worksheet you want to reference into an array in place of the fixed array in the code at https://www.msofficeforums.com/word-...ding-list.html then loop through the values. I posted code showing how to do this at https://www.msofficeforums.com/word-...ont-style.html
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Quote:
|
#4
|
||||
|
||||
The second link includes a code listing to read Excel data to an array.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
|||
|
|||
Quote:
Code:
Private Function xlFillArray(strWorkbook As String, _ strRange As String) As Variant 'Graham Mayor - http://www.gmayor.com - 24/09/2016 Dim RS As Object Dim CN As Object Dim iRows As Long strRange = strRange & "$]" 'Use this to work with a named worksheet 'strRange = strRange & "]" 'Use this to work with a named range Set CN = CreateObject("ADODB.Connection") 'Set HDR=NO for no header row CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""" Set RS = CreateObject("ADODB.Recordset") RS.Open "SELECT * FROM [" & strRange, CN, 2, 1 With RS .MoveLast iRows = .RecordCount .MoveFirst End With xlFillArray = RS.GetRows(iRows) If RS.State = 1 Then RS.Close Set RS = Nothing If CN.State = 1 Then CN.Close Set CN = Nothing lbl_Exit: Exit Function End Function |
#6
|
||||
|
||||
No.You need both functions. One to create the array which you need to loop through to search the document (i.e. vList in https://www.msofficeforums.com/word-...ding-list.html), the other to write the values to the second worksheet. https://www.msofficeforums.com/word-...ont-style.html demonstrates how to read the array so created and loop through the values to find them in the document.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
Tags |
copy text, vba word to excel |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Issues with a copy of a Contents Table! | KirstyAmanda | Word | 2 | 07-20-2020 02:51 PM |
Font issues when copy content from MS Word. | ArchanaV | Word | 3 | 06-29-2016 01:02 PM |
Issues with text in circle shapes that I am having | damefrombrum | Word | 0 | 12-01-2015 08:05 PM |
Page Up & Copy/Paste Issues | weamish | Word | 10 | 02-01-2014 08:25 PM |
Copy and Paste issues in Outlook 2007 | Oncilla | Outlook | 6 | 12-13-2010 01:00 PM |