#1
|
|||
|
|||
VBA copy issues
Hi everyone,
Previously, I worked on a project where I need to copy specific text from a Word document into an Excel workbook according to an Excel file. I've written some VBA code to do this, but I'm running into some issues. Here is the code I'm using: Code:
Option Explicit Sub CopyText_from_Word_to_Excel() On Error Resume Next ' Declare variables Dim xlWB1 As String Dim xlWB2 As String Dim xlSheet As String Dim EXL As Object Dim oDoc As Document Dim oRng As Range Dim Arr() As Variant ' Set the path to the Excel workbook to copy data from xlWB1 = "C:\Users\Excel file\list.xlsx" ' Show a file dialog box to select the Excel workbook to copy data to xlWB2 = BrowseForFile("Select Workbook", True) If xlWB2 = vbNullString Then Exit Sub ' Set the name of the worksheet to copy data to xlSheet = "Sheet1" ' Create an instance of Excel and open the target workbook Set EXL = CreateObject("Excel.Application") EXL.Visible = True EXL.Workbooks.Open xlWB2 ' Get a reference to the active Word document and search for each value in the Excel workbook Set oDoc = ActiveDocument Set oRng = oDoc.Range Arr = xlFillArray(xlWB1, xlSheet) With oRng.Find .ClearFormatting .Replacement.ClearFormatting .Font.Name = "Times New Roman" .Font.Bold = True Dim ind As Long For ind = LBound(Arr, 2) To UBound(Arr, 2) .Text = Arr(1, ind) Do While .Execute() WriteToWorksheet xlWB2, xlSheet, oRng.Text Loop Next ind End With End Sub Public Function WriteToWorksheet(strWorkbook As String, _ strRange As String, _ strValues As String) ' Declare variables Dim ConnectionString As String Dim strSQL As String Dim CN As Object ' Set the connection string and SQL statement to write data to the worksheet ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";" strSQL = "INSERT INTO [" & strRange & "$] VALUES('" & strValues & "')" ' Open a connection to the workbook and execute the SQL statement Set CN = CreateObject("ADODB.Connection") CN.Open ConnectionString CN.Execute strSQL CN.Close End Function Public Function BrowseForFile(Optional strTitle As String, Optional bExcel As Boolean) As String ' Declare variables Dim fDialog As FileDialog ' Show a file dialog box to select a file On Error Resume Next 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 BrowseForFile = fDialog.SelectedItems.Item(1) End If End With End Function Public Function xlFillArray(strWorkbook As String, _ strRange As String) As Variant ' Declare variables Dim RS As Object Dim CN As Object Dim iRows As Long ' Set the connection string and SQL statement to read data from the worksheet strRange = strRange & "$]" Set CN = CreateObject("ADODB.Connection") 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 ' Get the data from the recordset and close the connection and recordset With RS .MoveLast iRows = .RecordCount .MoveFirst End With xlFillArray = RS.GetRows(iRows) RS.Close CN.Close End Function Can anyone offer any suggestions on how to fix the issue with my code and get it working as expected? Thank you in advance for your help! Word document for testing: Loading Google Docs Excel template: Loading Google Sheets Excel list: Loading Google Sheets Excel file (Expected outcome): Loading Google Sheets Last edited by syl3786; 05-03-2023 at 08:02 PM. |
#2
|
||||
|
||||
Can you post a sample document and workbook that aligns with the code you are running so we can run the code and work out what your issue is?
Right off the bat I wouldn't be using a sql query to transfer the data into Excel - I would expect that to be writing to a Recordset, not to a worksheet.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Quote:
Word document for testing: Loading Google Docs Excel template: Loading Google Sheets Excel List: Loading Google Sheets Excel file (Expected outcome): Loading Google Sheets Last edited by syl3786; 05-03-2023 at 08:03 PM. |
#4
|
||||
|
||||
Oops, my bad. I didn't read beyond your code.
Your code is finding a string from the list. When it finds that string, it appears you want to report that value (which you already had anyway). It would make more sense if it actually looked to the right of the found string to return the value of the text sitting beside that found string (or returned the contents of the whole paragraph). Neither of the Excel workbooks look like they match the concept of list.xlsx. I was expecting a list of user names in that. Your code then loops through each list item finding hits before moving on to a next list item. This will harvest the data in unrelated order - for instance your outcome doc shows an order like CEO, Oliver, CEO, Ethan, CEO, Amelia, ... but your code is structured to return CEO, CEO, CEO, ..., Oliver, Ethan, Amelia, ... Why would that be a useful output? I'm looking at the contents of the Word file and in my mind, aligning it with your outcome workbook and think perhaps the list and the search is a pointless distraction. To me it appears you want the dialog transferred in the correct order into Excel and separate the speaker from the dialog by putting each into two separate columns. If this is the case, what is the point of searching for list items unless you want to exclude specific speakers? Your word doc shows xxx against each name. Your Excel workbook shows headings of Name & URL. Is each piece of text beside a name a URL or a string of text?
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
|||
|
|||
Quote:
I also forgot to upload the Excel list as reference. I edited the post and comments again. You can now see the Excel list. I expect the macro can copy the specific text such as "CEO", and other names (there should be more than 100 names but i just typed some of them in the sample) from Word to Excel according to the Excel List. The URL column in the Excel template and Excel(expected outcome) is for putting URL and then mail merge those URLs to the displayed text by another macros. I already have that, but I am struggled in this macro (copying text from Word to Excel) Word document for testing: Loading Google Docs Excel template: Loading Google Sheets Excel List: Loading Google Sheets Excel file (Expected outcome): Loading Google Sheets |
#6
|
|||
|
|||
Quote:
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA Copy Text issues | syl3786 | Word VBA | 5 | 04-17-2023 08:44 PM |
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 |
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 |