Selecting a workbook to copy data from

[Guest]

We have a workbook with a lot of user entered data and we want to set up a
macro to copy it to a new version of a tool (as it's released). Within the
code, I want to refer to the new version as "aWB". I want the user to
select the "oWB" that they want to copy their data from. How would I do
that with VBA?

Thanks

 

[Guest]

Barb, is the workbook open already or do we need to find and open it? This
solution should accomodate either:
Sub TestingOPen()
Dim oWB As Workbook
Dim sFile As String
Dim ShortName As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls"
.FilterIndex = 1
.Title = "Please Select File to open"
If .Show = False Then Exit Sub
sFile = .SelectedItems(1)
End With
ShortName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))
On Error Resume Next
Set oWB = Workbooks(ShortName)
On Error GoTo 0
If oWB Is Nothing Then _
Set oWB = Workbooks.Open(sFile)
End Sub

 

[Guest]

Just to add, that will work in xl2002 and later.

Use GetOpenfileName in earlier versions.

 

[Guest]

Tom, I did not realize that FileDialog was that recent of an addition. Do you
recall when the added the InStrRev function?

 

[Dave Peterson]

xl2k

Tom, I did not realize that FileDialog was that recent of an addition. Do you
recall when the added the InStrRev function?

 

[Tom Ogilvy]

Just to add:
More precisely, VBA6 which was introduced in xl2000.