CODEWITHSUNDEEP
vbams-access
Erik Wind
Erik Wind

Reputation: 11

VBA that runs shell command works only in debug mode

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.

Upvotes: 1

Views: 1236

Answers (3)

Smandoli
Smandoli

Reputation: 7019

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.

Upvotes: 0

Peter Henry
Peter Henry

Reputation: 747

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.

Upvotes: 2

user3726075
user3726075

Reputation: 31

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!

Upvotes: 0

Related Questions