Need help with code to save/open

[bstevens]

I have VBA code called "savetoron" in a spreadsheet (Max OSX, Excel
2004) which reads some data from certain cells and constructs a
filename, then saves that spreadsheet, with that filename, to a certain
location, and closes that file on the screen. It reads like this:

Sub savetoron()
fname = "(" & Range("B1") & ")" & Range("D1") & Range("H1") & "_" &
Range("T1") & ".xls"
pathname = "Volumes:ACSP:IDEACoordination:Funding Team:WEBSAS
Review:Ron Morgan:06-07:"
ActiveWorkbook.SaveAs Filename:=pathname & fname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End Sub

What I'm trying to do is modify this code so that it would not only
close the file, it would also open the original file called
"06-07main.xls"
which is on my desktop. I think that code should look like this:

Workbooks.Open Filename:= _
"Mac OS X HD:Users:rmorganesktop:06-07main.xls"

The task I'm trying to facilitate: the user starts off with
06-07main.xls, which is a blank template spreadsheet. He fills it in
with some data, then hits a "Save" button/object which executes the
"savetoron" code above.

Then it re-opens a fresh copy of 06-07main.xls, he fills it in, hits
the save button, and it gets saved, closes, and another fresh one
opens, etc. etc.

I can NOT find a place to insert the Workbooks.Open code so it will
work. Can somebody help?

Thanks,
Ron M.

 

[arthurjr07]

Sub savetoron()
fname = "(" & Range("B1") & ")" & Range("D1") & Range("H1") & "_" &
Range("T1") & ".xls"
pathname = "Volumes:ACSP:IDEACoordination:Funding Team:WEBSAS
Review:Ron Morgan:06-07:"
ActiveWorkbook.SaveAs Filename:=pathname & fname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Dim wb As New Workbook
Dim wb_old As New Workbook
Set wb_old = Application.ActiveWorkbook
Set wb = Application.Workbooks.Open("C:\book1.xls")
wb_old.Close

''''''''''''''''''''''ActiveWorkbook.Close 'Remove this
End Sub


hope this will help

 

[Tom Ogilvy]

Sub savetoron()
fname = "(" & Range("B1") & ")" & Range("D1") & Range("H1") & "_" &
Range("T1") & ".xls"
pathname = "Volumes:ACSP:IDEACoordination:Funding Team:WEBSAS
Review:Ron Morgan:06-07:"
ActiveWorkbook.SaveAs Filename:=pathname & fname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
set bk = ActiveWorkbook
Workbooks.Open Filename:= _
"Mac OS X HD:Users:rmorganesktop:06-07main.xls"
bk.Close SaveChange:=False
End Sub

 

[bstevens]

Thanks. Arthur's worked, Tom's didn't.

Ron

 

[Tom Ogilvy]

Sorry, but I am hard pressed to see a difference.

 

[bstevens]

Tom: when I used yours, it produced an error - a 1040, I think - and in
the debugger, that last line, "bk.Close SaveChange:=False":" was
highlighted.

Ron

 

[shawnRheal]

Hello it's simple to open a form you would use the name of the form:
Example
MyForm.Show

to Save the Change to the file you would use the code like this:
Example
Use the name of your time sheet for instance say your time sheet is
called,
myitem.

myitem.Save

to specify where you want to save this work sheet you will need to use
an object called a browser object. This object could be used to specify
where you want to save this item or work.

 

[Tom Ogilvy]

Looks like a typo

bk.Close SaveChange:=False
should be

bk.Close SaveChanges:=False

 

[bstevens]

Thanks, Tom. I guess you're off the hook... (-; (-;

Ron