1

I wrote some VBA code that calls ftp.exe via shell command. When the code runs, the shell command does not execute. However, if I step through the code in debug mode, it works every time. Here is the code:

Sub FTPFile(sSrc As String)
    Dim sHost As String
    Dim sUser As String
    Dim sPass As String
    Dim sDest As String
    Dim sFTPCmds As String
    Dim strConnect As String

    'Build up the necessary parameters
    sHost = "<redacted>"
    sUser = "<redacted>"
    sPass = "<redacted>"
    sDest = "\"

    'Write the FTP commands to a text file
    iFNum = FreeFile
    sFTPCmds = "<path redacted>" & "FTPCmd.tmp"
    Open sFTPCmds For Output As #iFNum
        Print #iFNum, "op " & sHost
        Print #iFNum, "user " & sUser & " " & sPass
        Print #iFNum, "cd " & sDest
        Print #iFNum, "put " & sSrc
        Print #iFNum, "bye"
    Close #iFNum

    Shell Environ("WINDIR") & "\System32\ftp.exe -n -s:" & sFTPCmds
End Sub

The only possible idea I came up with is that the user permissions that call this command differ based on whether the call occurs in debug mode or running, but I am unsure how to change the command.

Smandoli
  • 6,919
  • 3
  • 49
  • 83
Erik Wind
  • 11
  • 2
  • Thanks for the suggestion. When I tried that, I got error 53 file not found. So I then tried variations of that such as: Shell "" & Environ("WINDIR") & "\System32\ftp.exe -n -s:" & sFTPCmds & "" which resulted in the same problem I'm facing, and Shell "'" & Environ("WINDIR") & "\System32\ftp.exe -n -s:" & sFTPCmds & "'" which also yielded an error 53 file not found. – Erik Wind Apr 23 '16 at 19:46
  • I have no experience with this, but in general terms, if something works when debugging, but not when the code runs, then it could be a timing issue. Add that to "file not found" makes me wonder if the text file you're creating is not completely ready and released from lock-down when Shell executes? What happens if you put a meaningless for-loop in there that counts through to a high number, to give the system time to process the file? – Cindy Meister Apr 24 '16 at 07:30
  • `Debug.Print` the Shell command and run the line in PowerShell or Command Prompt. Do note spaces in path names require quote enclosure. – Parfait Apr 24 '16 at 12:45
  • @CindyMeister, thanks for the reply. File not found only occurred when I modifed the syntax as per the above suggestion. I don't get a file not found when the syntax is set up the way I had it, and the syntax is correct because it works in debug mode. It's also not an issue of needing to slow things down with a for loop - I thought that might have been it too. I stepped through all the code, waited a minute, then executed on the ftp command, and the same problem occurred. Had I stepped through instead, it would work. – Erik Wind Apr 30 '16 at 19:48

3 Answers3

2

Perhaps I can shed some light on this. I was experiencing a similar issue where I was creating a temporary pdf file and then using a shell command and pdftk to combine the temporary pdf file with another pdf and output the result. It was working perfectly when I stepped through the code, but during runtime I wasn't getting the output. As @Smandoli mentioned above, it is likely to be a timing issue.

My code was;

Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")

wsh.Run "pdftk.exe """ & pdf1 & """ """ & pdf2 & """ output """ & ActiveWorkbook.Path & "\" & outputFile & """"

After the shell command was initiated I was removing the temporary file using

Kill ActiveWorkbook.Path & "\" & pdf1

It turns out this was occurring before the shell command could execute.

Forcing the shell command to wait for return solved the issue, as shown in this post: Wait for shell command to complete. This post recommends using WSScript.Shell over Shell as it has a wait on return option.

Community
  • 1
  • 1
Peter Henry
  • 651
  • 5
  • 17
0

Usually this is a timing issue and it can be difficult to trace.

See some approaches here and here.

As a first step, add the DoEvents command at different spots. This prompts Windows to work on pending tasks outside the Access process.

Adding a timed loop to stave off a time-out is sometimes the answer, even though it seems unbearably kludgey.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Smandoli
  • 6,919
  • 3
  • 49
  • 83
  • Thanks for the suggestion, but it's not a timing issue. I can step through the code in debug mode, get to the ftp command, wait a minute, and then execute, and it won't work. Alternatively, if I do the same process, but step through the ftp command, it will work. – Erik Wind Apr 30 '16 at 19:49
  • You said in your post `if I step through the code in debug mode, it works every time.` I don't know how to interpret all this, but it needs to be presented clearly in your post. – Smandoli May 01 '16 at 01:15
0

I had a piece of VBA macro code where: 1) The macro prepares some data in the Excel spreadsheet, 2) Opens another Excel file, 3) Writes data to the opened file spreadsheet cells, 4) Saves and closes the opened file

The macro ran perfectly in the debugger. ...but not in real time from the application. It opened the other file and then stopped working. Nothing happened, the opened file was not updated nor saved and closed. Excel was not stuck.

I tried the delay loops and application.wait after a hint from a fried. No help. But then, I found DoEvents. Voila, it solved the problem. I put DoEvents before and after the file open and now it runs perfectly!

user3726075
  • 21
  • 3