1

I've been tearing my hair out this afternoon. From Access VBA I've been issuing a Command line via a shell to FTP a file to a 3rd party server using cURL.exe.

Things were working great until I brought my code to production where it is now failing silently. I suspect the multiple unpredictable file paths producing "strCmd" are just too long to pass thru the Command shell. >> Is there a limit? <<

fShellRun (strCmd)

curl -k -T testfile.txt --ftp-ssl --ftp-pasv -u "username\$domain:password" ftp://ftp-domain.egnyte.com/Shared/testdirectory/

This is the Shell function I am using (not mine):

Function fShellRun(sCommandStringToExecute)

Dim oShellObject, oFileSystemObject, sShellRndTmpFile
Dim oShellOutputFileToRead, iErr

Set oShellObject = CreateObject("Wscript.Shell")
Set oFileSystemObject = CreateObject("Scripting.FileSystemObject")

sShellRndTmpFile = oShellObject.ExpandEnvironmentStrings("%temp%") & oFileSystemObject.GetTempName
On Error Resume Next
oShellObject.Run sCommandStringToExecute & " > " & sShellRndTmpFile, 0, True
iErr = Err.Number

'~on error goto 0
If iErr <> 0 Then
    fShellRun = ""
    Exit Function
End If

'~on error goto err_skip
fShellRun = oFileSystemObject.OpenTextFile(sShellRndTmpFile, 1).ReadAll
oFileSystemObject.DeleteFile sShellRndTmpFile, True

Exit Function

err_skip:
fShellRun = ""
oFileSystemObject.DeleteFile sShellRndTmpFile, True

End Function

I am noticing strCmd longer than ~200 chars fails silently.

Questions:

  1. Is there a string length limit using a command shell?

  2. How might I circumvent this limit?

Thanks!

Edit: The long command string (copy/paste from debug.print) works just fine in an open command window. Leads me to think there is an issue with the shell command itself. (?)

Mark Pelletier
  • 1,329
  • 2
  • 24
  • 42
  • According to this link, the max is a lot larger than 200: http://stackoverflow.com/questions/30437330/is-there-a-limit-in-the-text-length-that-you-can-use-as-a-parameter-if-yes-what – Wayne G. Dunn Feb 28 '17 at 22:49
  • @Wayne - thanks for the link. Hmm, my own poking around does not produce any conclusive into relative to using long string in command shell...or, at least what is up in my case. – Mark Pelletier Feb 28 '17 at 23:48
  • Running the Long command line from a Batch file via the shell command seems to work just fine. This may turn out to be my workaround, albeit not great as this forces me to create (and then Kill) a file on the user machine. Grr – Mark Pelletier Mar 01 '17 at 00:05
  • Did you determine if the limit is between 250 and 256? Maybe old age, but seems long ago there were always places where there was a 256 byte limit, and sometimes that would get decreased if the interface tagged on some control characters of it's own. I did a few searches but found nothing relating to less than ~2,000 characters. What about using TinyURL? that 'may' get around the issue depending on where the redirect comes into play. – Wayne G. Dunn Mar 01 '17 at 00:15
  • I did not experiment to determine the precise length trigger. I suppose I could mess with the filename to arrive at this number. But issue remains. – Mark Pelletier Mar 01 '17 at 01:33
  • In your non-VBA call, you do not use any temp file as VBA does? Is this needed? Are you downloading or uploading from FTP? Try just running command: `oShellObject.Run sCommandStringToExecute` – Parfait Mar 01 '17 at 01:40
  • @Parfait - I am exporting an Access table into the ProjectPath, then sending over to the 3rd party via FTP. Using cURL.exe as I need a PASV transfer. I'll try just running the command. Else, I will create & kill a BAT file silently,.. – Mark Pelletier Mar 01 '17 at 12:28

0 Answers0