Linking word UserForm to an excel spreadsheet RRS feed

  • Question

  • Hi, a warning beforehand, I am using office 2003, but i can upgrade to 2010 if that makes things easier to solve.

    I am trying to link an excel spreadsheet with a word userform listbox. I'm very knew to VBA so i will need a bit of help with it but here is what i would like to do:

    • I have a form in word with various text boxes.
    • When someone opens the form i would like a userform with a listbox to appear
    • From the list box i would like a list of names to dropdown (these names are on column 1 in an excel spreadsheet)
    • When they choose a name and click an "OK" command button i would like the textboxes on the form to autofill with information from the spreadsheet that correspond to the chosen name.

    for example if "Dave" was selected, text box 1 on the form would fill in the name, box 2 the start date, box 3 the annual leave remaining etc, just for Dave:

    Name Start Date Annual Leave Remaining (days) Annual Leave entitlement (days)
    Dave 09/01/10 12 32
    John 08/02/09 3 34
    Bobby C 07/03/08 9 32
    • I would like to just have the names in the list box, i would like the rest of it to link but not be shown in the listbox of the userform
    • Finally, if someone updates the excel spreadsheet then I would like this to update the information that gets populated into the textboxes from the userform.

    The document I created in word which I wish to be filled is a template. All the boxes I want to fill have been bookmarked (for simplicity i called them "bookmark1" "bookmark2" "bookmark3"etc, there are 6 of them)

    The excel spreadsheet i wish to populate from has 6 columns which correspond to the 6 boxes i would like to be filled.

    I opened Visual Basic and added "UserForm1" which consist of a list box and command buton which says "OK"

    The references for the project include Microsoft Excel 11.0 Object Library and Microsoft DAO 3.6 Object Libarary as well as the ones which were already selected.

    Im not sure what kind of binding i need, is it late or early or DAO. I want the form to pull the data from the excel sheet, but if the excel data changes i want that to be reflected in the pulled data. This form will be used many times by many different people and the spreadsheet will be updated regularly so it's important that the data pulled is up to date.

    I have used Early Binding as that seemed to be the right kind judging from what i read.

    the code for the userform is as follows:

    Option Explicit

    Private Sub Userform_Initialize()

    Dim xlApp As Excel.Application
    Dim xlbook As Excel.Workbook
    Dim Listarray As Variant
    Dim bStartApp As Boolean
      On Error Resume Next
      Set xlApp = GetObject(, "Excel.Application")
      If Err Then
        bStartApp = True
        Set xlApp = New Excel.Application
      End If
      On Error GoTo 0
      With xlApp
        Set xlbook = .Workbooks.Open("G:\office\CAL.xls")
        Listarray = xlbook.Names("mySSRange").RefersToRange.Value
        xlbook.Close SaveChanges:=False
        Set xlbook = Nothing
      End With
      If bStartApp Then xlApp.Quit
      Set xlApp = Nothing
      With ListBox1
        .ColumnCount = UBound(Listarray, 2)
        .List() = Listarray
      End With
      Exit Sub
    End Sub

    I already know this code will not give me what i want (i have tried it just in case) but im hoping you can help me alter it to fill in what i need.

    I would like the UserForm to pop-up when the document is opened using the template so i created a module and the code for it is simply as follows:

    Option Explicit

    Sub AutoNew()

    End Sub

    It starts up and asks if i would like to enable the macros, i click "enable macros" it thinks long and hard then pops-up with:

    "CAL.xls is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen CAL.xls?" i click "Yes". I would like to point out the CAL.xls isnt open at this point so im not 100% sure why it's telling me this. After that it opens the template but comes up with:

    "run time error '1004':

    Application-defined or object-defined error"

    No UserForm shows

    I'm assuming this is due to my sloppy coding or some error on my part. do you know where i have gone wrong or how i can fix it?

    Monday, March 24, 2014 1:10 PM

All replies

  • You've asked a lot of questions here about your whole app, better to break things down and test each, then put them all together.

    Starting with the Excel file, is Excel already running and if so the file already open say by the user. get or create Excel and open the file if necessary. When done close the file if you opened it and quit Excel if you created a hidden instance. Otherwise normally best leave things as the user had them.

    Sub test()
    Dim bXL As Boolean
    Dim bWB As Boolean
    Dim xl As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim sFile As String
        sFile = "<path-to-file>"
        On Error Resume Next
        ' if xl is open we'll use it or create an invisible instance
        Set xl = GetObject(, "excel.application")
        On Error GoTo 0    ' or to our error handler
        If Not xl Is Nothing Then
            bXL = True
            Set xl = CreateObject("excel.application")
        End If
        If bXL Then
            ' if exl was open was our file already open
            On Error Resume Next
            Set wb = xl.Workbooks(sFile)
            On Error GoTo 0    'or to our error handler
        End If
        If Not wb Is Nothing Then
            bWB = True
            Set wb = xl.Workbooks.Open(sFile)
        End If
        If Not wb Is Nothing Then
            MsgBox wb.Name
            ' get the array
            MsgBox "oops!"
            ' does the file exists, is it open in a 2nd excel instance, etc
            ' handle the error
            Exit Sub
        End If
        ' close the wb only if we opened it
        If bWB = False Then
            wb.Close , False
        End If
        ' quit xl only if we created it
        If Not bXL Then
        End If
    End Sub

    As written, with the qualified variables to the Excel object model (early binding), you'll need to add a reference in your Word project to Excel. However it must be to the lowest version any user might potentially use, eg 2003. If that's n/a to you should adapt to 'late binding' by changing any variables declared as an Excel reference,  eg

    Dim wb As Excel.Workbook ' early binding with excel reference
    ' or
    Dim wb As Object ' late binding

    Test different combinations of Excel not/open and the file not/open before moving on. For simplicity the above doesn't not cater fro the relatively rare scenaro there are multiple instances of Excel running and you grabbed an instance but the file was open in a different instance.

    If your file is never likely to be open you might be able to get your data without opening it or starting an Excel instance by using ADO. If applicable a quick search should find you some examples.

    • Proposed as answer by ryguy72 Friday, May 2, 2014 3:27 PM
    Tuesday, March 25, 2014 7:14 PM
  • To load the listbox using ADO see the example code at http://www.gmayor.com/Userform_ComboBox.html

    Graham Mayor - Word MVP

    Wednesday, March 26, 2014 11:51 AM