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 - 21 Oct 2006 04:19 GMT
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
>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
[quoted text clipped - 23 lines]
>
> -Brian
Brian Beck - 23 Oct 2006 16:34 GMT
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
> Hi Brian
>
[quoted text clipped - 38 lines]
>>
>> -Brian
Helmut Weber - 23 Oct 2006 16:53 GMT
Hi Brian,
your selection is a Word-selection.
You have to search in an Excel-selection.

Signature
Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"
Brian Beck - 23 Oct 2006 17:01 GMT
How then do I differentiate the two in my code?
> Hi Brian,
>
> your selection is a Word-selection.
> You have to search in an Excel-selection.
Shauna Kelly - 23 Oct 2006 22:54 GMT
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
> How then do I differentiate the two in my code?
>
>> Hi Brian,
>>
>> your selection is a Word-selection.
>> You have to search in an Excel-selection.
Brian Beck - 24 Oct 2006 19:55 GMT
It helped greatly...thanks so much!
> Hi Brian
>
[quoted text clipped - 22 lines]
>>> your selection is a Word-selection.
>>> You have to search in an Excel-selection.