Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / October 2006

Tip: Looking for answers? Try searching our database.

Querying Excel from within a Word macro

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Brian Beck - 20 Oct 2006 14:06 GMT
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.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.