Getting Data from Excel to Word Userform

[rohanmalhotra]

Hi all

i am preparing a template in word userform. In the template, i want the senders name to come up automatically, therefore, i have created an excel sheet which has the list of staff members with their employee id's (as they login to the system with there employee id's and that will be picked up). i have written a code but it is giving me run time error 13.

Sub GetExcelData()
 
    Dim objExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim myRange As Range
    Dim UserName As String
    Dim rs As Variant
 
    Set objExcel = CreateObject("Excel.Application")
    Set wbk = objExcel.Workbooks.Open("M:\Phones\Management Information\Projects\Staff.xls")
    Set ws = wbk.Worksheets("Staff")
 
    UserName = Me.txtID.Value
 
    myRange = ws.Range("A1:B500")
     rs = wbk.Application.WorksheetFunction.VLookup("UserName", "myRange", 2, False)
 
End Sub

Can anyone of you please help.

Many Thanks

 

[rohanmalhotra]

Hi... can anyone help?

 

[pbornemeier]

Try this:

Sub GetExcelData()
 
    Dim objExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim myRange As Range
    Dim UserName As String
    Dim rs As Variant
 
    Set objExcel = CreateObject("Excel.Application")
    Set wbk = objExcel.Workbooks.Open("M:\Phones\Management Information\Projects\Staff.xls")
    Set ws = wbk.Worksheets("Staff")
 
    UserName = Me.txtID.Value
 
    Set myRange = ws.Range("A1:B500")
    rs = wbk.Application.WorksheetFunction.VLookup(UserName, myRange, 2, False)
 
End Sub

When describing a problem it is helpful to state the number and text of the error you see as well as where in your code the error is occurring.

 

[rohanmalhotra]

Hi

i am getting error on the following line :

rs = wbk.Application.WorksheetFunction.VLookup(UserName, myRange, 2, False)


its Runtime error '13'. Type mismatched.

Thanks

 

[pbornemeier]

This code is located in a MSWord userform code page.

When I changed the dimension statement for myRange to object, the code worked.

    Dim myRange As Object

If that does not work for you, put a STOP line immediately before the line that errors out and tell us the values of all variables in the code. VBE | View | Locals opens the 'Locals' pane which displays current info on code execution.

 

[rohanmalhotra]

now i am getting error 91

it says - Object Variable or with block variable not set. its coming on the same line