Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
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.) JingleRock wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
Sub saveworksheetandgooon()
Application.DisplayAlerts = False Sheets("Sheet10").Copy ActiveWorkbook.SaveAs "Sheet10.xls" ActiveWindow.Close Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "JingleRock" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
On Jul 18, 11:23*am, "Don Guillett" wrote:
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 news: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
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 wrote: On Jul 18, 11:23 am, "Don Guillett" wrote: 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 news: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
On Jul 18, 4:45*pm, JingleRock wrote:
On Jul 18, 11:23*am, "Don Guillett" wrote: 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 Don, Is there something special about the name "Sheet10"? See my following post. Chuck |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
On Jul 18, 6:48*pm, Dave Peterson wrote:
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
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 wrote: On Jul 18, 6:48 pm, Dave Peterson wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
Are you starting another instance of excel?
Maybe you aren't releasing all your object variable "set yyyy = nothing" before you close the application???? JingleRock wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
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. JingleRock wrote: 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? -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
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 wbname it and/or replace the original with the same name and close it. -- Don Guillett Microsoft MVP Excel SalesAid Software "JingleRock" wrote in message ... On Jul 18, 4:45 pm, JingleRock wrote: On Jul 18, 11:23 am, "Don Guillett" wrote: 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 Don, Is there something special about the name "Sheet10"? See my following post. Chuck |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
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. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
Glad you got it working.
JingleRock wrote: 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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAVE A SHEET AS A BOOK, CLOSE IT, CONTINUE VBA CODE - XCL 2003
Always good to post your final solution for the archives
-- Don Guillett Microsoft MVP Excel SalesAid Software "JingleRock" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'BeforeClose' code problems:book won't close if more than one book is open | Excel Programming | |||
save Changes and close book | Excel Programming | |||
Open book, copy and paste from sheet, and then close.... | Excel Programming | |||
Create new book..paste data..save..close | Excel Programming | |||
Open book, check for macros, close book | Excel Programming |