Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Version issues
I am having fits trying to deploy a spreadsheet with VBA.
I have developed the sheet in Excel2002-SP3(loaded from XP Office prof 2003) on Windows2000 SP4 It works fine on that platform... it also works on a Win2000 SP2 platform. On a NT based PC runing Excel2000 this line gives a compile error: Set fso = CreateObject("Scripting.FileSystemObject") On win2000 running Excel2002-sp1... Complie error on the "TrailingMinusNumbers:=True" of the Workbook.OpenText cmd. Removed that option completely (since its optional anyway) and then the macro runs up to the OpenText command then it gets a "Runtime error 1004: Method OpenText of object Workbooks failed" note: the FileSystemObject statement above executed fine on this one. How can I code these statements so that they are version independent? Or have logic that will detect the environment and only run the statements that will work. -- Regards, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Version issues
You can use Application.Version and IF/THEN logic re OpenText:
If Val(Application.Version) 9 Then ...OpenText with TrailingMinus Else ...OpenText without TrailingMinus End If With regard to CreateObject("Scripting.FileSystemObject") I'd first ask why you're using it. It may be possible to replace it with something that works under WinNT. If not you have to tell users they can't run your app by using error trapping: On Error Goto BadOS Set fso = CreateObject("Scripting.FileSystemObject") ...other stuff Exit Sub BadOS: Msgbox "You need scripting" End Sub -- Jim "John Keith" wrote in message ... |I am having fits trying to deploy a spreadsheet with VBA. | | I have developed the sheet in Excel2002-SP3(loaded from XP Office prof 2003) | on Windows2000 SP4 | It works fine on that platform... | it also works on a Win2000 SP2 platform. | | On a NT based PC runing Excel2000 this line gives a compile error: | Set fso = CreateObject("Scripting.FileSystemObject") | | On win2000 running Excel2002-sp1... | Complie error on the "TrailingMinusNumbers:=True" of the Workbook.OpenText | cmd. Removed that option completely (since its optional anyway) and then the | macro runs up to the OpenText command then it gets a "Runtime error 1004: | Method OpenText of object Workbooks failed" note: the FileSystemObject | statement above executed fine on this one. | | How can I code these statements so that they are version independent? Or | have logic that will detect the environment and only run the statements that | will work. | | -- | Regards, | John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Version issues
As a general rule, you should develop on the oldest version of excel for
which the code will be run. Particularly if you are using the recorder. If you don't want to do that, go to the oldest version and painstakingly do what you are doing. -- Regards, Tom Ogilvy "John Keith" wrote in message ... I am having fits trying to deploy a spreadsheet with VBA. I have developed the sheet in Excel2002-SP3(loaded from XP Office prof 2003) on Windows2000 SP4 It works fine on that platform... it also works on a Win2000 SP2 platform. On a NT based PC runing Excel2000 this line gives a compile error: Set fso = CreateObject("Scripting.FileSystemObject") On win2000 running Excel2002-sp1... Complie error on the "TrailingMinusNumbers:=True" of the Workbook.OpenText cmd. Removed that option completely (since its optional anyway) and then the macro runs up to the OpenText command then it gets a "Runtime error 1004: Method OpenText of object Workbooks failed" note: the FileSystemObject statement above executed fine on this one. How can I code these statements so that they are version independent? Or have logic that will detect the environment and only run the statements that will work. -- Regards, John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Version issues
Jim, Thanks for the quick responce!
On the TrailingMinusSign option, I really dont need it for the data i'm loading so I can have just one way of using the OpenText method. The problem is that the OpenText doesn't seem to be supported by Excel2002-SP1 (or at least it gets the "Runtime error 1004: Method OpenText of object Workbooks failed") error and I dont know why. (I'd prefer to just design the code using the lowest common denominator of the syntax) I'm using the the file system object so I can get to the shortname property. This is being used by FTP.EXE (which requires 8.3 style names). And I did not want to limit my users choices on where they want to store this spreadsheet. path = Workbooks("CAFTMAST.xls").path ChDir path Set fso = CreateObject("Scripting.FileSystemObject") Set f = fso.GetFolder(path) shortpath = f.shortpath Is there some form of VB syntax that will work for all (or most) of the kinds of platforms? Or I can still go back to using If-Else to execute the "right" kind of code for the platform. -- Regards, John "Jim Rech" wrote: You can use Application.Version and IF/THEN logic re OpenText: If Val(Application.Version) 9 Then ...OpenText with TrailingMinus Else ...OpenText without TrailingMinus End If With regard to CreateObject("Scripting.FileSystemObject") I'd first ask why you're using it. It may be possible to replace it with something that works under WinNT. If not you have to tell users they can't run your app by using error trapping: On Error Goto BadOS Set fso = CreateObject("Scripting.FileSystemObject") ...other stuff Exit Sub BadOS: Msgbox "You need scripting" End Sub -- Jim "John Keith" wrote in message ... |I am having fits trying to deploy a spreadsheet with VBA. | | I have developed the sheet in Excel2002-SP3(loaded from XP Office prof 2003) | on Windows2000 SP4 | It works fine on that platform... | it also works on a Win2000 SP2 platform. | | On a NT based PC runing Excel2000 this line gives a compile error: | Set fso = CreateObject("Scripting.FileSystemObject") | | On win2000 running Excel2002-sp1... | Complie error on the "TrailingMinusNumbers:=True" of the Workbook.OpenText | cmd. Removed that option completely (since its optional anyway) and then the | macro runs up to the OpenText command then it gets a "Runtime error 1004: | Method OpenText of object Workbooks failed" note: the FileSystemObject | statement above executed fine on this one. | | How can I code these statements so that they are version independent? Or | have logic that will detect the environment and only run the statements that | will work. | | -- | Regards, | John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Version issues
I knew there was a reason I kept that old DOS machine :)
But thats a good rule to follow, I think I will add it to my bag-of-tricks I will try recording an opentext on the SP-1 based PC and see what it returns -- Regards, John "Tom Ogilvy" wrote: As a general rule, you should develop on the oldest version of excel for which the code will be run. Particularly if you are using the recorder. If you don't want to do that, go to the oldest version and painstakingly do what you are doing. -- Regards, Tom Ogilvy "John Keith" wrote in message ... I am having fits trying to deploy a spreadsheet with VBA. I have developed the sheet in Excel2002-SP3(loaded from XP Office prof 2003) on Windows2000 SP4 It works fine on that platform... it also works on a Win2000 SP2 platform. On a NT based PC runing Excel2000 this line gives a compile error: Set fso = CreateObject("Scripting.FileSystemObject") On win2000 running Excel2002-sp1... Complie error on the "TrailingMinusNumbers:=True" of the Workbook.OpenText cmd. Removed that option completely (since its optional anyway) and then the macro runs up to the OpenText command then it gets a "Runtime error 1004: Method OpenText of object Workbooks failed" note: the FileSystemObject statement above executed fine on this one. How can I code these statements so that they are version independent? Or have logic that will detect the environment and only run the statements that will work. -- Regards, John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Version issues
"Runtime error 1004:
I don't know why that's failing either but I don't think it's an Excel version issue. Does it open manually? For getting a short path you could try this Windows API approach: Public Declare Function GetShortPathName Lib "kernel32" Alias "GetShortPathNameA" _ (ByVal lpszLongPath As String, ByVal lpszShortPath As String, _ ByVal cchBuffer As Long) As Long Sub Shortpath() Dim ShortPathName As String Dim LongPathName As String Dim Res As Long LongPathName = "C:\Documents and Settings\MyLongFile.xls" ShortPathName = String(255, " ") Res = GetShortPathName(LongPathName, ShortPathName, 255) MsgBox Left(ShortPathName, Res) End Sub -- Jim "John Keith" wrote in message ... | Jim, Thanks for the quick responce! | | On the TrailingMinusSign option, I really dont need it for the data i'm | loading so I can have just one way of using the OpenText method. The problem | is that the OpenText doesn't seem to be supported by Excel2002-SP1 (or at | least it gets the "Runtime error 1004: Method OpenText of object Workbooks | failed") error and I dont know why. (I'd prefer to just design the code | using the lowest common denominator of the syntax) | | I'm using the the file system object so I can get to the shortname property. | This is being used by FTP.EXE (which requires 8.3 style names). And I did | not want to limit my users choices on where they want to store this | spreadsheet. | | path = Workbooks("CAFTMAST.xls").path | ChDir path | Set fso = CreateObject("Scripting.FileSystemObject") | Set f = fso.GetFolder(path) | shortpath = f.shortpath | | Is there some form of VB syntax that will work for all (or most) of the | kinds of platforms? Or I can still go back to using If-Else to execute the | "right" kind of code for the platform. | | -- | Regards, | John | | | "Jim Rech" wrote: | | You can use Application.Version and IF/THEN logic re OpenText: | | If Val(Application.Version) 9 Then | ...OpenText with TrailingMinus | Else | ...OpenText without TrailingMinus | End If | | With regard to CreateObject("Scripting.FileSystemObject") I'd first ask why | you're using it. It may be possible to replace it with something that works | under WinNT. If not you have to tell users they can't run your app by using | error trapping: | | On Error Goto BadOS | Set fso = CreateObject("Scripting.FileSystemObject") | ...other stuff | Exit Sub | BadOS: | Msgbox "You need scripting" | End Sub | | | -- | Jim | "John Keith" wrote in message | ... | |I am having fits trying to deploy a spreadsheet with VBA. | | | | I have developed the sheet in Excel2002-SP3(loaded from XP Office prof | 2003) | | on Windows2000 SP4 | | It works fine on that platform... | | it also works on a Win2000 SP2 platform. | | | | On a NT based PC runing Excel2000 this line gives a compile error: | | Set fso = CreateObject("Scripting.FileSystemObject") | | | | On win2000 running Excel2002-sp1... | | Complie error on the "TrailingMinusNumbers:=True" of the Workbook.OpenText | | cmd. Removed that option completely (since its optional anyway) and then | the | | macro runs up to the OpenText command then it gets a "Runtime error 1004: | | Method OpenText of object Workbooks failed" note: the FileSystemObject | | statement above executed fine on this one. | | | | How can I code these statements so that they are version independent? Or | | have logic that will detect the environment and only run the statements | that | | will work. | | | | -- | | Regards, | | John | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I save an Excel 97-2003 version or 2007 version for Mac 200 | Excel Discussion (Misc queries) | |||
Wrap Text: Print Version different than Screen Version | Excel Discussion (Misc queries) | |||
How can I get the same colors from 2003 version to 2007 version? | Excel Discussion (Misc queries) | |||
Help Required!!! Macro to load data from version 1 to version 2 | Excel Worksheet Functions | |||
Recover earlier version of excel sheet after new version saved? | Excel Discussion (Misc queries) |