Reference a workbook chosen by a user from a userform

[Mark Hanley]

I am working on a spreadsheet from which a user can choose to import
data from a saved workbook. Once the user has chosen the import
workbook through a file chooser dialog, it is opened and checked for
validity. The user then chooses the subset of the data they want
through a simple userform.

How can I get a reference to the workbook that has been chosen and
opened so that I can act upon it from the userform or the procedure
that calls the userform? There is no code in the import workbook as it
is just raw data.

Is this a place for a global variable?

Thanks.

Mark

 

[Tim Williams]

How is the user choosing the workbook, and how are you opening it ?

It would be helpful to show some of the code you already have.

Tim

 

[JLGWhiz]

You could use a public variable. Declare it outside the procedure in the
public module at the top.

Public myWBVar As Workbook

Sub somename()
'Your procedure code
End sub

The time to Set the workbook to the variable is when you open it.


'code to open workbook
yWBVar = ActiveWorkbook

Now your public variable is initialized with the workbook containing the
data, so that if you make another workbook active, myWBVar will still refer
to the one with the data until you Set it to another value.

 

[Gary''s Student]

Set a reference when the workbook is opened:

Dim wb As Workbook, s As String
s = Application.GetOpenFilename()
Set wb = Workbooks.Open(s)

 

[RB Smissaert]

Another option:

Sub test()

Dim strFilePath As String

On Error Resume Next 'for if nil selected
With Application.FileDialog(msoFileDialogOpen)
.Filters.Add "Excel workbooks", "*.xls"
.FilterIndex = .Filters.Count
.Show
.Execute
strFilePath = .SelectedItems.Item(1)
End With

If Len(strFilePath) = 0 Then
Exit Sub
End If

'now do whatever is needed with the file strFilePath
MsgBox strFilePath, , "opened workbook"

End Sub


RBS