Word searching Excel Spreadsheet

[TSMABob]

Hello All,

I've got what might be a really easy question. I don't know how to do it, so I really have no clue.

I have a word document that is a form for the user to fill out. When they enter in data in one of the text fields, a macro runs. I would like the macro to do a few things:

1. Search an Excel document for the ID number just entered by the user.

2. When that ID is located (amongst 5500+ other entries) be able to access other values on that same row (name, location)

I've already figured out (with some help) how to do most of it with javascript inside of an HTML file searching an XML document. But I really need the added functionality of a word document and I figured that Excel is easier to do in word than XML would be.

Any help would be greatly appreciated.

 

[SkipVought]

Hi TSMABob,

1. Add a reference in the VB Editor to Excel
2. Create an Excel APplication OBject...

Dim xlApp as Excel.Application
Set xlApp = CreateObject("Excel.Application")

3. Open the workbook

dim wb as workbook
set wb = xlApp.Workbooks.Open MyPath & "\MyWorkbook.xls"

4. code a reference to the lookup value

dim vLookupVal
vLookupVal = Application.Vlookup(vLookupVal, YourLookupTable, YourLookupColumn)
If IsError(vLookupVal) then
'houston, what happened?!
Else
'houston, we have a hit!
EndIf

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TSMABob]

Sorry for my late reply, I've been out of town.

Wow, thanks for the help with this.

I've got a problem though. I get the error "Method or data member not found" related to Application.VLookup. Is that something I need to write myself? or is it in the Excel object dll?

Also, where should I actually specify the value I want to lookup?

 

[Stretchwickster]

Have you added a reference to Excel (Tools menu -> References) in your Word VB Editor as Skip instructed?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[DrBowes]

I would guess it should be Application.WorksheetFunction.VLookup

However it might be better to use Find.

Once you have found the row you can return the values of other columns in that row.

Set c = Columns(ColtoLookIn).Find(what:=LookingFor).Row

If Not c Is Nothing Then
RowContainingValue = _
Columns(ColtoLookIn).Find(what:=LookingFor).Row
Else
MsgBox "Item Not Found"
End If

 

[TSMABob]

Yeah, I have a reference to Excel in my VB Editor.

I tried making up a searching function of my own, but its painfully slow...

Set cell = Excel.Cells
For Each cell In Range("A1", "A6000")
  If cell.Value = lookThisUp Then
    finalcount = counter + 1
    MsgBox ("hooray")
  End If
  counter = counter + 1
Next

And when I tried DrBowes method I get:
"Run-time error 13: Type Mismatch"

Using "1CD-V17" as an actual value in my table, I tried using the code this way...

Dim c, RowContainingValue
Set c = Columns("A").Find(what:="1CD-V17").Row
    
    If Not c Is Nothing Then
        RowContainingValue = _
        Columns("A").Find(what:="1CD-V17").Row
    Else
    MsgBox "Item Not Found"
    End If

Did I screw something up?

 

[SkipVought]

The Application that the VLOOKUP refers to is Excel. Therfore (my mistake)

vLookupVal = xlApp.Vlookup(vLookupVal, YourLookupTable, YourLookupColumn)

Sorry!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TSMABob]

Thanks for the help Skip, but I'm still confused as to what value Vlookup is returning, since you're assigning the resulting value to vLookupVal and at the same time passing it into the function.

 

[SkipVought]

Its the ID number the user entered. Unless you need the ID number later on in your processing, the objective you to locate a row and get other data in that row.

VLOOKUP is returning a value in the column specified from the row being looked up.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[dparton]

I have a similar problem and none of your suggestions seem to help.
My boss gives me an Excel sheet listing all the patients are to be seen on a daily basis.
Once the patients are seen by a provider, a note is generated using MSWord and printed. After the note prints, a notation next to the patient’s name in the Excel sheet should be made for statistical purposes.
The patient’s names are on Column C in the Excel sheet and I want to have the Word macro open the spreadsheet (and this is done already with the code I show you below), then find the patient’s name in Column C and wait for the provider to make a notation on the next cell that the patient was seen..
The variable PatientName$ is public variable automatically generated by a “printing macro”. For my LittleTest () purpose I created an input box to generate the variable until I solve the problem. Please look where it says This is where I have the problem and NEED HELP. Thank you.


Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub

Sub LittleTest()
' This is a test to check if the name will be found in Excel..
' Create the variable that will be tested. This should be found in the DSL sheet
PatientName$ = InputBox("Please enter a patient's Name", "Patient Identity&quot
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.

Set MyXL = GetObject(, "Excel.Application&quot
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel
' Set the object variable to reference the file you want to see.
Set MyXL = GetObject(&quot;L:\shared\pa\MyDirectory\MySpreadSheet.xls&quot
' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
' Do manipulations of My file here.
' This is where I have the problem and NEED HELP

Set Cell = Excel.Cells
Range(Cells(1, 3), Cells(1000, 3)) = True
Excel.Application.Cells.Find(what:=PatientName$, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
' The following routine is always true, and it closes the file.
' If ExcelWasNotRunning = True Then
' MyXL.Application.Quit
' End If

Set MyXL = Nothing ' Release reference to the
' application and spreadsheet
GoTo Quit

Quit:
End Sub