"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
|
|
|