Get vaules from Excel based on Word Drop Down Box

[Robert_L_Ross]

Here's where I'm at:

I want to have a drop down box value determine what values to return to
other fields from Excel.

I have dropdown1 and I currently use the Case process (Case 1, Case 2,
etc.). Problem is you have to keep modifying the macro to add new values.

I'd like Word to search an Excel spreadsheet and return a specific value
(kind of like how DLookup works) back to a cell in the Word Doc. Here's what
I have so far:

Sub SetToInformation()
Select Case _
ActiveDocument.FormFields("ATTNBox").DropDown.Value
Case 1
ToBoxTemp = " "
FaxNumberTemp = "(000) 000-0000"
TelNumberTemp = "(000) 000-0000"
Case 2
ToBoxTemp = "Citibank"
FaxNumberTemp = "(800) 846-6444"
TelNumberTemp = "(585) 248-7254"
Case 3
ToBoxTemp = "EDFUND"
FaxNumberTemp = "(123) 456-7890"
TelNumberTemp = "(098) 765-4321"
Case Else
ToBoxTemp = ""
FaxNumberTemp = "(999) 999-9999"
TelNumberTemp = "(999) 999-9999"
End Select
ActiveDocument.FormFields("ToBox").Result = ToBoxTemp
ActiveDocument.FormFields("FaxNumber").Result = FaxNumberTemp
ActiveDocument.FormFields("TelNumber").Result = TelNumberTemp

End Sub

I'm not sure if this will work to get the Excel sheet open, I get a "User
Defined type not defined" error on the line marked with an **:

Dim FAXApp As excel.Application **
Dim FAXBook As excel.Workbook
Dim FAXSheet As excel.sheet
Dim FAXData As Variant
Dim ListCount As Long
Dim ToBoxTemp As String
Dim FaxNumberTemp As String
Dim TelNumberTemp As String

Set FAXApp = GetObject(, "Excel Application")
Set FAXBook = FAXApp.Workbooks.Open("G:\A F S\Document Templates\FAX
COVER SHEET\FAX.xls")
Set FAXSheet = FAXBook.Worksheets(1)

FAXData = FAXSheet.Range("A23").Value

Any help would be greatly appreciated!

 

[Perry]

Hi Robert,

Consider this:

From within Word VBA:
Dim wb as object
Dim arrFaxInfo as variant
Set wb = GetObject("c:\MyPath\Workbook1.xls")
'<< Have Excel to return values in array
On Local Error Goto ErrHandler '<< always use an errhandler (to be
further developed by you)
arrFaxInfo =
wb.GetValue(ActiveDocument.FormFields("ATTNBox").DropDown.Value)
wb.Close 0 '<< close without saving changes
'<< check second dimension of array
MsgBox affFaxInfo(2)

In other words, you'll be retrieving data from Excel in an array to be used
in word.
Some Excel programming is involved here, to pick up the corresponding other
faxinfo.
The Excel function to do so:

From within Excel VBA (c:\MyPath\Workbook1.xls)
ThisWorkbook module

Public Function GetValue(byval DropDownValue as string) as variant
Dim sht as worksheet, cel as range
Dim arr(), x as integer
set sht = me.sheets(1) '<< first sheet with data
On Local Error GoTo NoMatch
set cel = sht.usedrange.columns(1).Find(DropDownValue)
If not cel is nothing then
for x = 0 to sht.usedrange.columns.count
redim preserve arr(x)
arr(x) = cel.offset(, x)
next
End if
ExitHere:
GetValue = arr
Exit function
NoMatch:
Resume exithere
End Function

Krgrds,
Perry

 

[Robert_L_Ross]

Perry,

I think it might work, but I'm getting a type mismatch:
Dim CSFaxWorkbook As Object

....

Set CSFaxWorkbook = ("C:\pathname\filename.xls") 'I get a type mismatch
on this line. Any ideas?

THX again!

 

[Perry]

replace

Set CSFaxWorkbook = ("C:\pathname\filename.xls")

by
Set CSFaxWorkbook = GetObject("C:\pathname\filename.xls")

Kindly report yr progress.

Krgrds,
Perry

 

[Robert_L_Ross]

Perry,

I had to make one additional change by adding:
Dim ArrFaxInfo As String
But then I received:
Run-Time Error '438'
Ojbect doesn't support this property or method
on this line:
ArrFaxInfo =
CSFaxWorkbook.GetValue(ActiveDocument.FormFields("ATTNBox").DropDown.Value)

I noticed as I typed that line, the usual prompts you get after the first
period:
ArrFaxInfo = CSFaxWorkbook.
did not appear. It's as if my system doesn't know what the GetValue command
is.

So, right now the code looks like this:
Dim CSFaxWorkbook As Object
Dim ToBoxInfo As Variant
Dim FAXBoxInfo As Variant
Dim PHONEBoxInfo As Variant
Dim ArrFaxInfo As String
Set CSFaxWorkbook = GetObject("pathname\filename.xls")
' On Local Error GoTo ErrHandler - rem'd this till i get farther in the
process
ArrFaxInfo =
CSFaxWorkbook.getvalue(ActiveDocument.FormFields("ATTNBox").DropDown.Value)
CSFaxWorkbook.Close 0
MsgBox (ArrFaxInfo)

Any ideas?

BTW, thanks a LOT for the help and time you've already given me!

 

[Perry]

ArrFaxInfo =
CSFaxWorkbook.GetValue(ActiveDocument.FormFields("ATTNBox").DropDown.Value)

This should be one line; second line dropped due to word wrapping.

Notice the Line Continuation sign in below VBA statement:

ArrFaxInfo = _
CSFaxWorkbook.GetValue(ActiveDocument.FormFields("ATTNBox").DropDown.Value)

Try again.
Krgrds,
Perry

 

[Robert_L_Ross]

Perry,

I have this as one line (my screen resolution is high so I don't have the
wrap indicator - the newsgroup screen is so small it wraps here, but it
doesn't wrap on my screen).

Plus, since it didn't work, I tried to hand-enter the line, and like I said,
my system doesn't seem to have the GetValue function. You know how as you
enter, when the system detects that it has a list to choose from, it presents
that list. When I'm entering and I get here:

ArrFaxInfo = CSFaxWorkbook.

I get the list of options, but GetValue is not one of those options. The
list goes from "FullNameURLEncoded" to "HasPassword". Also, when I type
getvalue, it should capitalize if it recognizes the command (getvalue =
GetValue), but it doesn't. I'm thinking I don't have the correct library
registered? Currently, I have the following, listed in the order the appear
in my References window:

Visual Basic for Applications
Microsoft Word 10.0 Object Library
OLE Automation
Normal
Microsoft Office 10.0 Object Library
Microsoft Excel 10.0 Object Library
Microsoft VBScript REgular Expressions 1.0
Microsoft VBScript REgular Expressions 5.5
Microsoft Visual Basic for Applications Extensibility 5.3

Is the GetValue function in some other reference?

THX!