Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
Hi everyone,
Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
This is the key line in Chip's code for determining which workbook it
deletes from. Set VBProj = ActiveWorkbook.VBProject Change ActiveWorkbook to the name of your new workbook that you want the code deleted from, or just make sure that it is the active workbook. Either way that ties it down to where you want to do the delete. "CB" wrote in message ... Hi everyone, Programming in Excel is rather new to me. I've been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. I'm looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. I've searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a "save as". The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, I'm including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
Instead of creating a macro to delete the macros from the workbook. Instead
create a new workbook and copy the sheet to new book. Sub CreateCopy First = true for each sht in thisworkbook If First = True then sht.copy 'create new workbook set NewBk = Activeworkbook First = False else with NewBk sht.copy after:=.sheets(.sheets.count) end with end if next sht NewBk.SAveas filename:="book2.xls" next sht end sub "CB" wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
On Mar 19, 12:10*pm, CB wrote:
Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference tohttp://www.cpearson.com/excel/VBE.aspxwhere the following code will delete all VBA in a project. Sub DeleteAllVBACode() * * * * Dim VBProj As VBIDE.VBProject * * * * Dim VBComp As VBIDE.VBComponent * * * * Dim CodeMod As VBIDE.CodeModule * * * * Set VBProj = ActiveWorkbook.VBProject * * * * For Each VBComp In VBProj.VBComponents * * * * * * If VBComp.Type = vbext_ct_Document Then * * * * * * * * Set CodeMod = VBComp.CodeModule * * * * * * * * With CodeMod * * * * * * * * * * .DeleteLines 1, .CountOfLines * * * * * * * * End With * * * * * * Else * * * * * * * * VBProj.VBComponents.Remove VBComp * * * * * * End If * * * * Next VBComp * * End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() * * If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then * * * * MsgBox ("You must enter a serial number.") * * Exit Sub * * Else * * * * Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) * * * * If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then * * * * * * ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" * * * * Else * * * * * * If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then * * * * * * ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" * * * * Else * * * * * * MsgBox ("Please fix the serial number.") * * * * End If * * End If * * End If End Sub Thanks for any and all assistance. Chris Chris, In Chip's code, VBProj is an object created to reference the VBProjects in the ActiveWorkbook. VBComp includes the components of VBProj (or the VBProjects in the ActiveWorkbook). In your code, you are using ActiveWorkbook, so whatever workbook is currently selected will be the ActiveWorkbook. There are a few other ways to refer to a Workbook. You can use Me, ThisWorkbook, an index number, or a workbook name. The code will execute in the workbook you specify. Let me know if this helps. Best, Matt Herbert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
Hi Joel,
Thanks for responding. I thought I would try this. First, the debugger didn't like the fact that you had "next sht" twice. Once I deleted the last instance, compiled, then ran the code, I received the following error... Run-time Error "438" Object doesn't support this property or method. The debugger then stops on the line: For each sht in This Workbook. Thanks again. Chris "Joel" wrote: Instead of creating a macro to delete the macros from the workbook. Instead create a new workbook and copy the sheet to new book. Sub CreateCopy First = true for each sht in thisworkbook If First = True then sht.copy 'create new workbook set NewBk = Activeworkbook First = False else with NewBk sht.copy after:=.sheets(.sheets.count) end with end if next sht NewBk.SAveas filename:="book2.xls" next sht end sub "CB" wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
Hi there,
Thanks for taking the time to respond. I thought as much. To change ActiveWorkbook to the name of my workbook, I presume I'll have to specify the path as well, since the workbook will be saved over the network. I'm not clear on what the proper syntax would be. Could you help? Hmm, since I'm naming the file based on the value in cell D3 and date, I'm thinking this complicates things? Regards, Chris "JLGWhiz" wrote: This is the key line in Chip's code for determining which workbook it deletes from. Set VBProj = ActiveWorkbook.VBProject Change ActiveWorkbook to the name of your new workbook that you want the code deleted from, or just make sure that it is the active workbook. Either way that ties it down to where you want to do the delete. "CB" wrote in message ... Hi everyone, Programming in Excel is rather new to me. I've been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. I'm looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. I've searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a "save as". The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, I'm including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
Hi Matt,
Thanks for replying. As I mentioned in my reply to JLGWhiz, I'm not sure of the proper syntax to refer to the file I'm saving (not the currently opened on). Your thoughts? Regards, Chris " wrote: On Mar 19, 12:10 pm, CB wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference tohttp://www.cpearson.com/excel/VBE.aspxwhere the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris Chris, In Chip's code, VBProj is an object created to reference the VBProjects in the ActiveWorkbook. VBComp includes the components of VBProj (or the VBProjects in the ActiveWorkbook). In your code, you are using ActiveWorkbook, so whatever workbook is currently selected will be the ActiveWorkbook. There are a few other ways to refer to a Workbook. You can use Me, ThisWorkbook, an index number, or a workbook name. The code will execute in the workbook you specify. Let me know if this helps. Best, Matt Herbert |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
If the line is as you show it, then remove the space from between This and
Workbook... the correct reference is ThisWorkbook -- Rick (MVP - Excel) "CB" wrote in message ... Hi Joel, Thanks for responding. I thought I would try this. First, the debugger didn't like the fact that you had "next sht" twice. Once I deleted the last instance, compiled, then ran the code, I received the following error... Run-time Error "438" Object doesn't support this property or method. The debugger then stops on the line: For each sht in This Workbook. Thanks again. Chris "Joel" wrote: Instead of creating a macro to delete the macros from the workbook. Instead create a new workbook and copy the sheet to new book. Sub CreateCopy First = true for each sht in thisworkbook If First = True then sht.copy 'create new workbook set NewBk = Activeworkbook First = False else with NewBk sht.copy after:=.sheets(.sheets.count) end with end if next sht NewBk.SAveas filename:="book2.xls" next sht end sub "CB" wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
As others have pointed out, the code as written works on the ActiveWorkbook, specified only on the line Set VBProj = ActiveWorkbook.VBProject As I see it, you have two workbooks under consideration: Original.xls ' and SavedAs.xls When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. If you want to remove all the VBA code from the SavedAs.xls workbook, you can run the code as is. However, if you need to run the code against Original.xls (or any other closed workbook), you will need to open that workbook. The code works only on open workbooks. You can specify the workbook whose code is to be stripped with something like Set VBProj = Workbooks("WorkbookName.xls") If you need to open the workbook, you can use code like Dim WB As Workbook Set WB = Workbooks.Open("C:\Book1.xls") Set VBProj = WB.VBProject The variable WB is set to the instance of the opened C:\Book1.xls workbook and the VBProj variable is set to the VBProject of that workbook. All the rest of the code will refer to that workbook. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 10:10:01 -0700, CB wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
Hi Chip,
Thanks for responding. I was perplexed by your statement... "When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. " This is not the behaviour I see. When I click the button to save the file, a new file is saved in the background (in that I don't see it open at all) that contains all the data that was entered and my original file remains open. Of course, the original files also contains the data but I have a button that clears the data so the tech can run another test. Chris "Chip Pearson" wrote: As others have pointed out, the code as written works on the ActiveWorkbook, specified only on the line Set VBProj = ActiveWorkbook.VBProject As I see it, you have two workbooks under consideration: Original.xls ' and SavedAs.xls When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. If you want to remove all the VBA code from the SavedAs.xls workbook, you can run the code as is. However, if you need to run the code against Original.xls (or any other closed workbook), you will need to open that workbook. The code works only on open workbooks. You can specify the workbook whose code is to be stripped with something like Set VBProj = Workbooks("WorkbookName.xls") If you need to open the workbook, you can use code like Dim WB As Workbook Set WB = Workbooks.Open("C:\Book1.xls") Set VBProj = WB.VBProject The variable WB is set to the instance of the opened C:\Book1.xls workbook and the VBProj variable is set to the VBProject of that workbook. All the rest of the code will refer to that workbook. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 10:10:01 -0700, CB wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
Hi Rick,
My mistake. I do not have a space in the code. It was a typo in this message. Chris "Rick Rothstein" wrote: If the line is as you show it, then remove the space from between This and Workbook... the correct reference is ThisWorkbook -- Rick (MVP - Excel) "CB" wrote in message ... Hi Joel, Thanks for responding. I thought I would try this. First, the debugger didn't like the fact that you had "next sht" twice. Once I deleted the last instance, compiled, then ran the code, I received the following error... Run-time Error "438" Object doesn't support this property or method. The debugger then stops on the line: For each sht in This Workbook. Thanks again. Chris "Joel" wrote: Instead of creating a macro to delete the macros from the workbook. Instead create a new workbook and copy the sheet to new book. Sub CreateCopy First = true for each sht in thisworkbook If First = True then sht.copy 'create new workbook set NewBk = Activeworkbook First = False else with NewBk sht.copy after:=.sheets(.sheets.count) end with end if next sht NewBk.SAveas filename:="book2.xls" next sht end sub "CB" wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
Thanks for responding. I was perplexed by your statement...
"When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. " Are you talking about a Save or a Save As? The subject of your post "(creating using save as)" implies that you are talking about Save As and not just Save. This is not the behaviour I see. When I click the button to save the file, a new file is saved in the background (in that I don't see it open at all) that contains all the data that was entered and my original file remains open. That is a standard Save (in particular a save of a new file that has never been saved), not a Save As. There are three different Save operations to consider. First is just plain Save, which saves changes to the current workbook and leaves it open. The second is SaveAs, which in effect closes the current workbook without saving any changes made since the last Save operation, and leaves you with an open workbook with the new name. So if you have Original.xls open and do a Save As to SavedAs.xls, Original.xls is effectively closed without saving changes and the current open workbook is SavedAs.xls. Whether you want to think of this as closing and opening or as renaming (under the covers, all this happens with temp files and renaming these temp files), it doesn't really matter. The end result is that Original.xls is closed without saving changes and SavedAs.xls is open with the current changes. The third type of save is SaveCopyAs. This saves the current state of the workbook, including changes made since that last Save operation, to another file. That file isn't opened in Excel. It is a snapshot of the current workbook as it exists when SaveCopyAs is executed. The file specified in SaveCopyAs isn't opened in Excel. In the Workbook_BeforeSave event, there is a parameter named SaveAsUI. If the user is using the Save As dialog, this parameter will be True. If the user does a plain old Save, this parameter will be False. You can also override the user and cancel the save by setting the Cancel parameter to True. You could put code in Workbook_BeforeSave to strip the VBA. Two caveats: The user can still cancel the save after BeforeSave concludes so you would have stripped out the code when no save was carried out. As a (partial) remedy to this, you could write your own save logic in the Workbook_BeforeSave event, set Cancel to True, and use ThisWorkbook.Saved = True to mark the workbook as having been saved (even if it wasn't really saved). The second caveat is that code that strips out code in the same workbook as which that code resides can cause problems. That is to say, it isn't really a good idea to have a workbook strip out its own code, and even more importantly, you don't want a module to strip out its own code. At the very least, put all the code that strips out the VBA in its own module and make sure that that module is the last one removed. Otherwise, the results can be unpredictable. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 12:43:01 -0700, CB wrote: Hi Chip, Thanks for responding. I was perplexed by your statement... "When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. " This is not the behaviour I see. When I click the button to save the file, a new file is saved in the background (in that I don't see it open at all) that contains all the data that was entered and my original file remains open. Of course, the original files also contains the data but I have a button that clears the data so the tech can run another test. Chris "Chip Pearson" wrote: As others have pointed out, the code as written works on the ActiveWorkbook, specified only on the line Set VBProj = ActiveWorkbook.VBProject As I see it, you have two workbooks under consideration: Original.xls ' and SavedAs.xls When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. If you want to remove all the VBA code from the SavedAs.xls workbook, you can run the code as is. However, if you need to run the code against Original.xls (or any other closed workbook), you will need to open that workbook. The code works only on open workbooks. You can specify the workbook whose code is to be stripped with something like Set VBProj = Workbooks("WorkbookName.xls") If you need to open the workbook, you can use code like Dim WB As Workbook Set WB = Workbooks.Open("C:\Book1.xls") Set VBProj = WB.VBProject The variable WB is set to the instance of the opened C:\Book1.xls workbook and the VBProj variable is set to the VBProject of that workbook. All the rest of the code will refer to that workbook. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 10:10:01 -0700, CB wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
Hi Chip,
Sorry for the delay in getting back to you. I've been having a lot of problems with the web-based newsgroup both yesterday and again today - getting lots of "service temporarily unavailable." Just as well since I was really tired and getting frustrated yesterday trying to get Excel to do what I wanted. :) Thanks for the very clear explanation below. It has helped my understanding a good deal. Clearly I was tired since I missed the the fact that my own code has SaveCopyAS, not Save or SaveAs. Sheesh! I will be training my users to save the document using the command button in the spreadsheet, as opposed to File/Save or File/SaveAs. The idea is that the spreadsheet is used to capture test data from sensors (via a datalogger). The users will in all likelihood will be testing several sensors in a row so the process will be: -connect a sensor -collect date (Excel determines whether it is a pass or fail) -save a copy of the data to a file -clear the data in the original file (template if you will) via a command button -connect another sensor and repeat the process. Because of this process, IDEALLY the user shouldn't be tempted to use File/Save or File/SaveAs. I'm looking at your last paragraph again and I think I'm missing something. You say that "...it isn't really a good idea to have workbook strip out its own code...". Isn't that the point of the code on your website? :) What am I missing? (I'm blaming it on the fact that its still early and I haven't had a coffee break yet. LOL!) Thanks again! Chris "Chip Pearson" wrote: Thanks for responding. I was perplexed by your statement... "When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. " Are you talking about a Save or a Save As? The subject of your post "(creating using save as)" implies that you are talking about Save As and not just Save. This is not the behaviour I see. When I click the button to save the file, a new file is saved in the background (in that I don't see it open at all) that contains all the data that was entered and my original file remains open. That is a standard Save (in particular a save of a new file that has never been saved), not a Save As. There are three different Save operations to consider. First is just plain Save, which saves changes to the current workbook and leaves it open. The second is SaveAs, which in effect closes the current workbook without saving any changes made since the last Save operation, and leaves you with an open workbook with the new name. So if you have Original.xls open and do a Save As to SavedAs.xls, Original.xls is effectively closed without saving changes and the current open workbook is SavedAs.xls. Whether you want to think of this as closing and opening or as renaming (under the covers, all this happens with temp files and renaming these temp files), it doesn't really matter. The end result is that Original.xls is closed without saving changes and SavedAs.xls is open with the current changes. The third type of save is SaveCopyAs. This saves the current state of the workbook, including changes made since that last Save operation, to another file. That file isn't opened in Excel. It is a snapshot of the current workbook as it exists when SaveCopyAs is executed. The file specified in SaveCopyAs isn't opened in Excel. In the Workbook_BeforeSave event, there is a parameter named SaveAsUI. If the user is using the Save As dialog, this parameter will be True. If the user does a plain old Save, this parameter will be False. You can also override the user and cancel the save by setting the Cancel parameter to True. You could put code in Workbook_BeforeSave to strip the VBA. Two caveats: The user can still cancel the save after BeforeSave concludes so you would have stripped out the code when no save was carried out. As a (partial) remedy to this, you could write your own save logic in the Workbook_BeforeSave event, set Cancel to True, and use ThisWorkbook.Saved = True to mark the workbook as having been saved (even if it wasn't really saved). The second caveat is that code that strips out code in the same workbook as which that code resides can cause problems. That is to say, it isn't really a good idea to have a workbook strip out its own code, and even more importantly, you don't want a module to strip out its own code. At the very least, put all the code that strips out the VBA in its own module and make sure that that module is the last one removed. Otherwise, the results can be unpredictable. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 12:43:01 -0700, CB wrote: Hi Chip, Thanks for responding. I was perplexed by your statement... "When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. " This is not the behaviour I see. When I click the button to save the file, a new file is saved in the background (in that I don't see it open at all) that contains all the data that was entered and my original file remains open. Of course, the original files also contains the data but I have a button that clears the data so the tech can run another test. Chris "Chip Pearson" wrote: As others have pointed out, the code as written works on the ActiveWorkbook, specified only on the line Set VBProj = ActiveWorkbook.VBProject As I see it, you have two workbooks under consideration: Original.xls ' and SavedAs.xls When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. If you want to remove all the VBA code from the SavedAs.xls workbook, you can run the code as is. However, if you need to run the code against Original.xls (or any other closed workbook), you will need to open that workbook. The code works only on open workbooks. You can specify the workbook whose code is to be stripped with something like Set VBProj = Workbooks("WorkbookName.xls") If you need to open the workbook, you can use code like Dim WB As Workbook Set WB = Workbooks.Open("C:\Book1.xls") Set VBProj = WB.VBProject The variable WB is set to the instance of the opened C:\Book1.xls workbook and the VBProj variable is set to the VBProject of that workbook. All the rest of the code will refer to that workbook. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 10:10:01 -0700, CB wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
I'm looking at your last paragraph again and I think I'm missing something. You say that "...it isn't really a good idea to have workbook strip out its own code...". Isn't that the point of the code on your website? :) Not exactly. The code on the web site describes a number of methods that can be used with any workbook, not just the ActiveWorkbook or ThisWorkbook. The code is deliberately generic so that it is easily adapted to work with any arbitrary workbook. While you can use code in a workbook to modify the code in that same workbook, sometimes VBE will choke, especially if you are modifying the module that contains the extensibility code. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 20 Mar 2009 07:04:02 -0700, CB wrote: Hi Chip, Sorry for the delay in getting back to you. I've been having a lot of problems with the web-based newsgroup both yesterday and again today - getting lots of "service temporarily unavailable." Just as well since I was really tired and getting frustrated yesterday trying to get Excel to do what I wanted. :) Thanks for the very clear explanation below. It has helped my understanding a good deal. Clearly I was tired since I missed the the fact that my own code has SaveCopyAS, not Save or SaveAs. Sheesh! I will be training my users to save the document using the command button in the spreadsheet, as opposed to File/Save or File/SaveAs. The idea is that the spreadsheet is used to capture test data from sensors (via a datalogger). The users will in all likelihood will be testing several sensors in a row so the process will be: -connect a sensor -collect date (Excel determines whether it is a pass or fail) -save a copy of the data to a file -clear the data in the original file (template if you will) via a command button -connect another sensor and repeat the process. Because of this process, IDEALLY the user shouldn't be tempted to use File/Save or File/SaveAs. I'm looking at your last paragraph again and I think I'm missing something. You say that "...it isn't really a good idea to have workbook strip out its own code...". Isn't that the point of the code on your website? :) What am I missing? (I'm blaming it on the fact that its still early and I haven't had a coffee break yet. LOL!) Thanks again! Chris "Chip Pearson" wrote: Thanks for responding. I was perplexed by your statement... "When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. " Are you talking about a Save or a Save As? The subject of your post "(creating using save as)" implies that you are talking about Save As and not just Save. This is not the behaviour I see. When I click the button to save the file, a new file is saved in the background (in that I don't see it open at all) that contains all the data that was entered and my original file remains open. That is a standard Save (in particular a save of a new file that has never been saved), not a Save As. There are three different Save operations to consider. First is just plain Save, which saves changes to the current workbook and leaves it open. The second is SaveAs, which in effect closes the current workbook without saving any changes made since the last Save operation, and leaves you with an open workbook with the new name. So if you have Original.xls open and do a Save As to SavedAs.xls, Original.xls is effectively closed without saving changes and the current open workbook is SavedAs.xls. Whether you want to think of this as closing and opening or as renaming (under the covers, all this happens with temp files and renaming these temp files), it doesn't really matter. The end result is that Original.xls is closed without saving changes and SavedAs.xls is open with the current changes. The third type of save is SaveCopyAs. This saves the current state of the workbook, including changes made since that last Save operation, to another file. That file isn't opened in Excel. It is a snapshot of the current workbook as it exists when SaveCopyAs is executed. The file specified in SaveCopyAs isn't opened in Excel. In the Workbook_BeforeSave event, there is a parameter named SaveAsUI. If the user is using the Save As dialog, this parameter will be True. If the user does a plain old Save, this parameter will be False. You can also override the user and cancel the save by setting the Cancel parameter to True. You could put code in Workbook_BeforeSave to strip the VBA. Two caveats: The user can still cancel the save after BeforeSave concludes so you would have stripped out the code when no save was carried out. As a (partial) remedy to this, you could write your own save logic in the Workbook_BeforeSave event, set Cancel to True, and use ThisWorkbook.Saved = True to mark the workbook as having been saved (even if it wasn't really saved). The second caveat is that code that strips out code in the same workbook as which that code resides can cause problems. That is to say, it isn't really a good idea to have a workbook strip out its own code, and even more importantly, you don't want a module to strip out its own code. At the very least, put all the code that strips out the VBA in its own module and make sure that that module is the last one removed. Otherwise, the results can be unpredictable. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 12:43:01 -0700, CB wrote: Hi Chip, Thanks for responding. I was perplexed by your statement... "When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. " This is not the behaviour I see. When I click the button to save the file, a new file is saved in the background (in that I don't see it open at all) that contains all the data that was entered and my original file remains open. Of course, the original files also contains the data but I have a button that clears the data so the tech can run another test. Chris "Chip Pearson" wrote: As others have pointed out, the code as written works on the ActiveWorkbook, specified only on the line Set VBProj = ActiveWorkbook.VBProject As I see it, you have two workbooks under consideration: Original.xls ' and SavedAs.xls When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. If you want to remove all the VBA code from the SavedAs.xls workbook, you can run the code as is. However, if you need to run the code against Original.xls (or any other closed workbook), you will need to open that workbook. The code works only on open workbooks. You can specify the workbook whose code is to be stripped with something like Set VBProj = Workbooks("WorkbookName.xls") If you need to open the workbook, you can use code like Dim WB As Workbook Set WB = Workbooks.Open("C:\Book1.xls") Set VBProj = WB.VBProject The variable WB is set to the instance of the opened C:\Book1.xls workbook and the VBProj variable is set to the VBProject of that workbook. All the rest of the code will refer to that workbook. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 10:10:01 -0700, CB wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
Hi again,
I hope I didn't offend you as that certainly wasn't my intention. I do appreciate you taking the time to help me out! I think the best course of action for me at this point would be to be rethink how I want things done. Simplest is best - I just need to step back and figure out what "simplest" is. Thanks to all for your time! Regards, Chris "Chip Pearson" wrote: I'm looking at your last paragraph again and I think I'm missing something. You say that "...it isn't really a good idea to have workbook strip out its own code...". Isn't that the point of the code on your website? :) Not exactly. The code on the web site describes a number of methods that can be used with any workbook, not just the ActiveWorkbook or ThisWorkbook. The code is deliberately generic so that it is easily adapted to work with any arbitrary workbook. While you can use code in a workbook to modify the code in that same workbook, sometimes VBE will choke, especially if you are modifying the module that contains the extensibility code. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 20 Mar 2009 07:04:02 -0700, CB wrote: Hi Chip, Sorry for the delay in getting back to you. I've been having a lot of problems with the web-based newsgroup both yesterday and again today - getting lots of "service temporarily unavailable." Just as well since I was really tired and getting frustrated yesterday trying to get Excel to do what I wanted. :) Thanks for the very clear explanation below. It has helped my understanding a good deal. Clearly I was tired since I missed the the fact that my own code has SaveCopyAS, not Save or SaveAs. Sheesh! I will be training my users to save the document using the command button in the spreadsheet, as opposed to File/Save or File/SaveAs. The idea is that the spreadsheet is used to capture test data from sensors (via a datalogger). The users will in all likelihood will be testing several sensors in a row so the process will be: -connect a sensor -collect date (Excel determines whether it is a pass or fail) -save a copy of the data to a file -clear the data in the original file (template if you will) via a command button -connect another sensor and repeat the process. Because of this process, IDEALLY the user shouldn't be tempted to use File/Save or File/SaveAs. I'm looking at your last paragraph again and I think I'm missing something. You say that "...it isn't really a good idea to have workbook strip out its own code...". Isn't that the point of the code on your website? :) What am I missing? (I'm blaming it on the fact that its still early and I haven't had a coffee break yet. LOL!) Thanks again! Chris "Chip Pearson" wrote: Thanks for responding. I was perplexed by your statement... "When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. " Are you talking about a Save or a Save As? The subject of your post "(creating using save as)" implies that you are talking about Save As and not just Save. This is not the behaviour I see. When I click the button to save the file, a new file is saved in the background (in that I don't see it open at all) that contains all the data that was entered and my original file remains open. That is a standard Save (in particular a save of a new file that has never been saved), not a Save As. There are three different Save operations to consider. First is just plain Save, which saves changes to the current workbook and leaves it open. The second is SaveAs, which in effect closes the current workbook without saving any changes made since the last Save operation, and leaves you with an open workbook with the new name. So if you have Original.xls open and do a Save As to SavedAs.xls, Original.xls is effectively closed without saving changes and the current open workbook is SavedAs.xls. Whether you want to think of this as closing and opening or as renaming (under the covers, all this happens with temp files and renaming these temp files), it doesn't really matter. The end result is that Original.xls is closed without saving changes and SavedAs.xls is open with the current changes. The third type of save is SaveCopyAs. This saves the current state of the workbook, including changes made since that last Save operation, to another file. That file isn't opened in Excel. It is a snapshot of the current workbook as it exists when SaveCopyAs is executed. The file specified in SaveCopyAs isn't opened in Excel. In the Workbook_BeforeSave event, there is a parameter named SaveAsUI. If the user is using the Save As dialog, this parameter will be True. If the user does a plain old Save, this parameter will be False. You can also override the user and cancel the save by setting the Cancel parameter to True. You could put code in Workbook_BeforeSave to strip the VBA. Two caveats: The user can still cancel the save after BeforeSave concludes so you would have stripped out the code when no save was carried out. As a (partial) remedy to this, you could write your own save logic in the Workbook_BeforeSave event, set Cancel to True, and use ThisWorkbook.Saved = True to mark the workbook as having been saved (even if it wasn't really saved). The second caveat is that code that strips out code in the same workbook as which that code resides can cause problems. That is to say, it isn't really a good idea to have a workbook strip out its own code, and even more importantly, you don't want a module to strip out its own code. At the very least, put all the code that strips out the VBA in its own module and make sure that that module is the last one removed. Otherwise, the results can be unpredictable. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 12:43:01 -0700, CB wrote: Hi Chip, Thanks for responding. I was perplexed by your statement... "When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. " This is not the behaviour I see. When I click the button to save the file, a new file is saved in the background (in that I don't see it open at all) that contains all the data that was entered and my original file remains open. Of course, the original files also contains the data but I have a button that clears the data so the tech can run another test. Chris "Chip Pearson" wrote: As others have pointed out, the code as written works on the ActiveWorkbook, specified only on the line Set VBProj = ActiveWorkbook.VBProject As I see it, you have two workbooks under consideration: Original.xls ' and SavedAs.xls When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. If you want to remove all the VBA code from the SavedAs.xls workbook, you can run the code as is. However, if you need to run the code against Original.xls (or any other closed workbook), you will need to open that workbook. The code works only on open workbooks. You can specify the workbook whose code is to be stripped with something like Set VBProj = Workbooks("WorkbookName.xls") If you need to open the workbook, you can use code like Dim WB As Workbook Set WB = Workbooks.Open("C:\Book1.xls") Set VBProj = WB.VBProject The variable WB is set to the instance of the opened C:\Book1.xls workbook and the VBProj variable is set to the VBProject of that workbook. All the rest of the code will refer to that workbook. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 10:10:01 -0700, CB wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting all code in new file only (creating using save as)
I hope I didn't offend you as that certainly wasn't my intention. I do appreciate you taking the time to help me out! No offense taken at all. I'm happy to help. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 20 Mar 2009 08:12:14 -0700, CB wrote: Hi again, I hope I didn't offend you as that certainly wasn't my intention. I do appreciate you taking the time to help me out! I think the best course of action for me at this point would be to be rethink how I want things done. Simplest is best - I just need to step back and figure out what "simplest" is. Thanks to all for your time! Regards, Chris "Chip Pearson" wrote: I'm looking at your last paragraph again and I think I'm missing something. You say that "...it isn't really a good idea to have workbook strip out its own code...". Isn't that the point of the code on your website? :) Not exactly. The code on the web site describes a number of methods that can be used with any workbook, not just the ActiveWorkbook or ThisWorkbook. The code is deliberately generic so that it is easily adapted to work with any arbitrary workbook. While you can use code in a workbook to modify the code in that same workbook, sometimes VBE will choke, especially if you are modifying the module that contains the extensibility code. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 20 Mar 2009 07:04:02 -0700, CB wrote: Hi Chip, Sorry for the delay in getting back to you. I've been having a lot of problems with the web-based newsgroup both yesterday and again today - getting lots of "service temporarily unavailable." Just as well since I was really tired and getting frustrated yesterday trying to get Excel to do what I wanted. :) Thanks for the very clear explanation below. It has helped my understanding a good deal. Clearly I was tired since I missed the the fact that my own code has SaveCopyAS, not Save or SaveAs. Sheesh! I will be training my users to save the document using the command button in the spreadsheet, as opposed to File/Save or File/SaveAs. The idea is that the spreadsheet is used to capture test data from sensors (via a datalogger). The users will in all likelihood will be testing several sensors in a row so the process will be: -connect a sensor -collect date (Excel determines whether it is a pass or fail) -save a copy of the data to a file -clear the data in the original file (template if you will) via a command button -connect another sensor and repeat the process. Because of this process, IDEALLY the user shouldn't be tempted to use File/Save or File/SaveAs. I'm looking at your last paragraph again and I think I'm missing something. You say that "...it isn't really a good idea to have workbook strip out its own code...". Isn't that the point of the code on your website? :) What am I missing? (I'm blaming it on the fact that its still early and I haven't had a coffee break yet. LOL!) Thanks again! Chris "Chip Pearson" wrote: Thanks for responding. I was perplexed by your statement... "When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. " Are you talking about a Save or a Save As? The subject of your post "(creating using save as)" implies that you are talking about Save As and not just Save. This is not the behaviour I see. When I click the button to save the file, a new file is saved in the background (in that I don't see it open at all) that contains all the data that was entered and my original file remains open. That is a standard Save (in particular a save of a new file that has never been saved), not a Save As. There are three different Save operations to consider. First is just plain Save, which saves changes to the current workbook and leaves it open. The second is SaveAs, which in effect closes the current workbook without saving any changes made since the last Save operation, and leaves you with an open workbook with the new name. So if you have Original.xls open and do a Save As to SavedAs.xls, Original.xls is effectively closed without saving changes and the current open workbook is SavedAs.xls. Whether you want to think of this as closing and opening or as renaming (under the covers, all this happens with temp files and renaming these temp files), it doesn't really matter. The end result is that Original.xls is closed without saving changes and SavedAs.xls is open with the current changes. The third type of save is SaveCopyAs. This saves the current state of the workbook, including changes made since that last Save operation, to another file. That file isn't opened in Excel. It is a snapshot of the current workbook as it exists when SaveCopyAs is executed. The file specified in SaveCopyAs isn't opened in Excel. In the Workbook_BeforeSave event, there is a parameter named SaveAsUI. If the user is using the Save As dialog, this parameter will be True. If the user does a plain old Save, this parameter will be False. You can also override the user and cancel the save by setting the Cancel parameter to True. You could put code in Workbook_BeforeSave to strip the VBA. Two caveats: The user can still cancel the save after BeforeSave concludes so you would have stripped out the code when no save was carried out. As a (partial) remedy to this, you could write your own save logic in the Workbook_BeforeSave event, set Cancel to True, and use ThisWorkbook.Saved = True to mark the workbook as having been saved (even if it wasn't really saved). The second caveat is that code that strips out code in the same workbook as which that code resides can cause problems. That is to say, it isn't really a good idea to have a workbook strip out its own code, and even more importantly, you don't want a module to strip out its own code. At the very least, put all the code that strips out the VBA in its own module and make sure that that module is the last one removed. Otherwise, the results can be unpredictable. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 12:43:01 -0700, CB wrote: Hi Chip, Thanks for responding. I was perplexed by your statement... "When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. " This is not the behaviour I see. When I click the button to save the file, a new file is saved in the background (in that I don't see it open at all) that contains all the data that was entered and my original file remains open. Of course, the original files also contains the data but I have a button that clears the data so the tech can run another test. Chris "Chip Pearson" wrote: As others have pointed out, the code as written works on the ActiveWorkbook, specified only on the line Set VBProj = ActiveWorkbook.VBProject As I see it, you have two workbooks under consideration: Original.xls ' and SavedAs.xls When you do the Save As operation, Original.xls is closed and SavedAs.xls is open and the Active Workbook. If you want to remove all the VBA code from the SavedAs.xls workbook, you can run the code as is. However, if you need to run the code against Original.xls (or any other closed workbook), you will need to open that workbook. The code works only on open workbooks. You can specify the workbook whose code is to be stripped with something like Set VBProj = Workbooks("WorkbookName.xls") If you need to open the workbook, you can use code like Dim WB As Workbook Set WB = Workbooks.Open("C:\Book1.xls") Set VBProj = WB.VBProject The variable WB is set to the instance of the opened C:\Book1.xls workbook and the VBProj variable is set to the VBProject of that workbook. All the rest of the code will refer to that workbook. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 10:10:01 -0700, CB wrote: Hi everyone, Programming in Excel is rather new to me. Ive been able to get by thus far by recording macros and looking at the code. I have been able to manipulate the code by referring to the VBE help and these news groups. Im looking for assistance in modifying my code such that when the file is saved (SAVE AS actually), all code is removed from the new file but not the current file. Ive searched these discussions groups and found a reference to http://www.cpearson.com/excel/VBE.aspx where the following code will delete all VBA in a project. Sub DeleteAllVBACode() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents If VBComp.Type = vbext_ct_Document Then Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines End With Else VBProj.VBComponents.Remove VBComp End If Next VBComp End Sub What I am not clear on is whether or not this code will delete all code in the workbook that is currently open or whether it will delete the code in the new workbook that is created when my code does a save as. The first line under the declarations makes me think perhaps it deletes the code in the workbook currently opened. Am I correct? FWIW, Im including the code that executes when my user clicks the command button to save the file. Sorry in advance if the lines don't wrap properly. Private Sub SaveData_Click() If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then MsgBox ("You must enter a serial number.") Exit Sub Else Worksheets("Post-Service").Range("D3") = UCase(Worksheets("Post-Service").Range("D3")) If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else If MsgBox("Are you sure the serial number doesn't begin with C?", vbYesNo) = vbYes Then ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3") & "_" & Format(Now, "yyyymmmdd") & ".xls" Else MsgBox ("Please fix the serial number.") End If End If End If End Sub Thanks for any and all assistance. Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel creating TMP with each save but not deleting on close | Excel Discussion (Misc queries) | |||
Creating a path to save a file | Excel Programming | |||
Can VBA save file after deleting all VB code? | Excel Programming | |||
Problem: vba code for creating a button to save worksheet as Cell | Excel Programming | |||
Deleting code in a file with code.. | Excel Programming |