#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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 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 |
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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? |
#7
|
||||
|
||||
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 |
#8
|
|||
|
|||
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. |
|
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 |
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 |