SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003

[JingleRock]

The Subject pretty much describes my situation. There are 5 sheets in
'ThisWorkbook'; I want to save one of them as an .xls file (replacing
an existing file with the same name; preferably this newly-saved file
will have zero VBA code included), close it, continue with my Code.
(Later in my Code, I want to open this VBA-saved .xls file.)

Previous attempts: 1) using a 'Sheets("WORKPLACE").Copy' stmt; this
did not result in consistent stable results; 2) using AdvancedFilter
to copy the Sheet (omitting the CriteriaRange); I could not generate
an error-free stmt.

Help

 

[Dave Peterson]

I would use the same kind of thing as "sheets("workplace").copy" line as you
tried. If it didn't give stable results (whatever that means) for you, I would
expect the same problem for me.

Can you explain what happened when you used it?

=====
An (uglier) alternative would be to create a new workbook (single sheet only???)
and copy the data and formulas (or values) to the the worksheet in that new
workbook.

But if that Workplace worksheet doesn't have any code behind it, this doesn't
sound efficient to me.

(formatting, page setup, freeze panes/splits, ... all that stuff would have to
be reapplied.)

 

[Don Guillett]

Sub saveworksheetandgooon()
Application.DisplayAlerts = False
Sheets("Sheet10").Copy
ActiveWorkbook.SaveAs "Sheet10.xls"
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

 

[JingleRock]

Sub saveworksheetandgooon()
Application.DisplayAlerts = False
Sheets("Sheet10").Copy
ActiveWorkbook.SaveAs "Sheet10.xls" '<<< this has to be a WB other than the Active one -- see my code below
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

Dave and Don,

Thanks to each of you for your comments. A snippet of my code:

Worksheets("WORKPLACE").Activate '<<< one of 5 Sheets
Set VBA_CodeWB = ThisWorkbook '<<< there is no code in
'ThisWorkbook'
' and no
code in any of the Sheet modules
Set newWB = Workbooks.Open(myPath & "MMF_RATINGS-DAILY_NEW.xlS")
VBA_CodeWB.Activate
VBA_CodeWB.ActiveSheet.Copy
newWB.SaveAs (myPath & "MMF_RATINGS-DAILY_NEW.xlS")
newWB.Close
VBA_CodeWB.Activate

Good news and bad first, the bad news -- instead of the VBA-
saved file being the name the Code specifies, it is 'Book 1' (a
totally new WB file); and the good news -- there is no VBA code
included.

Comments?

 

[Don Guillett]

I wrote mine based on your original post. Did you try it?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Sub saveworksheetandgooon()
Application.DisplayAlerts = False
Sheets("Sheet10").Copy
ActiveWorkbook.SaveAs "Sheet10.xls" '<<< this has to be a WB other
than the Active one -- see my code below
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

Dave and Don,

Thanks to each of you for your comments. A snippet of my code:

Worksheets("WORKPLACE").Activate '<<< one of 5 Sheets
Set VBA_CodeWB = ThisWorkbook '<<< there is no code in
'ThisWorkbook'
' and no
code in any of the Sheet modules
Set newWB = Workbooks.Open(myPath & "MMF_RATINGS-DAILY_NEW.xlS")
VBA_CodeWB.Activate
VBA_CodeWB.ActiveSheet.Copy
newWB.SaveAs (myPath & "MMF_RATINGS-DAILY_NEW.xlS")
newWB.Close
VBA_CodeWB.Activate

Good news and bad first, the bad news -- instead of the VBA-
saved file being the name the Code specifies, it is 'Book 1' (a
totally new WB file); and the good news -- there is no VBA code
included.

Comments?

 

[Dave Peterson]

How about:

dim ActCell as range
set ActCell = activecell

thisworkbook.Worksheets("WORKPLACE").Copy 'to a new workbook

with activesheet.parent
application.displayalerts = false 'no overwrite prompt!
.saveas filename:=myPath & "MMF_RATINGS-DAILY_NEW.xlS", _
fileformat:=xlworkbooknormal
application.displayalerts = true
.close 'why close if you're going to open later?
end with

application.goto actcell

=========
I hope myPath contains a nice value.

 

[JingleRock]

Don,

Is there something special about the name "Sheet10"?
See my following post.

Chuck

 

[JingleRock]

How about:

dim ActCell as range
set ActCell = activecell

thisworkbook.Worksheets("WORKPLACE").Copy 'to a new workbook

with activesheet.parent
  application.displayalerts = false 'no overwrite prompt!
  .saveas filename:=myPath & "MMF_RATINGS-DAILY_NEW.xlS", _
      fileformat:=xlworkbooknormal
  application.displayalerts = true
  .close 'why close if you're going to open later?
end with

application.goto actcell

=========
I hope myPath contains a nice value.

Dave,

Thanks very much. With a few modifications (see below), your code
appears to be working.

This is my current snippet: (I apologize; I should have indicated
earlier that I am setting DisplayAlerts and Enable Events to 'False'
at the beginning of my Code and to 'True' at the end of my Code.)

Worksheets("WORKPLACE").Activate '<<< one of 5 Sheets
Set VBA_CodeWB = ThisWorkbook '<<< there is no code in
'ThisWorkbook'
' and no
code in any of the Sheet modules
VBA_CodeWB.Activate
VBA_CodeWB.Worksheets("WORKPLACE").Copy
With ActiveSheet.Parent
.SaveAs Filename:=(myPath & "MMF_RATINGS-DAILY_NEW.xlS"),
_
FileFormat:=xlWorkbookNormal
End With
Set newWB = Workbooks.Open(myPath & "MMF_RATINGS-DAILY_NEW.xlS")
'<<< IS THERE A BETTER
_
WAY TO DO THIS?????????
VBA_CodeWB.Activate

I agree; there is no need to close this VBA-created file.

What is the story about 'ActCell'? -- what purpose does it serve?

Thanks again.

P.S.: And no VBA code in the VBA-created file -- YEA!

 

[Dave Peterson]

I wanted to return to where I started. ActCell is a variable that represents
the activecell.

I didn't activate a workbook or select a worksheet, but copying that worksheet
does change the activesheet.

application.goto actcell
returns to where I was when I started.

Why don't you want to use ThisWorkbook in your code? I don't see much benefit
in code like this:

Set VBA_CodeWB = ThisWorkbook
VBA_CodeWB.Worksheets("WORKPLACE").Copy

Why not just:
thisworkbook.worksheets("workplace").copy

And you don't need to activate the workbook, then select the sheet to copy it.
Just copy it from where it sits.



dim ActCell as range
set ActCell = activecell

thisworkbook.Worksheets("WORKPLACE").Copy 'to a new workbook
set newwb = activesheet.parent

with newwb
application.displayalerts = false 'no overwrite prompt!
.saveas filename:=myPath & "MMF_RATINGS-DAILY_NEW.xlS", _
fileformat:=xlworkbooknormal
application.displayalerts = true
end with

application.goto actcell

========
And the only way this would result in the new workbook having code would be if
that workplace sheet had code it its code module -- or you modified the workbook
that's used as the standard workbook.

 

[JingleRock]

One more question: the following is a snippet of code that I have
been using to determine whether this is the first time today that
'VBA_CodeWB' has been executed or not. (It is important that
'VBA_CodeWB' not be saved when the project is processed.)

'NAME THE FILE USED AS A TEST OF HAVING ALREADY RUN TODAY
BeforeChanges = (myPath & "MMF_RATINGS-DAILY_NEW.xlS")

'THIS IS 'Last Save Date' OF 'BEFORE CHANGES' FILE
LSD_BeforeChanges_File = DateValue(FileDateTime(BeforeChanges))

If LSD_BeforeChanges_File <> Date Then

'MACRO HAS NOT YET RUN TODAY
Set oldWB = Workbooks.Open(myPath & "MMF_RATINGS-
DAILY_NEW.xlS")

'ARCHIVE 'Start of Day' (SOD) FILE
oldWB.SaveAs (myPath & "MMF_RATINGS-DAILY_SOD.xlS")

Else

'MACRO HAS PREVIOUSLY RUN AT LEAST ONCE TODAY (SOD is 'Start
of Day')
Set oldWB = Workbooks.Open(myPath & "MMF_RATINGS-
DAILY_SOD.xlS")

End If

The above Code will not work (If stmt will always yield 'False' and go
to 'Else') with the new way to generate the VBA-created file. Any
ideas?

 

[JingleRock]

Dave,

Thanks a ton for all of your tips; my Code is now significantly
simplified as a result. And I have learned a lot.
Two final queries:

One was at the end of my preceding post about 'Last Saved Date' (used
in determining whether 'ThisWorkbook', which is not saved, has
previously executed on any given day).

The other is that EXCEL is hanging in the background of my PC,
requiring me to go into 'Task Manager' and shut it down -- after doing
this, if I open a blank copy of EXCEL, the 'ThisWorkbook' file is
typically not on the left side of the display requiring it to be
closed again. Also, this process is not cumulative, meaning that I
can execute the code multiple times and there is never more than one
instance of EXCEL in 'Task Manager'.

I am tired; I am going to bed. If you have any MORE comments, they
would be appreciated.

 

[Dave Peterson]

Are you starting another instance of excel?

Maybe you aren't releasing all your object variable "set yyyy = nothing" before
you close the application????

 

[Dave Peterson]

This worked for me.

Dim BeforeChanges As String
Dim myPath As String
Dim LSD_BeforeChanges_File As Date
Dim OldWB As Workbook

myPath = "c:\my documents\excel\"
BeforeChanges = (myPath & "book1.xls")

LSD_BeforeChanges_File = DateValue(FileDateTime(BeforeChanges))
Set OldWB = Workbooks.Open(BeforeChanges)
If LSD_BeforeChanges_File <> Date Then
OldWB.Save
End If

If you have that beforechanges workbook open, then the date may not be what you
expect.

 

[Don Guillett]

Your original post said you wanted save a sheet as a wb and close it. I used
sheet10 as an EXAMPLE to save it as a wb>name it and/or replace the original
with the same name> and close it.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Don,

Is there something special about the name "Sheet10"?
See my following post.

Chuck

 

[JingleRock]

Dave and Don,

I guess this is a wrap-up post on a very successful, for me, query
session. Everything is working great -- YEA!
Thanks very much.

A few comments:

The XCL "hangin" issue was due to 'Ap.EnableEvents' not being reset to
'True' immediately prior to 'Ap.Quit'. Sorry about that.

oldWB cannot be the trigger for LastSavedDate because it is never
saved until after the If Stmt. However, I think that the SOD (Start
of Day) file will work as a trigger; it is archived a maximum of once
daily for each calendar day.

Thanks again.

 

[Dave Peterson]

Glad you got it working.

Dave and Don,

I guess this is a wrap-up post on a very successful, for me, query
session. Everything is working great -- YEA!
Thanks very much.

A few comments:

The XCL "hangin" issue was due to 'Ap.EnableEvents' not being reset to
'True' immediately prior to 'Ap.Quit'. Sorry about that.

oldWB cannot be the trigger for LastSavedDate because it is never
saved until after the If Stmt. However, I think that the SOD (Start
of Day) file will work as a trigger; it is archived a maximum of once
daily for each calendar day.

Thanks again.

 

[Don Guillett]

Always good to post your final solution for the archives