list of Opened excel files

SDondeti · #1 04-13-2011, 09:23 AM

Hi,

I got some problem in finding the names of all excel opened files using word VBA.

Following code is working in excel vba but i am not able to do it in word vba. Can some one help me. Thanks in advance.

Dim wb As Workbook
For Each wb In Application.Workbooks
MsgBox wb.Name
Next

Thanks,
SDondeti

macropod · #2 04-13-2011, 03:59 PM

Hi SDondeti,

Try something along the lines of:

Code:

Sub ListOpenExcelFiles()
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 "No Excel Files are open (Excel is not running)"
  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
End Sub

SDondeti · #3 04-15-2011, 08:31 AM

Thank you Paul.

Finally i am able to manage instanse create object and able to retrieve the names of all active excel file names.

Thank you.

goose0701 · #4 02-21-2018, 03:40 PM

Hello All!

I know this one is solved, but I didn't want to start a whole new thread for the same issue (pretty close).

I used the code above with a tweak to populate a listbox, but it is only picking up 1 open excel file. it is not doing each.

here is my code:

Code:

Public Sub UserForm_Initialize()
    
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
  filelist.AddItem ("No Excel Files are open (Excel is not running)")
  Exit Sub
End If
For Each xlWkBk In ObjXL.Workbooks
filelist.AddItem xlWkBk.Name
Next xlWkBk

Set ObjXL = Nothing
    
End Sub

does anyone know where I am going wrong on this one?

macropod · #5 02-21-2018, 05:07 PM

The code as posted works for me.

goose0701 · #6 02-23-2018, 10:29 AM

No way!

Thank you for the quick response macropod! you're the best!

If I have multiple excel docs open, only 1 shows up in my list. I have to close the other docs and reopen the user form in order to get to the one I want.

any ideas if I am running a setting wrong? I have the Excel 14.0 library reference included already.

macropod · #7 02-23-2018, 01:33 PM

As I said, the code works for me and lists multiple files. No Excel reference is needed, as late binding is used. Perhaps the issue is that your userform has already created its own Excel session and is therefore only finding files running under that session?

goose0701 · #8 05-10-2018, 02:16 PM

Quote:

Originally Posted by macropod

As I said, the code works for me and lists multiple files. No Excel reference is needed, as late binding is used. Perhaps the issue is that your userform has already created its own Excel session and is therefore only finding files running under that session?

Better late than never. I am sorry I haven't had time to mess with this project lately.

So how would my userform create it's own Excel session? This is the only code I have in the UserForm Initialize sub. So I am unsure where to look.

macropod · #9 05-10-2018, 02:25 PM

Possible misdirection there - the userform code you posted cannot create a new Excel session. That said, in Excel it's possible to have more than one session running. If that's the situation on your system, the code will only find files open in the Excel session it attaches to (probably the last one started). If you open the Task Manager, you should be able to see how many Excel sessions you have running.

goose0701 · #10 05-10-2018, 02:49 PM

Quote:

Originally Posted by macropod

Possible misdirection there - the userform code you posted cannot create a new Excel session. That said, in Excel it's possible to have more than one session running. If that's the situation on your system, the code will only find files open in the Excel session it attaches to (probably the last one started). If you open the Task Manager, you should be able to see how many Excel sessions you have running.

ahhhh!! that is exactly it. I have them running in separate instances always.

Is there a way around this to show all excel instances?

macropod · #11 05-10-2018, 03:52 PM

Not that I am familiar with.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
seach an Excel list elements in word document	7ajar	Word VBA	5	03-16-2011 12:38 PM
Importing many vcf files in excel	shiflerg	Excel	0	01-26-2011 09:16 AM
Creating mailing labels from list in excel	wordnoob2	Mail Merge	0	12-13-2010 03:46 PM
Opening multiple Excel files within the same Excel window.	lost9471	Excel	2	05-01-2010 01:57 PM
Creating a Distrubution List from Excel	webphotogeek	Outlook	0	01-10-2008 11:21 AM