Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook | Excel Programming | |||
Selecting data from another workbook | Excel Programming | |||
Selecting Specific Data to copy/move to a new sheet | Excel Programming | |||
Selecting data from 1 workbook to copy and paste to a 2nd workbook | Excel Programming | |||
VBA: selecting sheets and copy them to a workbook | Excel Programming |