Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box with Titles (Excel XP)

    I'm populating a combo box on a userform with the files names. How could I include the title of the file as found in the properties of the workbook.

    TIA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Combo Box with Titles (Excel XP)

    It is a "BuiltinDocumentProperty"
    To get it for the activeworkbook and place it in a variable named "sTitle":

    sTitle = Activeworkbook.BuiltinDocumentProperties("title")

    or if you are looping thru all the workbooks and have a workbook object variable:

    sTitle = wkb.BuiltinDocumentProperties("title")

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box with Titles (Excel XP)

    Steve thanks for the reply. Below is the code I've been using to populate the combo box. How will I pick up the "title" in there?

    Private Sub ComboBox1_Change()
    cc = Me.ComboBox1.Value
    With Application.FileSearch
    .NewSearch
    If cc <> "" Then
    .LookIn = "S:FinanceRebatesSupplierReconsCalYear2005" & cc
    End If
    .SearchSubFolders = True
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute() > 0 Then
    For i = 1 To .FoundFiles.Count
    Filename = Mid(.FoundFiles(i), 48 + Len(cc), Len(.FoundFiles(i)))
    Bookname = .FoundFiles.BuiltinDocumentProperties("title")
    Me.ComboBox2.AddItem (Filename)
    Next i
    End If
    End With

    TIA

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box with Titles (Excel XP)

    You must open each of the workbooks in turn to read the Title property, then close it again:

    For i = 1 To .FoundFiles.Count
    With Workbooks.Open(.FoundFiles(i))
    FileName = .Name
    BookName = .BuiltinDocumentProperties("Title")
    Me.ComboBox2.AddItem FileName & " - " & BookName
    .Close SaveChanges:=False
    End With
    Next i

    Alternatively, see Dsofile.dll lets you edit Office document properties without Office in Visual Basic .NET 2003 and in Visual Basic .NET 2002. It works in VBA too, but you will have to study the examples provided in the download.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •