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

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

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.

Well, that was a little premature. Despite fixing the syntax errors, it
obviously doesn't work. I guess I still need a little help with this
one!

Sorry Bob, not to be too obtuse, but what I'd really like to do is the
following:

1. Prompt for the worksheet filename (no problem here, I can see how
to use the GetOpenFilename method)

2. Open the workbook (can't seem to find the right method for this)

3. Copy the worksheet (named "Copy") from that workbook to the main
workbook (named "Coax Designer II.xls")

4. Then copy the ranges from it (again, no problem here... I already
have this working).

Thanks!

Well, this looks like it:

Sub DoFileRead()
Dim fileReadName

' Delete existing sheet "Copy"
Sheets("Copy").Select
ActiveWindow.SelectedSheets.Delete

' Get the filename
fileReadName = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")
If fileReadName < False Then
Workbooks.Open fileReadName
End If

' Copy the sheet to the main app
Sheets("Copy").Select
Sheets("Copy").Copy After:=Workbooks("Designer
_1.23.xls").Worksheets("Coax Cables")
End Sub

All I need to do now is do some checking to be sure the "Copy" sheet
existing to start with. Thanks for all the help.

Simon

Well, this looks like it:

Sub DoFileRead()
Dim fileReadName

' Delete existing sheet "Copy"
Sheets("Copy").Select
ActiveWindow.SelectedSheets.Delete

' Get the filename
fileReadName = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")
If fileReadName < False Then
Workbooks.Open fileReadName
End If

' Copy the sheet to the main app
Sheets("Copy").Select
Sheets("Copy").Copy After:=Workbooks("Designer
_1.23.xls").Worksheets("Coax Cables")
End Sub

All I need to do now is do some checking to be sure the "Copy" sheet
existing to start with. Thanks for all the help.

Simon

It's a bit pointless trying to use the SaveAs method to open a file!

Sub DoFileRead()
Dim fileReadName

Application.GetOpenFilename ("Excel Files (*.xls), *.xls")
If fileReadName < False Then
ActiveWorkbook.Open Filename:=fileReadName
Activeworkbook.Worksheets("Copy").Copy _
Befo=Workbooks("Coax Designer II.xls").Sheets(1)
End If
End Sub


--

HTH

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


wrote in message
oups.com...
Sorry Bob, not to be too obtuse, but what I'd really like to do is the
following:

1. Prompt for the worksheet filename (no problem here, I can see how
to use the GetOpenFilename method)

2. Open the workbook (can't seem to find the right method for this)

3. Copy the worksheet (named "Copy") from that workbook to the main
workbook (named "Coax Designer II.xls")

4. Then copy the ranges from it (again, no problem here... I already
have this working).

Thanks!

What's that saying?

"The hurrier I go, the behinder I get!"

Thanks!

Might also want to populate fileReadName

Sub DoFileRead()
Dim fileReadName

fileReadName = Application.GetOpenFilename ("Excel Files (*.xls), *.xls")
If fileReadName < False Then
ActiveWorkbook.Open Filename:=fileReadName
Activeworkbook.Worksheets("Copy").Copy _
Befo=Workbooks("Coax Designer II.xls").Sheets(1)
End If
End Sub

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
It's a bit pointless trying to use the SaveAs method to open a file!

Sub DoFileRead()
Dim fileReadName

Application.GetOpenFilename ("Excel Files (*.xls), *.xls")
If fileReadName < False Then
ActiveWorkbook.Open Filename:=fileReadName
Activeworkbook.Worksheets("Copy").Copy _
Befo=Workbooks("Coax Designer II.xls").Sheets(1)
End If
End Sub


--

HTH

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


wrote in message
oups.com...
Sorry Bob, not to be too obtuse, but what I'd really like to do is the
following:

1. Prompt for the worksheet filename (no problem here, I can see how
to use the GetOpenFilename method)

2. Open the workbook (can't seem to find the right method for this)

3. Copy the worksheet (named "Copy") from that workbook to the main
workbook (named "Coax Designer II.xls")

4. Then copy the ranges from it (again, no problem here... I already
have this working).

Thanks!

LOL!

I know the feeling.

Bob

wrote in message
oups.com...
What's that saying?

"The hurrier I go, the behinder I get!"

Thanks!

Another typo:
ActiveWorkbook.Open Filename:=fileReadName
should be:
Workbooks.Open Filename:=fileReadName

(along with Tom's suggestion)

Bob Phillips wrote:

It's a bit pointless trying to use the SaveAs method to open a file!

Sub DoFileRead()
Dim fileReadName

Application.GetOpenFilename ("Excel Files (*.xls), *.xls")
If fileReadName < False Then
ActiveWorkbook.Open Filename:=fileReadName
Activeworkbook.Worksheets("Copy").Copy _
Befo=Workbooks("Coax Designer II.xls").Sheets(1)
End If
End Sub

--

HTH

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

wrote in message
oups.com...
Sorry Bob, not to be too obtuse, but what I'd really like to do is the
following:

1. Prompt for the worksheet filename (no problem here, I can see how
to use the GetOpenFilename method)

2. Open the workbook (can't seem to find the right method for this)

3. Copy the worksheet (named "Copy") from that workbook to the main
workbook (named "Coax Designer II.xls")

4. Then copy the ranges from it (again, no problem here... I already
have this working).

Thanks!


--

Dave Peterson