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:
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.
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.