Selecting a workbook to copy data from
 

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

Selecting a workbook to copy data from
 

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
--
Charles Chickering

"A good example is twice the value of good advice."


"Barb Reinhardt" wrote:

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

Selecting a workbook to copy data from
 

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

Use GetOpenfileName in earlier versions.

--
Regards,
Tom Ogilvy


"Charles Chickering" wrote:

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
--
Charles Chickering

"A good example is twice the value of good advice."


"Barb Reinhardt" wrote:

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

Selecting a workbook to copy data from
 

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

"A good example is twice the value of good advice."


"Tom Ogilvy" wrote:

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

Use GetOpenfileName in earlier versions.

--
Regards,
Tom Ogilvy


"Charles Chickering" wrote:

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
--
Charles Chickering

"A good example is twice the value of good advice."


"Barb Reinhardt" wrote:

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

Selecting a workbook to copy data from
 

xl2k

Charles Chickering wrote:

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

"A good example is twice the value of good advice."

"Tom Ogilvy" wrote:

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

Use GetOpenfileName in earlier versions.

--
Regards,
Tom Ogilvy


"Charles Chickering" wrote:

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
--
Charles Chickering

"A good example is twice the value of good advice."


"Barb Reinhardt" wrote:

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

--

Dave Peterson

Selecting a workbook to copy data from
 

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

--
Regards,
Tom Ogilvy

"Charles Chickering" wrote in
message ...
Tom, I did not realize that FileDialog was that recent of an addition. Do
you
recall when the added the InStrRev function?
--
Charles Chickering

"A good example is twice the value of good advice."


"Tom Ogilvy" wrote:

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

Use GetOpenfileName in earlier versions.

--
Regards,
Tom Ogilvy


"Charles Chickering" wrote:

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
--
Charles Chickering

"A good example is twice the value of good advice."


"Barb Reinhardt" wrote:

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