Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-03-2023, 08:21 AM
syl3786 syl3786 is offline VBA copy issues Windows 10 VBA copy issues Office 2019
Advanced Beginner
VBA copy issues
 
Join Date: Jan 2023
Posts: 68
syl3786 is on a distinguished road
Default 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
The issue I'm having is that the text is not getting copied from Word to Excel according to the xlWB1 workbook. I believe the problem may be with the RS.Open "SELECT * FROM [" & strRange, CN, 2, 1 line, but I'm not sure how to fix it.

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.
Reply With Quote
  #2  
Old 05-03-2023, 04:29 PM
Guessed's Avatar
Guessed Guessed is offline VBA copy issues Windows 10 VBA copy issues Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #3  
Old 05-03-2023, 05:07 PM
syl3786 syl3786 is offline VBA copy issues Windows 10 VBA copy issues Office 2019
Advanced Beginner
VBA copy issues
 
Join Date: Jan 2023
Posts: 68
syl3786 is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
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.
Thanks for your reply and feedback. I posted the sample files in my original post.

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.
Reply With Quote
  #4  
Old 05-03-2023, 07:39 PM
Guessed's Avatar
Guessed Guessed is offline VBA copy issues Windows 10 VBA copy issues Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #5  
Old 05-03-2023, 08:04 PM
syl3786 syl3786 is offline VBA copy issues Windows 10 VBA copy issues Office 2019
Advanced Beginner
VBA copy issues
 
Join Date: Jan 2023
Posts: 68
syl3786 is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
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?
It is my bad. I posted the links of sample files after your first comment.
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
Reply With Quote
  #6  
Old 05-05-2023, 01:15 AM
syl3786 syl3786 is offline VBA copy issues Windows 10 VBA copy issues Office 2019
Advanced Beginner
VBA copy issues
 
Join Date: Jan 2023
Posts: 68
syl3786 is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
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?
May I ask if you could help edit the codes? I am still new to VBA. I beg you on bended knee on that. Please
Reply With Quote
Reply

Thread Tools
Display Modes


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
VBA copy issues 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
VBA copy issues Copy and Paste issues in Outlook 2007 Oncilla Outlook 6 12-13-2010 01:00 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:59 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft