Updating an Excel doc that is already opened from another already opened Excel doc

[Genghis]

Hello,

If I have two to four excel documents already opened and I want to send updates to one of the other excel documents from one I am working in.

How can I get a list of excel documents that are already opened, and select one to get the handle of it so I can send information to it? I am looking for code that is dynamic enough that I don't have to hardcode a file name in it and I can use it to select one of the already opened documents that I may not know the location of in my system.

I hope this is clear enough for someone to help.

Thanks in advance,
Genghis

[westconn1]

you could use a listbox to get a list of the open workbooks like

listbox1.clear   '   start fresh
for each w in workbooks
    if not w.name = thisworkbook.name then listbox1.additem w.name
next

change name of listbox to suit
there would be many other methods to do what you are requesting, but this is a simple example

you can then use the click event of the listbox to get the selected workbook as part of your code

[Genghis]

Pete:

Thank you for your reply. I tried the following code and it didn't worked as I had hoped, it only found the workbook that the code was in, I had three other excel docoments open:

Dim w As Workbook


MsgBox Workbooks.Count

Sheet1.ListBox1.Clear ' start fresh
For Each w In Workbooks
If Not w.Name = ThisWorkbook.Name Then ListBox1.AddItem w.Name
Next

Please note that I am a beginner and I am not sure of the classes and which ones to dim. I was hoping for code that would provide a pop up window where I wouldn't need to have listbox predefined.

Also, do you know where I can get a complete listing of VBA classes with the elements and methods listed?

I hope you can still help,
Thank you,
Genghis

[GBeats]

Hi,

the listbox would have been just a container to put the results in westconn's post, you dont need to use it

your code should be something like this, bare in mind im not a vba fan
ill name everything with long names so you can see whats referring to what

-----------------------------------------------------------------------------------------------------
dim CurrentWorkbookLoopIsChecking as Excel.Workbook
dim MyWorkbookObjectSoICanAccessItEasilyWhenINeedTo as Excel.Workbook

For Each CurrentWorkbookLoopIsChecking In Workbooks <--this may be application.workbooks or something like it>
if CurrentWorkbookLoopIsChecking.Name = <-please enter the name of your workbook here, example: "SalesBook1"-> then
MyWorkbookObjectSoICanAccessItEasilyWhenINeedTo = CurrentWorkbookLoopIsChecking
end if
Next

then you can use this

MyWorkbookObjectSoICanAccessItEasilyWhenINeedTo.sheets("Sheet1").range("A1").Text = "Hello World" <- or is it .value :/ ->

hope this helps
sorry about the long names , please change them to something else

you can get anything you want online regarding classes, just do a search, "All VBA Classes"

[westconn1]

I was hoping for code that would provide a pop up window where I wouldn't need to have listbox predefined.

there is not really a pop up selection box predefined, you can use a msgbox or an inputbox, but neither would allow the user to select from a list of open workbooks
that was why i suggested a listbox, you could pop open a userform, just containing a listbox for the user to select from, close the userform on clicking the listbox

the code sample i posted should have listed all workbooks open in the same instance of excel, except the workbook containing the code

Also, do you know where I can get a complete listing of VBA classes with the elements and methods listed?

the object browser, in the VBA ide

[Genghis]

Hello Pete,

That's the problem, my workbooks are not open in the same instance of excel, but in different instances. Is there a way to get those listed and select from them?

Thanks GBeatss, your code did help explain things.

Genghis

[westconn1]

it is possible to automate workbooks in another instance of excel, using getobject, but i have never tried iterating through multiple instances

it should be possible to iterate through all running processes, determining which are excel workbooks, then listing them, then using getobject for required workbook to automate