Querying Excel from within a Word macro

[Brian Beck]

I have a form that users fill in and there are 3 fields in which they fill
out their county, district and campus numbers (CDC #) to define their
location. Every user will put in this information, and it is what I use to
identify them later when I import all the data into an Access database.
Occasionally though, a user will either enter the wrong CDC # or they will
enter the correct one, but then when they write out the name of their
district and school, it will be something different from the one identified
by the CDC # they entered.

Now I know this is stuff I could easily check for at the time of entry, but
management is what it is and as such, I cannot include ANY macros in the
Word form. That being the case, I'd like to write a macro for me personally
so that when I am looking at a form, I could run this macro wherein it would
grab the county-district number, look that number up in an Excel file and
return the district name associated with that number. Additionally, it
would also take the full CDC # and query another Excel file that contained
all the campuses and return the campus name associated with that number.
This way, I could visually compare what the user typed in for their district
and campus names with what the CDC # they typed in corresponds to.
Naturally we want these to be the same, but on occasion someone mistypes the
CDC # and I'd like to catch that error as early as I can.

So how can I construct a Word macro that will allow me to do a VLOOKUP in an
Excel spreadsheet and return the value in a msgbox?

-Brian

 

[Shauna Kelly]

Hi Brian

Start with
Control Excel from Word
http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm

Then, I think the easiest thing is to use Find within an Excel column to
find the item you're looking up, and then go across the required number of
cells to grab the data you're looking for.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[Brian Beck]

It looks like this should work great, but I'm not encountering the following
error:
"Compile Error: Named argument not found"

This error points to the "Selection.Find" statement below. Any ideas?

Sub Verify_Data()
Dim vFormDistrictName As String
Dim vActualDistrictName As String
Dim vCDC1 As String
Dim vCDC2 As String
Dim vCDC3 As String
Dim vCountyDistrictNum As String
Dim vFormCampusName As String
Dim vActualCampusName As String
Dim oExcelApp As Object
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

vFormDistrictName = ActiveDocument.FormFields("District_Name").Result
vCDC1 = ActiveDocument.FormFields("County_District_Num1").Result
vCDC2 = ActiveDocument.FormFields("County_District_Num2").Result
vCDC3 = ActiveDocument.FormFields("County_District_Num3").Result
vFormCampusName = ActiveDocument.FormFields("Campus_Name").Result
vCountyDistrictNum = vCDC1 & vCDC2

'specify the workbook to work on
WorkbookToWorkOn = "S:\Security\Incident Tracking Database -
00Dev\Development\district_update_.xls"

'If Excel is running, get a handle on it; otherwise start a new instance of
Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Process the workbook
For Each oSheet In oXL.ActiveWorkbook.Worksheets

'Search in column A for the value found in vCountyDistrictNum
Activecell.Columns("A:A").EntireColumn.Select
Selection.Find(What:=vCountyDistrictNum, After:=Activecell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate

'Move one cell to the right and assign that value of that cell to
vActualDistrictName
Activecell.Offset(0, 1).Range("A1").Select
vActualDistrictName = Activecell.Value

MsgBox "The actual district name is " & vActualDistrictName
Next oSheet

If ExcelWasNotRunning Then
oXL.Quit
End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub

 

[Helmut Weber]

Hi Brian,

your selection is a Word-selection.
You have to search in an Excel-selection.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Brian Beck]

How then do I differentiate the two in my code?

 

[Shauna Kelly]

Hi Brian

You basically need to check every line and make sure that it explicitly
refers to the right application.

For example, code running in Word doesn't know what the ActiveCell is. So
you need to do oXL.ActiveCell. Code with this kind of problem is the hardest
to track down, because sometimes the code will work today, on your machine,
if you're wearing blue socks. But there are no guarantees that it will work
reliably all the time.

Sometimes, both Word and Excel use the same object names. So Word interprets
Selection.Find to mean Word's Selection.Find. If you want to use Excel's
Selection, you'll need oXL.Selection.Find.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[Brian Beck]

It helped greatly...thanks so much!

Hi Brian

You basically need to check every line and make sure that it explicitly
refers to the right application.

For example, code running in Word doesn't know what the ActiveCell is. So
you need to do oXL.ActiveCell. Code with this kind of problem is the
hardest to track down, because sometimes the code will work today, on your
machine, if you're wearing blue socks. But there are no guarantees that it
will work reliably all the time.

Sometimes, both Word and Excel use the same object names. So Word
interprets Selection.Find to mean Word's Selection.Find. If you want to
use Excel's Selection, you'll need oXL.Selection.Find.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word