Forum » Scripting / Automation »

No Idea What I am Doing

DBCox Guest

No Idea What I am Doing

2013-09-24 20:33

Hi everyone,

I found WinSCP while searching for an alternative to command line ftp functions using MS Excel. Let me warn you that I am NOT a programmer and barely understand the very basics. One of the FTP sites I have been connecting to for years will no longer connect using my VBA code (that I did not develop). I searched and found that is a someone common problem and has to be corrected on the server end, which isn't going to happen. I contacted them and they swear everything is OK.

However, I can connect with a client (filezilla) without a problem. When looking for a client that I can control with VBA code, I found WinSCP. Problem is I cannot get past the first step! I cannot seem to register the WinSCP console interface .NET wrapper (not in the list, and I browsed to it with no luck).

All I need to do is have Excel connect to a FTP site, upload a single file, and quit. Can someone point me in the right direction, either with example code, or a support file that will outline it?

Thanks!

David

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 41,442
Location:: Prague, Czechia

Re: No Idea What I am Doing

2013-09-26

If you are going to replace the Windows build-in ftp with WinSCP, you probably do not need the WinSCP .NET assembly. Just use WinSCP scripting interface the same way you were using the ftp.
See https://winscp.net/eng/docs/guide_automation

Reply with quote

DBCox Guest

2013-09-26 19:50

Thank you for the info! I am having trouble finding a VBA example that works. As I stated, I am not a programmer...

I found this and modified it, but it isn't working:

Sub Macro1()
'
  ' Setup session options
    Dim mySessionOptions As New SessionOptions
    With mySessionOptions
        .Protocol = Protocol_Sftp
        .HostName = "****"
        .UserName = "****"
        .Password = "****"
 
    End With
    
    ' Connect
    mySession.Open mySessionOptions
    
    ' Upload files
    Dim myTransferOptions As New TransferOptions
    myTransferOptions.TransferMode = TransferMode_Binary
     
    Dim transferResult As TransferOperationResult
    Set transferResult = mySession.PutFiles("C:\Directory and filename was here", False, myTransferOptions)
     
    ' Throw on any error
    transferResult.Check
     
    ' Display results
    Dim transfer As TransferEventArgs
    For Each transfer In transferResult.Transfers
        MsgBox "Upload of " & transfer.Filename & " succeeded"
    Next
    
End Sub

I get the following error:

"Compile error:

User-defined type not defined"

Thanks!

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 41,442
Location:: Prague, Czechia

2013-09-30

Have added a reference to WinSCP .NET assembly?
See https://winscp.net/eng/docs/library_vb

But note, I have recommended you NOT to use the assembly. Anyway, the choice is yours.

Reply with quote

flow'd Guest

sftp using scheduled task

2014-01-28 23:47

Hi

Complete novice seeking assistence. I have a file that i would like to ftp to a web server to update an exl file which is used be a web page.

Having had a vba soution working using

taskID = VBA.Shell("C:\ftp.exe -s:C\waitlist.scr", VBA.vbMinimizedNoFocus)

waitlist.scr

open xxx.xxx.xx.xx
xxx
xxx
cd var/www/dashboard
delete waitlistweek.xml
put waitlistweek.xml
quit

then it stopped. I investigated asuggested vba solution

Public Sub SftpPut()
Const cstrSftp As String = """C:\Program Files\PuTTY\pscp.exe"""
Dim strCommand As String
Dim pUser As String
Dim pPass As String
Dim pHost As String
Dim pFile As String
Dim pRemotePath As String

pUser = "xxx"
pPass = xxx"
pHost ="xxx.xxx.xx.xx"
pFile = "C:/waitlistweek.xml"
pRemotePath = "/var/www/dashboard/"

strCommand = cstrSftp & " -sftp -l " & pUser & " -pw " & pPass & _
" " & pFile & " " & pHost & ":" & pRemotePath
Debug.Print strCommand
Shell strCommand, 1 ' vbNormalFocus '
End Sub

I spent way too much time yesterday trying to get this working without success. The file just wouldn't appear on the target server.

Tried to reference 'the WinSCP console interface .NET wrapper' after the suggested install but could not find the reference. ??

Have reverted to scheduled tasks as suggested by prikryl.

"C:\Program Files\WinSCP\WinSCP.exe" /command "option batch abort" "option confirm off" "open sftp://xxx:xxx@xxx.xxx.xx.xx" "put C:\waitlistweek.xml /var/www/dashboard/" "exit" /log

This appears not to be working.

Does replacing a file have any inpact upon the scheduled task process?

Any thoughts would be appreciated.

cheers

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 41,442
Location:: Prague, Czechia

Re: sftp using scheduled task

2014-02-03

flow'd wrote:

Tried to reference 'the WinSCP console interface .NET wrapper' after the suggested install but could not find the reference. ??

That does it mean "but could not find the reference"?

Have reverted to scheduled tasks as suggested by prikryl.

"C:\Program Files\WinSCP\WinSCP.exe" /command "option batch abort" "option confirm off" "open sftp://xxx:xxx@xxx.xxx.xx.xx" "put C:\waitlistweek.xml /var/www/dashboard/" "exit" /log

This appears not to be working.

What does it mean "This appears not to be working"?

Reply with quote

flow'd
Joined:: 2014-01-31
Posts:: 7
Location:: Christchurch NZ

Re: sftp using scheduled task

2014-02-11 02:31

martin wrote:

flow'd wrote:

Tried to reference 'the WinSCP console interface .NET wrapper' after the suggested install but could not find the reference. ??
That does it mean "but could not find the reference"?

Apologies for lack of clarity. When trying to select the Reference-VBAProject what is the precise name of reference as I do not see anything like WinSCP . Have I failed to install correctly

Have reverted to scheduled tasks as suggested by prikryl.

"C:\Program Files\WinSCP\WinSCP.exe" /command "option batch abort" "option confirm off" "open sftp://xxx:xxx@xxx.xxx.xx.xx" "put C:\waitlistweek.xml /var/www/dashboard/" "exit" /log

This appears not to be working.
What does it mean "This appears not to be working"?

It runs, does not throw any errors but the file does not trasfer.

Thanks for your help.

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 41,442
Location:: Prague, Czechia

Re: sftp using scheduled task

2014-02-11

flow'd wrote:

Apologies for lack of clarity. When trying to select the Reference-VBAProject what is the precise name of reference as I do not see anything like WinSCP . Have I failed to install correctly

Name is WinSCP console interface .NET wrapper.
See https://winscp.net/eng/docs/library_vb

Have reverted to scheduled tasks as suggested by prikryl.

"C:\Program Files\WinSCP\WinSCP.exe" /command "option batch abort" "option confirm off" "open sftp://xxx:xxx@xxx.xxx.xx.xx" "put C:\waitlistweek.xml /var/www/dashboard/" "exit" /log

This appears not to be working.
What does it mean "This appears not to be working"?

It runs, does not throw any errors but the file does not trasfer.[/quote]
Try using winscp.com, so that you can see an output of the script, including errors, if any.
Also add /log=path_to_log switch and inspect the log. Or attach it here.

Reply with quote

flow'd
Joined:: 2014-01-31
Posts:: 7
Location:: Christchurch NZ

2014-02-11 21:47

Thx for the reply. Registering produced;

C:\>C:\Windows\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe WinSCPnet.dll /codebase /tlb

RegAsm : error RA0000 : Unable to locate input assembly 'WinSCPnet.dll' or one of its dependencies.

'WinSCPnet.dll' is the the directory C:\Program Files\WinSCP

Any thoughts would be appreciated.

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 41,442
Location:: Prague, Czechia

2014-02-12

flow'd wrote:

Thx for the reply. Registering produced;

C:\>C:\Windows\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe WinSCPnet.dll /codebase /tlb

RegAsm : error RA0000 : Unable to locate input assembly 'WinSCPnet.dll' or one of its dependencies.

'WinSCPnet.dll' is the the directory C:\Program Files\WinSCP

Well, RegAsm.exe does not/cannot scan whole hard disk to look for the assembly. You need to give it full path to the .dll:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe "C:\Program Files\WinSCP\WinSCPnet.dll" /codebase /tlb

Reply with quote

flow'd
Joined:: 2014-01-31
Posts:: 7
Location:: Christchurch NZ

2014-02-13 02:45

. 2014-02-13 14:35:00.887 File: "G:\Division\RAD\netTestUpload\waitlistweek.xml"
* 2014-02-13 14:35:00.887 (EOSError) System Error. Code: 3.
* 2014-02-13 14:35:00.903 The system cannot find the path specified
. 2014-02-13 14:35:00.903 Asking user:
. 2014-02-13 14:35:00.919 File or folder 'G:\Division\RAD\netTestUpload\waitlistweek.xml' does not exist. ("System Error. Code: 3.
. 2014-02-13 14:35:00.919 The system cannot find the path specified")

Reply with quote

flow'd

2014-02-13 02:48

amd success on the installation. Much appreciated.

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 41,442
Location:: Prague, Czechia

2014-02-13

flow'd wrote:

. 2014-02-13 14:35:00.887 File: "G:\Division\RAD\netTestUpload\waitlistweek.xml"
* 2014-02-13 14:35:00.887 (EOSError) System Error. Code: 3.
* 2014-02-13 14:35:00.903 The system cannot find the path specified
. 2014-02-13 14:35:00.903 Asking user:
. 2014-02-13 14:35:00.919 File or folder 'G:\Division\RAD\netTestUpload\waitlistweek.xml' does not exist. ("System Error. Code: 3.
. 2014-02-13 14:35:00.919 The system cannot find the path specified")

Please give us some context.

Reply with quote

flow'd
Joined:: 2014-01-31
Posts:: 7
Location:: Christchurch NZ

Scheduled task using winscp

2014-02-13 09:50

martin wrote:

flow'd wrote:

. 2014-02-13 14:35:00.887 File: "G:\Division\RAD\netTestUpload\waitlistweek.xml"
* 2014-02-13 14:35:00.887 (EOSError) System Error. Code: 3.
* 2014-02-13 14:35:00.903 The system cannot find the path specified
. 2014-02-13 14:35:00.903 Asking user:
. 2014-02-13 14:35:00.919 File or folder 'G:\Division\RAD\netTestUpload\waitlistweek.xml' does not exist. ("System Error. Code: 3.
. 2014-02-13 14:35:00.919 The system cannot find the path specified")
Please give us some context.

As suggested I used winscp.com and logged the events. Can you see anything wrong with the 'file or folder'

Thx

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 41,442
Location:: Prague, Czechia

Re: Scheduled task using winscp

2014-02-13

I'd like to see a command that produced the error, in case you have syntax error there.

Otherwise I can only suggest you that the file does not exist or that WinSCP process does not see the G: drive (such as when it is run in a session, like Windows Scheduler session, that does not have the mapped drives).

Reply with quote

flow'd

Scheduled task using winscp

2014-02-13 22:34

"C:\Program Files\WinSCP\WinSCP.com" /command "option batch abort" "option confirm off" "open sftp://root:xxxxxx@xxxxxx.xx.xx" "put G:\Division\RAD\netTestUpload\waitlistweek.xml /var/www/dashboard/" "exit" "/log=H:\log.txt"

thx

Reply with quote

flow'd
Joined:: 2014-01-31
Posts:: 7
Location:: Christchurch NZ

Scheduled task using winscp

2014-02-13 23:37

Solved

"C:\Program Files\WinSCP\WinSCP.com" /command "option batch abort" "option confirm off" "open sftp://xx:xx@xx.xx.xx.xx" "put G:\Division\RAD\COMMON\testNetUpload\waitlistweek.xml /var/www/dashboard/" "exit" "/log=H:\log.txt"

It was a space in the production path causing the error. Out of interest what char should I have used when there is a space in the path?

Apologies for wasting your time.

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 41,442
Location:: Prague, Czechia

Re: Scheduled task using winscp

2014-02-28

flow'd wrote:

Out of interest what char should I have used when there is a space in the path?

See
https://winscp.net/eng/docs/commandline#syntax
https://winscp.net/eng/docs/scripting#syntax

Reply with quote

You can post new topics in this forum