| 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? |
| 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 | |||
Hybrid Mode
