VBA Compatibility Between Excel 2000 and 2002

[Winshent]

Can anyone tell why this code should run on excel 2000 (Win2k)machine
and not excel 2002 (WinXP)?

It creates a workbook, copies certain sheets accross, and then deletes
the code from the new workbook. And its giving me major hasle and the
client site. I have no means of testing with the same environment
here.

###################################################################
Option Explicit

Public strFileName As String
Public strPath As String
'

Public Sub DeleteAllCode()

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 Sub


Public Sub CreateEndUserBook(ByVal DorW As String)

Dim strWkBookName As String

strPath = ActiveWorkbook.Path
strPath = strPath & "\"
strWkBookName = ActiveWorkbook.Name

Dim WkSheet As Worksheet
Dim AllSheets As Sheets

Set AllSheets = Worksheets
'Stop
For Each WkSheet In AllSheets
Debug.Print WkSheet.Name
Sheets(WkSheet.Name).Activate
ActiveSheet.Unprotect Password:="Dub"
Next WkSheet

If DorW = "D" Then
strFileName = "DailyBMSReturn_" & Format(Now(), "yyyy-mm-dd") &
".xls"

'copyModule

Sheets("DailyCallStats").Select

Sheets("Instructions").Activate

Sheets("DailyCallStats").Copy

DeleteAllCode

'===== new code to paste values
Sheets("DailyCallStats").Select

Sheets("DailyCallStats").Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Sheets("DailyCallStats").Range("A1").Select
'===== new code to paste values

ElseIf DorW = "W" Then
strFileName = "WeeklyBMSReturn_" & Format(Now(), "yyyy-mm-dd") &
".xls"

'copyModule

Sheets(Array("MailRtn", "LanguageRtn", "VDNRtn",
"HourlyCallStatsRtn")).Select

Sheets("Instructions").Activate

Sheets(Array("MailRtn", "LanguageRtn", "VDNRtn",
"HourlyCallStatsRtn")).Copy

DeleteAllCode

'===== new code to paste values
Sheets(Array("MailRtn", "LanguageRtn", "VDNRtn",
"HourlyCallStatsRtn")).Select

Sheets("MailRtn").Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Sheets("MailRtn").Range("A1").Select
'===== new code to paste values

End If

'Stop

ActiveWorkbook.SaveAs Filename:=strPath & strFileName,
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False

ActiveWorkbook.Close

For Each WkSheet In AllSheets
Sheets(WkSheet.Name).Activate
ActiveSheet.Protect Password:="Dub", DrawingObjects:=True,
Contents:=True, Scenarios:=True

Next WkSheet


'Windows(strFileName).Activate
'Windows(strWkBookName).Activate

End Sub
###################################################################

 

[keepITcool]

Does your client allow access to VBOM?
it's a registry setting per version.
default install does not allow VBA to access VB Object Model,
thus your code will fail.

The solution is convincing your client to allow access.
(if I were an administrator I wouldnt grant it..)

Although you can write the registry string from VBA using APIs,
BUT you need to restart excel for the change to take effect)

key name like

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Security
AccessVBOM = 0|1





keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Winshent P]

Hi

Thanks for the response. I assume access to the object model as the
spread runs validation code between sheets before creating the new
workbook.

 

[keepITcool]

"I assumed" are famous last words..

Further, what exactly are you trying to achieve?
and which line generates the error?


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Winshent P]

My master spreadsheet is a data entry form, which captures daily and
weekly data. Daily data does not go through any validation. When
submitting the daily return, the user simply presses a button on one of
the sheets, which then triggers code to copy and paste a values only
daily sheet into a new workbook. Then some more code sends this workbook
via outlook and then kills the file on the drive.

I will be releasing a new version, which exports data into a new work
book in a tabular format (current return has loads of formatting and
navigation combo's), with named ranges so that it can be read via
ado.net on a web server.. Hence i am concerned that the user cannot run
the code to create another workbook.

The only info i have back from the user is that the new excel workbook
is created and then the machine locks up. He is running a 2.Ghz machine
which should be more than capable of running this code!

How can i test if he has access to the VB Object Model?

 

[keepITcool]

Function VBOMallowed() As Boolean
On Error Resume Next
VBOMallowed = Not IsError(ThisWorkbook.VBProject.Name)
End Function


IN xl2002 it's a USER setting and can be changed from
Tools/Macro/Security/ 2nd Tab/ last checkmark.

in xl2003 it's a MACHINE setting and cannot be changed
from excel UI. the checkbox is visible but grayed out.

If you can use regedit or regedt32.exe AND
have permissions to edit following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\11.0\Excel\Security
then you may set the AccessVBOM to 1



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Winshent P]

Yes that seems to have solved the problem!! Thanks.

I do have a question:-

My problem above related to the code creating a copy of the relavent
sheet in a workbook and then sending it and deleting the file.

So how come my users could run VBA which sends the complete original
file via outloook?

 

[keepITcool]

If code is generated "on the fly" you (your program)
needs access to the VBIDE and it's objects & properties.
in order to write the code lines. (or delete 'm)

For security purposes VBA makes a distinction between the code
lines which are already compiled and available inside the project
and code that is generated "on the fly". The latter cannot be easily
authenticated and scanned for "malicious intent"

If VBOM access is enabled, you could very easily write or copy your code
to ANY workbook on the user's system... (you still can.. but it's more
difficult <g>)

Thus: Your normal code can be run if the user's macro settings allow him
to RUN code. (enable macro's) and he opts to do so when opening the
workbook. As soon as you start rummaging in the code lines of a
workbook, you need VBOM access.









keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Winshent P]

thanks for clarifying that. makes sense now..

cheers for your help.