Word 2007 Macro accessing Excel document

[scar1]

Good morning,

Can anyone help me here with this strange problem.

I have programmed a form in MS Word 2007 which has various buttons, text boxes and combo boxes.
Part of my logic on one of the combo boxes is to look at a particular sheet in an excel file, run a vlookup and result is stored in a variable. I then use this value and place in a text box. My below code works however when changing the combobox value I sometimes get the following error:

Run-time error '1004':
Method 'Worksheets of object '_Global' failed

which causes me to end and restart macro. When restarting and running again it all works fine.

Can anyone help me with what the possible problem could be please.

Thanks
Steve

Code
=====

Private Sub cboCustomerName_Change()
Dim appExcel As Object
Dim CustomerNm
Dim AssAdNm
Dim ContractExp
Dim RevFreq
Dim hardware
Dim Software

CustomerNm = cboCustomerName.Value
Set appExcel = CreateObject("Excel.Application")


appExcel.Workbooks.Open "c:\Customers.xls"

AssAdNm = appExcel.VLookup(CustomerNm, Worksheets("CustomerSheet").Range("A1:L100"), 2, False)
tbAssignedAdvocate.Value = AssAdNm

ContractExp = appExcel.VLookup(CustomerNm, Worksheets("CustomerSheet").Range("A1:L100"), 3, False)
tbContractExp.Value = ContractExp

RevFreq = appExcel.VLookup(CustomerNm, Worksheets("CustomerSheet").Range("A1:L100"), 5, False)
tbReviewFreq.Value = RevFreq

hardware = appExcel.VLookup(CustomerNm, Worksheets("CustomerSheet").Range("A1:L100"), 6, False)
tbHardware.Value = hardware

Software = appExcel.VLookup(CustomerNm, Worksheets("CustomerSheet").Range("A1:L100"), 7, False)
tbSoftware.Value = Software

appExcel.Quit
Set appExcel = Nothing

End Sub

[Dave]

I'm guessing but Word usually doesn't like XL values. Usually you need to convert the XL value to a string using it in Word. Perhaps trial the following. Good luck. Dave
Dim CustomerNm as String
Dim AssAdNm as String
Dim ContractExp as String
Dim RevFreq as String
Dim hardware as String
Dim Software as String

[scar1]

Cheers Dave, ill give that a try and let you know how I get on with it.

Steve

[scar1]

Hmmm still having the same problem on this.... can anybody help?
Thanks

[Tommy]

Could you post a sample worksheet and the sample Word file with all confidential information removed? This way we will not have to recreate the data. :)