GetShortPathName

[Milton Snider]

I have a module which is suppose to generate an xml file and then ftp it to
an ftp server. I am getting an error on the GetShortPathName function which
indicates Sub or Function not defined. The entire code is as follows. The
error is in the GetShortFileName Function which is last. I am on an XP
Professional machine. Any help is appriciated.
thanks
Milton

Dim sXMLPath As String
Public Function UpdateSQL()
On Error GoTo finish
sXMLPath = CurrentProject.Path & "\AccessEXP.xml"
If Dir(sXMLPath) <> "" Then
Kill sXMLPath
MsgBox sXMLPath
End If
ExportXML acExportTable, "mytable", sXMLPath
Call ExportFTP
finish: MsgBox "Error number " & Err.Number & ": " & Err.Description
End Function

Public Function outputhtml()
sXMLPath = CurrentProject.Path & "\AccessEXP.html"
If Dir(sXMLPath) <> "" Then
Kill sXMLPath
MsgBox sXMLPath
End If
ExportXML acExportTable, "mytable", sXMLPath

finish: MsgBox "Error number " & Err.Number & ": " & Err.Description
End Function

Public Sub ExportFTP()
Dim sSCR As String, sDir As String, sExe As String

sSCR = CurrentProject.Path & "\AccessFTP.scr"
sSCR = GetShortFileName(sSCR)

sDir = Environ$("COMSPEC")
sDir = Left$(sDir, Len(sDir) - Len(Dir(sDir)))
sExe = sDir & "ftp.exe -s:" & sSCR

Shell sExe, vbMaximizedFocus
End Sub

Public Function GetShortFileName(ByVal LongFileName As String) As String
Dim buffer As String, length As Long
' Prepare the receiving buffer
buffer = Space$(300)
length = GetShortPathName(LongFileName, buffer, Len(buffer))
' if return value was non-zero, estract the result
' else, it returns a null string (probably
GetShortFileName = Left$(buffer, length)
End Function

 

[RealJC]

Hi Milton,

GetShortPathName is an API function that needs to be declared before you can
use it. Add the following code to your module, ideally at the top with any
module level declarations:

Declare Function GetShortPathName Lib "kernel32" Alias "GetShortPathNameA"
(ByVal lpszLongPath As String, ByVal lpszShortPath As String, ByVal cchBuffer
As Long) As Long

Hope that helps!

RealJC

 

[Douglas J. Steele]

As you were told, GetShortPathName is a function. The declaration you were
given showed that it requires 3 parameters to be passed to it: a string
containing the long file path, a string to be used as a buffer to hold the
short file path, and a value indicating how big the buffer being passed is..
Note, too, that the function does not return the short file name: it returns
a value that indicates the length of the value returned as the short path,
or 0 if an error arose. See http://www.mvps.org/access/api/api0020.htm at
"The Access Web" for an example of how to use that function.

As to why the rest of your script is working, you're trying to pass VBA code
to the FTP interpretter. You have to use your VBA code to generate the
necessary scr file, and then have the FTP interpretter run that scr file.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ok,
Thanks for the GetShortPathName declaration.
Can you tell me why the following scr does not run properly. All the files
are in the correct directory. The dos window does open and the script does
attempt to run but with errors The errors are listed first:
Errors:
ftp> sAppPath = GetShortAppPath
Invalid command.
ftp> sSCR = sAppPath & "AccessFTP.scr"
Invalid command.
ftp> sEXPFile = sAppPath & "\AccessEXP.xml"
Invalid command.
ftp> sEXPFile = GetShortFileName(sEXPFile)
Invalid command.
ftp> sPutFile = "Put " & sEXPFile & " AccessEXP.xml"
Invalid command.
ftp>
Invalid command.
ftp> iFreeFile = FreeFile
Invalid command.
ftp> Open sSCR For Output As iFreeFile
Usage: Open host name [port]
ftp> Print #iFreeFile, "lcd " & sAppPath
Invalid command.
ftp> Print #iFreeFile, "open " & casacapricho.com
Invalid command.
ftp> Print #iFreeFile, "sendftp"
Invalid command.
ftp> Print #iFreeFile, "(e-mail address removed)"
Invalid command.
ftp> Print #iFreeFile, "cd incoming"
Invalid command.
ftp> Print #iFreeFile, "binary"
Invalid command.
ftp> Print #iFreeFile, sPutFile
Invalid command.
ftp> Print #iFreeFile, "bye"
Invalid command.
ftp> Close #iFreeFile
Not connected.
ftp>


Script:

sAppPath = GetShortAppPath
sSCR = sAppPath & "AccessFTP.scr"
sEXPFile = sAppPath & "\AccessEXP.xml"
sEXPFile = GetShortFileName(sEXPFile)
sPutFile = "Put " & sEXPFile & " AccessEXP.xml"

iFreeFile = FreeFile
Open sSCR For Output As iFreeFile
Print #iFreeFile, "lcd " & sAppPath
Print #iFreeFile, "open " & casacapricho.com
Print #iFreeFile, "sendftp"
Print #iFreeFile, "(e-mail address removed)"
Print #iFreeFile, "cd incoming"
Print #iFreeFile, "binary"
Print #iFreeFile, sPutFile
Print #iFreeFile, "bye"
Close #iFreeFile