So what I ended up doing is a table like the one on the attached file.
I then entered a formula in D3 to give me the file path; as I'm not that good with VBA to enter all the formulas in the code itself, I know it's possible.
I then entered this code and refer to D3 on the attachment
Sub DeleteAllCode()
Dim wb As Workbook
Dim x As Integer
Dim Proceed As VbMsgBoxResult
Dim Prompt As String
Dim Title As String
If Application.Version > 11 Then
Set wb = ActiveWorkbook
wb.BuiltinDocumentProperties("Comments") = "Created by " & Environ("USERNAME") & " on " & Format(Now, "mmm dd, yy hh:mm:ss AM/PM")
wb.SaveAs Filename:=Sheets("Sheet1").Range("D3").Text & "\" & "FS Statements.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Else
wb.BuiltinDocumentProperties("Comments") = "Created by " & Environ("USERNAME") & " on " & Format(Now, "mmm dd, yy hh:mm:ss AM/PM")
wb.SaveAs Filename:=Sheets("Sheet1").Range("D3").Text & "\" & "FS Statements.xls", _
FileFormat:=xlWorkbookNormal
Prompt = "Are you certain that you want to delete all the VBA Code from " & _
ActiveWorkbook.Name & "?"
Title = "Verify Procedure"
Proceed = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
If Proceed = vbNo Then
MsgBox "Procedure Canceled", vbInformation, "Procedure Aborted"
Exit Sub
End If
On Error Resume Next
With ActiveWorkbook.VBProject
For x = .VBComponents.Count To 1 Step -1
.VBComponents.Remove .VBComponents(x)
Next x
For x = .VBComponents.Count To 1 Step -1
.VBComponents(x).CodeModule.DeleteLines _
1, .VBComponents(x).CodeModule.CountOfLines
Next x
End With
On Error GoTo 0
End If
End Sub
So the code above do two things. As I want the tweaked file (after running all my other pieces of code) saved in a specific file path, I also don't want to save the VBA code. So I know that by saving in xlsx format all code is deleted. So I told excel to identify the excel version being used, if its 2007 and above to save as xlsx and all code is gone. Now, if the version is lower, then obviously it cannot save as xlsx. It can only save as xls file; but by doing so it retain all the vba code (which I don't want) so I told it to save as xls format in the path specified in D3 and remove all the vba code programatically.
I've tested in excel 2010 and it works fine. I haven't yet tested in xl 2003. It'd be interesting how it handles the removal of the code....
Now, how do I pat my own back... ?
LinkBack URL
About LinkBacks
Register To Reply
Very different approach. :D I never knew VBA could remove code from the module. What the hell!
Bookmarks