J
Jorn Rossvoll
Hi.
I've made 50 or so reports in Word which uses VBA code and SQL queries to
gather data from a SQL server, and distribute this data in various tables
etc. in the given report. These reports can be anywhere from 1 to 10 pages
with tables and data from analytical analysis. When all the necessary data is
filled in, the VBA code tells to store the document in a specific folder on a
server. And here is the problem;
How can I loose ALL the vba code in this document, so we can e-mail the
report to customers?
The best method I've found so far, is a self-destruct code at the end of the
process like this:
ActiveDocument.SaveAs ("\\Server\Folder A\" & Filename)
If ThisDocument Is Nothing Then Exit Sub
i = 0
On Error Resume Next
i = ThisDocument.VBProject.VBComponents.Count
On Error GoTo 0
If i < 1 Then ' no VBComponents or protected VBProject
MsgBox "The VBProject in " & ThisDocument.name & _
" is protected or has no components!", _
vbInformation, "Remove All Macros"
Exit Sub
End If
With ThisDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
On Error Resume Next
.VBComponents.Remove .VBComponents(i)
' delete the component
On Error GoTo 0
Next i
End With
With ThisDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
l = 1
On Error Resume Next
l = .VBComponents(i).CodeModule.CountOfLines
.VBComponents(i).CodeModule.DeleteLines 1, l
' clear lines
On Error GoTo 0
Next i
End With
End Sub
-------
This code works fine in 98% of the cases, but this one requiresd the user to
manually save the document one more time to save the changes of the "missing"
VBA code. And they don't always do this...
I have tried to save the report in RTF format, and it kinda works, but the
resulting file is often 3-6 times as big compared to the .doc-version. And
then there is the odd formatting issues that sometimes happens with the RTF
files.
Does anyone of you Word-wizards know of a better way to do this?
I whish there was a switch you could use like
ActiveDocument.SaveAs ("\\Server\Folder\Filename.doc") /NoMacros
I've made 50 or so reports in Word which uses VBA code and SQL queries to
gather data from a SQL server, and distribute this data in various tables
etc. in the given report. These reports can be anywhere from 1 to 10 pages
with tables and data from analytical analysis. When all the necessary data is
filled in, the VBA code tells to store the document in a specific folder on a
server. And here is the problem;
How can I loose ALL the vba code in this document, so we can e-mail the
report to customers?
The best method I've found so far, is a self-destruct code at the end of the
process like this:
ActiveDocument.SaveAs ("\\Server\Folder A\" & Filename)
If ThisDocument Is Nothing Then Exit Sub
i = 0
On Error Resume Next
i = ThisDocument.VBProject.VBComponents.Count
On Error GoTo 0
If i < 1 Then ' no VBComponents or protected VBProject
MsgBox "The VBProject in " & ThisDocument.name & _
" is protected or has no components!", _
vbInformation, "Remove All Macros"
Exit Sub
End If
With ThisDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
On Error Resume Next
.VBComponents.Remove .VBComponents(i)
' delete the component
On Error GoTo 0
Next i
End With
With ThisDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
l = 1
On Error Resume Next
l = .VBComponents(i).CodeModule.CountOfLines
.VBComponents(i).CodeModule.DeleteLines 1, l
' clear lines
On Error GoTo 0
Next i
End With
End Sub
-------
This code works fine in 98% of the cases, but this one requiresd the user to
manually save the document one more time to save the changes of the "missing"
VBA code. And they don't always do this...
I have tried to save the report in RTF format, and it kinda works, but the
resulting file is often 3-6 times as big compared to the .doc-version. And
then there is the odd formatting issues that sometimes happens with the RTF
files.
Does anyone of you Word-wizards know of a better way to do this?
I whish there was a switch you could use like
ActiveDocument.SaveAs ("\\Server\Folder\Filename.doc") /NoMacros