Need to save/write ranges to a workbook

[simonclivehughes]

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 Phillips]

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)

 

[simonclivehughes]

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.

 

[simonclivehughes]

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!

 

[Bob Phillips]

Simon,

I gave a hint to that in my original post.

--

HTH

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

 

[simonclivehughes]

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!

 

[simonclivehughes]

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!

 

[simonclivehughes]

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

 

[simonclivehughes]

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

 

[Bob Phillips]

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 _
Before:=Workbooks("Coax Designer II.xls").Sheets(1)
End If
End Sub


--

HTH

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

 

[simonclivehughes]

What's that saying?

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

Thanks!

 

[Tom Ogilvy]

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 _
Before:=Workbooks("Coax Designer II.xls").Sheets(1)
End If
End Sub

 

[Bob Phillips]

LOL!

I know the feeling.

Bob

 

[Dave Peterson]

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

(along with Tom's suggestion)