Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2014
    Posts
    10

    Unanswered: [SOLVED] Populate listbox with names of opened excel workbooks

    Hi,

    I need Access to check for opened excel workbooks and then populate a listbox with the names of the workbooks.
    This is for getting the user to determine what workbook access then should continue processing.

    I found the following piece of code which is a start, but it lumps the results together in a string. I need separate rows in the listbox.

    Dim ObjXL As Object, xlWkBk, StrWkBks As String
    ' Test to see if there is a copy of Microsoft Excel already running.
    On Error Resume Next
    Set ObjXL = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
    MsgBox "There are no opened Excel files"
    Exit Sub
    End If
    For Each xlWkBk In ObjXL.Workbooks
    StrWkBks = StrWkBks & vbCr & xlWkBk.Name
    Next

    MsgBox "The following Excel Files are open:" & StrWkBks

    Set ObjXL = Nothing


    Can anyone help me out?
    Last edited by Marcustofeles; 06-04-14 at 04:46.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Use the ; symbol to separate the entries for a listbox rowsource
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    10
    healdem,

    Thanks! As soon as I read your post I connected the dots.
    Here's the resulting code:

    Dim ObjXL As Object, xlWkBk, StrWkBks As String
    ' Test to see if there is a copy of Microsoft Excel already running.
    On Error Resume Next
    Set ObjXL = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
    MsgBox "There are no opened Excel files"
    Exit Sub
    End If

    For Each xlWkBk In ObjXL.Workbooks
    StrWkBks = StrWkBks & xlWkBk.Name & ";"
    Next

    Me.ListBox.RowSource = StrWkBks

    Set ObjXL = Nothing

  4. #4
    Join Date
    Jun 2014
    Posts
    10
    Hi healdem,

    I wrote a reply shortly after you posted but it didn't get through for some reason.

    Thank you. As soon as I read your post I facepalmed and connected the dots.
    Here is the resulting code:
    Code:
    Dim ObjXL As Object, xlWkBk, StrWkBks As String
    ' Test to see if there is a copy of Microsoft Excel already running.
    On Error Resume Next
    Set ObjXL = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
      MsgBox "There are no opened Excel files"
      Exit Sub
    End If
    
    For Each xlWkBk In ObjXL.Workbooks
    StrWkBks = StrWkBks & xlWkBk.Name & ";"
    
    Next
    
    Me.YourListbox.RowSource = StrWkBks
    
    Set ObjXL = Nothing

    Perhaps I ought to start a new thread but does anyone now I have move forward with referencing the workbook in question? Let's say for instance that I simply want to delete cell A1. Any pointers?

  5. #5
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi Marcustofeles,

    It looks like you're using a message box, not a list box. If you change vbCr to vbCrLf, I think you'll get what you're looking for.

  6. #6
    Join Date
    Jun 2014
    Posts
    10
    *TEST*

    This is my third try replying in this thread!

  7. #7
    Join Date
    Jun 2014
    Posts
    10
    Ok, now my post was submitted right away.

    Thanks healdem!

    Resulting code:

    Code:
    Dim ObjXL As Object, xlWkBk, StrWkBks As String
    ' Test to see if there is a copy of Microsoft Excel already running.
    On Error Resume Next
    Set ObjXL = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
      MsgBox "There are no opened Excel files"
      Exit Sub
    End If
    
    For Each xlWkBk In ObjXL.Workbooks
      StrWkBks = StrWkBks & xlWkBk.Name & ";"
    Next
    
    Me.Yourlistbox.RowSource = StrWkBks
    
    Set ObjXL = Nothing

  8. #8
    Join Date
    Jun 2014
    Posts
    10
    And there we go again, couldn't post the resulting code.

    Anyway, thanks healdem!

Posting Permissions

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