Macro to select open filename

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,718
I'm working on a macro that gets data from another workbook, does the calculations on the macro workbook and returns the values to the data workbook.

The "Data Workbook" is supposed to be open and the data is supposed to be found from the active sheet.

I'm trying to use an UserForm to let the user select which of the open workbooks is the one where the data is found:
Code:
Private Sub UserForm_Initialize()
Dim WB As Workbook
Dim i As Integer

'Get rid of old items:
For i = cbFileNames.ListCount - 1 To 0 Step -1
    cbFileNames.RemoveItem (i)
Next i

'Add new items:
For Each WB In Workbooks
    If WB.Name <> ThisWorkbook.Name Then
        cbFileNames.AddItem WB.Name
    End If
Next WB

End Sub

This code seems to work most of the times but sometimes it seems like it's not finding all the open workbooks: I know for sure the file I want to use is open but VBA can't seem to find it.

Could it be there's more than one Excel Application running at the same time because even the Window-file seems to have different files showing depending if I'm looking at it from the macro file window or the "missing data file's" window (= doesn't show the macro file). How could I make sure the macro does find all the open (.xls) filenames?

At first I was thinking of using the GetOpenFilename method to return the file name but most of the times the actual data file might not be saved yet and I don't think GetOpenFilename works on unsaved workbooks.

Also, what would be the best way to get the data from the User Form back to the main macro? I'm using Public variable for the DataFile name in my code but I'm thinking there might be better ways to do it. The User Form is opened from the middle of my actual macro and it only has that single ComboBox (+ OK & Cancel buttons).

I'm using Excel 2003 on XP.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If your ComboBox doesn't include all the workbooks that you think are open, the missing ones must be open in a different instance of Excel. They all need to be open in the same instance.
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,718
How can I make sure all the open workbooks are open in the same instance? Usually they're all opened by clicking on the file from the Explorer window (not from the Excel's Open File dialog) or from an email attachment and most of the times Excel seems to open them in the same instance but not always.

Is it possible to kill all the other instances and reopen all the open files in the single instance (=where the macro is running)?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry, I don't know if that's possible.

It would be better to find out why the workbooks are opening in a new instance of Excel and fix it. The usual cause is that 'Ignore other applications' is checked under Tools|Options|General tab.
 

Forum statistics

Threads
1,147,821
Messages
5,743,396
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top