Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-06-2014, 07:48 AM
ptmuldoon ptmuldoon is offline Pulling Data From Excel Windows 7 64bit Pulling Data From Excel Office 2013
Advanced Beginner
Pulling Data From Excel
 
Join Date: Sep 2014
Posts: 93
ptmuldoon is on a distinguished road
Default Pulling Data From Excel

I'm working/trying to learn how to pull NamedRanges from an Excel file and then loop the nameranges to find matches to bookmarks in Word and insert the info.



And I'll post back eventual code as I learn. But in getting started...

Do I need to actually open the excel file (from Word) to get the NamedRanges? I ask as I believe I need to first set a reference to Excel (in the VB Editor, select Tools + References).

You do need to open the file and not just able to select/point to the filename/location?

As I I would plan for this macro to be used company wide, I would have to instruct everyone to enable that feature in Word, which could be a challenge for the PC illiterate types.
Reply With Quote
  #2  
Old 12-06-2014, 08:33 AM
gmayor's Avatar
gmayor gmayor is offline Pulling Data From Excel Windows 7 64bit Pulling Data From Excel Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
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 of
Default

You don't need to open Excel to read a named range. The following function will put the named range in an array, and you can loop through the array to find your value

Code:
Option Explicit
Const strWorkbook As String = "C:\Path\WorkbookName.xlsx"        'The path of the workbook
Const strRange As String = "RangeName"

Private Function xlFillArray(strWorkbook As String, _
                             strRangeName As String) As Variant
Dim RS As Object
Dim CN As Object
Dim iRows As Long
    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"";"

    Set RS = CreateObject("ADODB.Recordset")
    RS.Open "SELECT * FROM [" & strRangeName & "]", 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
For example, using the data file from my Chart Merge add-in, this has a range called "MergeData"



You could then look for a name in the first column of the array, and report (say) the value associated with the name in the second column e.g. as follows. You would of course have to set the values of the workbook and the range name in the declarations above

Code:
Sub Test()
'Look for the value 'Al Gore' in the first column of the array
'And report the value in the second column
'Which here should report - 14132
Dim arr() As Variant
    arr = xlFillArray(strWorkbook, strRange)
    On Error GoTo lbl_Exit
    For iRows = 0 To UBound(arr, 1)
        sResult = arr(0, iRows)
        If sResult = "Al Gore" Then
            MsgBox arr(1, iRows)
        End If
    Next iRows
lbl_Exit:
    Exit Sub
End Sub
__________________
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
  #3  
Old 12-07-2014, 06:39 PM
ptmuldoon ptmuldoon is offline Pulling Data From Excel Windows 7 64bit Pulling Data From Excel Office 2013
Advanced Beginner
Pulling Data From Excel
 
Join Date: Sep 2014
Posts: 93
ptmuldoon is on a distinguished road
Default

Ok, I've been trying to follow what you posted, and honestly it looks more advanced than I can understand at the moment.

And I've been searching and preparing my own code as well in working to learn and figure it out.

Again, the end goal here to is starting from Word, to compare the BookMarks in Word to the NamedRanges in an Excel file, and if matches found to add the values(text) of the Named ranges to Word.

This code is a work in process. Currently, I'm trying to figure how to loop the Bookmarks and compare to Excel.

But I think it may be better/faster to have both the BookMarks and NamedRanges in separate arrays, and then compare them for matches? And then take just the matches and update the bookmarks?

Work in Process Code
Code:
Sub Test()
    Dim f, xlWorkBook As Object
    Dim Nm As Variant
    Dim NewPath, xlNamedRange, MyText As String
    Dim Bmk() As String
    Dim x, j As Integer

    'Choose the Excel File
    Set f = Application.FileDialog(msoFileDialogFilePicker)
    f.Title = "Please Select A New File"
    f.AllowMultiSelect = False
    f.Filters.Clear
    f.Filters.Add "Microsoft Excel Files", "*.xls, *.xlsb, *.xlsm, *.xlsx" 'Limit to Excel Files Only

    If f.Show = -1 Then
        'NewPath = f.SelectedItems(1)
        'MsgBox "The New File Path is: " & NewPath
        Set xlWorkBook = GetObject(f.SelectedItems(1))
        
        'Put all Excel Named Ranges into an Array
        For Each Nm In xlWorkBook.Names
            xlNamedRange = Nm.Name

            If xlNamedRange = ActiveDocument.Bookmarks("Company_Name") Then
                'Selection.TypeText (MyText)
                MyText = "String you want to insert"
                'UpdateBM "Company_Name", MyText
            End If
        Next Nm
    Else 'user clicked cancel
        Exit Sub
    End If
    
    'Read all the Book Marks into an Array
    x = ActiveDocument.Bookmarks.Count
    ReDim Bmk(x)
    For j = 1 To x
        Bmk(j) = ActiveDocument.Bookmarks(j).Name
        UpdateBM ActiveDocument.Bookmarks(j).Name, ""
        ActiveDocument.Bookmarks(j).Range.InsertAfter "add this after"
    Next j
    
    ActiveDocument.Bookmarks.ShowHidden = True
    ActiveWindow.View.ShowBookmarks = False
    
End Sub


Sub UpdateBM(BookmarkToUpdate As String, TextToUse As String)
    Dim BMRange As Range
    Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
    BMRange.Text = TextToUse
    ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
End Sub
Reply With Quote
  #4  
Old 12-08-2014, 04:49 AM
gmayor's Avatar
gmayor gmayor is offline Pulling Data From Excel Windows 7 64bit Pulling Data From Excel Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
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 of
Default

The function simply copies the part of the table that is a named range to an array, i.e. a virtual table which may be interrogated to get the values required from it and those values can be written to bookmarks, but you seem to be relating named ranges to bookmark names, so the eventual aim eludes me.

What is in the named range(s) and what do you want to do with the values contained, and how do the bookmark names (as opposed to their contents) relate to this?
__________________
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
  #5  
Old 12-08-2014, 09:27 AM
ptmuldoon ptmuldoon is offline Pulling Data From Excel Windows 7 64bit Pulling Data From Excel Office 2013
Advanced Beginner
Pulling Data From Excel
 
Join Date: Sep 2014
Posts: 93
ptmuldoon is on a distinguished road
Default

In Excel, I have a few hundred cells labeled with a NamedRange.

ie, Cell B10 on Worksheet2 may be called 'Company_Name', 'GL_Name', or something else. The information in these cells is all text.

Now in Word, I have similarly named BookMarks of 'Company_Name', etc.

Now, i'm trying to compare the BookMark Name to the NamedRange in Excel. And if a match is found, to update the BookMark with the value of the NamedRanged.

I'm boarding a plane here soon, but will report back later tonight as well as I continue to learn and experiment some more.

And thank you for your help as well. I'm (very) slowly beginning to understand and learn more on VBA. Its a long self learning !!

Edit. Made some more progress. And the below is now close. But it gives me the cell reference, ie. =Sheet1!$E$5 vs the actual value of the cell. So Unsure how to correct the NmdValue = Nm.Value in the below.

And/Or a better way vs looping everything.

Code:
Sub Test()
    Dim f, xlWorkBook As Object
    Dim Bm, Nm As Variant
    Dim Bmk, NmdRng, NmdValue As String
    Dim NewPath, MyText As String

    'Choose the Excel File
    Set f = Application.FileDialog(msoFileDialogFilePicker)
    f.Title = "Please Select A New File"
    f.AllowMultiSelect = False
    f.Filters.Clear
    f.Filters.Add "Microsoft Excel Files", "*.xls, *.xlsb, *.xlsm, *.xlsx" 'Limit to Excel Files Only

    If f.Show = -1 Then
        'NewPath = f.SelectedItems(1)
        'MsgBox "The New File Path is: " & NewPath
        Set xlWorkBook = GetObject(f.SelectedItems(1))
    Else 'user clicked cancel
        Exit Sub
    End If
    
    For Each Bm In ActiveDocument.Bookmarks
        Bmk = Bm.Name
        'MsgBox Bm
             
        ' Check Each BookMark for a Matching NamedRanged in the Excel File
        For Each Nm In xlWorkBook.Names
        NmdRng = Nm.Name
        NmdValue = Nm.Value 'This currently wrong, gives the cell reference, not the value?
        
             If Bmk = NmdRng Then
                'MsgBox "Match Found with " & Bmk
                'MsgBox NmdValue
                MyText = "String you want to insert"
                'ActiveDocument.Bookmarks(Bmk).Range.InsertAfter MyText
                ActiveDocument.Bookmarks(Bmk).Range.InsertAfter NmdValue
                'UpdateBM Bmk, MyText
                 Exit For
             End If
        Next Nm
        
    Next Bm
    
    ActiveDocument.Bookmarks.ShowHidden = True
    ActiveWindow.View.ShowBookmarks = False
    
End Sub


Sub UpdateBM(BookmarkToUpdate As String, TextToUse As String)
    Dim BMRange As Range
    Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
    BMRange.Text = TextToUse
    ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
End Sub

Last edited by ptmuldoon; 12-08-2014 at 12:18 PM.
Reply With Quote
  #6  
Old 03-17-2016, 02:02 PM
airwolf airwolf is offline Pulling Data From Excel Windows 7 32bit Pulling Data From Excel Office XP
Novice
 
Join Date: Mar 2016
Posts: 8
airwolf is on a distinguished road
Default

Ok, used your macro and moved the other two files into the right places.

When I run it, this pops up in yellow;

CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12 .0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

I think there is something I need to enable for this to work?
Reply With Quote
  #7  
Old 03-17-2016, 11:25 PM
gmayor's Avatar
gmayor gmayor is offline Pulling Data From Excel Windows 10 Pulling Data From Excel Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
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 of
Default

Quote:
Originally Posted by airwolf View Post
I think there is something I need to enable for this to work?
You probably don't have the Office 2010 database engine - see https://www.microsoft.com/en-us/down....aspx?id=13255
__________________
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
  #8  
Old 03-19-2016, 03:28 PM
airwolf airwolf is offline Pulling Data From Excel Windows 7 32bit Pulling Data From Excel Office XP
Novice
 
Join Date: Mar 2016
Posts: 8
airwolf is on a distinguished road
Default

You're right I didnt.

I do now and a different error comes up.

Gonna park this for a little while while I work on something else which i'm nearly there with and is less complicated.

Thanks for your help so far.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to replicate, consolidate and count cell data in excel for data consoldation. Loran Excel 7 06-05-2014 01:07 AM
Help please: Automatically pulling info out of one document and place in others. qwerty11 Word 2 06-25-2013 07:49 AM
Pulling Address Generated Word Documents Aalaf Alot Word 1 09-06-2012 11:27 PM
Pulling Data From Excel Need help with pulling out information mtnguye9 Excel 6 07-24-2010 07:52 PM
Mail merging and pulling varying data from Excel shannag1881 Mail Merge 0 10-05-2009 08:51 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:11 PM.


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