Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Compatibility Between Excel 2000 and 2002
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 ################################################## ################# |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Compatibility Between Excel 2000 and 2002
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Compatibility Between Excel 2000 and 2002
"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 wrote: 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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Compatibility Between Excel 2000 and 2002
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? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Compatibility Between Excel 2000 and 2002
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 wrote: How can i test if he has access to the VB Object Model? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Compatibility Between Excel 2000 and 2002
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? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Compatibility Between Excel 2000 and 2002
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 wrote: 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? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Compatibility Between Excel 2000 and 2002
thanks for clarifying that. makes sense now..
cheers for your help. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Backwards Compatibility with Excel 2002 | Excel Discussion (Misc queries) | |||
Excel 2002 compatibility with earlier versions | Excel Discussion (Misc queries) | |||
Excel 2002/2003 Macro Compatibility issue | Excel Programming | |||
VBA - Macro backward compatibility from excel 2002 to excel 2000 | Excel Programming | |||
compatibility between excel 2002 and 2003 | Excel Programming |