CODEWITHSUNDEEP
excelvbabatch-filesftpwinscp
Kyle
Kyle

Reputation: 455

WinSCP "lcd .\" command ends up in C:\WINDOWS\system32 when executed in VBA, instead of user profile folder like when executed manually

Trying a simple file download from an SFTP server by calling a batch file from Excel VBA. Here's the bat:

@echo off

".\WinSCP.com" ^
/log="C:\Users\User\Documents\WinSCP.log" /ini=nul ^
 /command ^
    "open sftp://user:[email protected]/ -hostkey=""ssh-rsa 4096 y+key=""" ^
    "cd /" ^
    "lcd .\" ^
    "get file.TXT" ^
    "exit"

exit

The script runs perfect if I run the bat manually but if called from VBA it doesn't execute.

I've tried:

Set oSHELL = VBA.CreateObject("WScript.shell")
exitCode = oSHELL.Run("""C:\Users\" & Environ("username") & "\Folder\Folder Name Has Spaces\sftp.bat""", 0, True)
If exitCode <> 0 Then
  MsgBox "Failed to download TN1!", vbCritical, "Failure"
Else
  MsgBox "Downloaded successfully", vbOKOnly, "Success"
End If

and also, simply:

Call Shell("""C:\Users\" & Environ("username") & "\Folder\Folder Name Has Spaces\sftp.bat""", 0, True)

Edit: I pointed it to the program files (x86) winscp.com file and it generated a log. The lcd .\ is pointing to a restricted folder (system32):

> 2021-05-21 10:54:10.918 Script: lcd .\
< 2021-05-21 10:54:10.918 Script: C:\WINDOWS\system32

How can get around this? I cannot use an absolute file path because it will be different for different users based on their user name.

Upvotes: 0

Views: 480

Answers (1)

Martin Prikryl
Martin Prikryl

Reputation: 202494

The lcd .\ does nothing! If the batch file works, when you run it manually, it's only because the batch file is started in the right working directory from the beginning, not because of the lcd .\.

If you want to work in syncfolder subfolder of the current user's profile folder, no matter, where the batch file is executed from, use:

    "lcd ""%USERPROFILE%\syncfolder""" ^

Upvotes: 1

Related Questions