I have an application in which I currently save certain ranges (via
VBA) to a separate sheet within the app which can then be manually
saved to a separate workbook. By reversing the procdure, I can read in
data to the app. Clumsy!

What I'd like is to have the subroutine prompt for a file name and then
create a new book and then copy the ranges to the new book. Conversely,
I need a routine that allows me to (again, via dialog) to specify a
file that would then be opened and the data read back to the app.
Any help would be really appreciated.

Simon

Simon,

Here is one way to save it. It creates a new workbook from sheet1, and then
uses the SaveAs dialog to get a filename, which if okay, is saved.

Lookup GetOpenFilename in Help for a similar technique in oipening itr
again.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
I have an application in which I currently save certain ranges (via
VBA) to a separate sheet within the app which can then be manually
saved to a separate workbook. By reversing the procdure, I can read in
data to the app. Clumsy!

What I'd like is to have the subroutine prompt for a file name and then
create a new book and then copy the ranges to the new book. Conversely,
I need a routine that allows me to (again, via dialog) to specify a
file that would then be opened and the data read back to the app.
Any help would be really appreciated.

Simon

Bob, I don't find your answer? Were you attaching a file?

"Bob Phillips" wrote:

Simon,

Here is one way to save it. It creates a new workbook from sheet1, and then
uses the SaveAs dialog to get a filename, which if okay, is saved.

Lookup GetOpenFilename in Help for a similar technique in oipening itr
again.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
I have an application in which I currently save certain ranges (via
VBA) to a separate sheet within the app which can then be manually
saved to a separate workbook. By reversing the procdure, I can read in
data to the app. Clumsy!

What I'd like is to have the subroutine prompt for a file name and then
create a new book and then copy the ranges to the new book. Conversely,
I need a routine that allows me to (again, via dialog) to specify a
file that would then be opened and the data read back to the app.
Any help would be really appreciated.

Simon

No, just messed up the pasting.

Here is thecode I in tended

Dim fileSaveName

Sheets("Sheet1").Copy
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName < False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"gocush" wrote in message
...
Bob, I don't find your answer? Were you attaching a file?

"Bob Phillips" wrote:

Simon,

Here is one way to save it. It creates a new workbook from sheet1, and
then
uses the SaveAs dialog to get a filename, which if okay, is saved.

Lookup GetOpenFilename in Help for a similar technique in oipening itr
again.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
I have an application in which I currently save certain ranges (via
VBA) to a separate sheet within the app which can then be manually
saved to a separate workbook. By reversing the procdure, I can read in
data to the app. Clumsy!

What I'd like is to have the subroutine prompt for a file name and
then
create a new book and then copy the ranges to the new book.
Conversely,
I need a routine that allows me to (again, via dialog) to specify a
file that would then be opened and the data read back to the app.
Any help would be really appreciated.

Simon

Bob,

That worked perfectly... now I just need the other part, the routine to
prompt for the file name and load it so I can then save the ranges back
again.

Actually, I was able to do it with the following:

Sub DoFileRead()
Dim fileReadName

Application.GetOpenFilename ("Excel Files (*.xls), *.xls")
If fileReadName < False Then
ActiveWorkbook.SaveAs Filename:=fileReadName
End If
End Sub

Many thanks for all the help!

Simon,

I gave a hint to that in my original post.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Bob,

That worked perfectly... now I just need the other part, the routine to
prompt for the file name and load it so I can then save the ranges back
again.

Here are a couple of lines that may help you get started

Application.Dialogs(xlDialogSaveAs).Show
this will bring up the SaveAs dialog where you enter the name of the new
filename and directory

or if you already know the name of the new file try:
ActiveSheet.SaveAs Filename:= _
"MyfileName", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Change the "MyfileName" to a variable including a path.
This will save the active SHEET as a new workbook.


" wrote:

I have an application in which I currently save certain ranges (via
VBA) to a separate sheet within the app which can then be manually
saved to a separate workbook. By reversing the procdure, I can read in
data to the app. Clumsy!

What I'd like is to have the subroutine prompt for a file name and then
create a new book and then copy the ranges to the new book. Conversely,
I need a routine that allows me to (again, via dialog) to specify a
file that would then be opened and the data read back to the app.
Any help would be really appreciated.

Simon