Topic "No Idea What I am Doing"

Author Message
DBCox

Guest


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
Advertisements
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25034
Location: Prague, Czechia
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
_________________
Martin Prikryl
DBCox

Guest


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:

Code:
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!
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25034
Location: Prague, Czechia
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.
_________________
Martin Prikryl
flow'd

Guest


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
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25034
Location: Prague, Czechia
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"?

Quote:
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"?
_________________
Martin Prikryl
flow'd
[View user's profile]

Joined: 2014-01-31
Posts: 7
Location: Christchurch NZ
prikryl 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

Quote:
Quote:
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.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25034
Location: Prague, Czechia
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

Quote:
Quote:
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.
_________________
Martin Prikryl
flow'd
[View user's profile]

Joined: 2014-01-31
Posts: 7
Location: Christchurch NZ
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.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25034
Location: Prague, Czechia
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:

Code:
C:\Windows\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe "C:\Program Files\WinSCP\WinSCPnet.dll" /codebase /tlb
flow'd
[View user's profile]

Joined: 2014-01-31
Posts: 7
Location: Christchurch NZ
. 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")
flow'd
[View user's profile]

Joined: 2014-01-31
Posts: 7
Location: Christchurch NZ
amd success on the installation. Much appreciated.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25034
Location: Prague, Czechia
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.
_________________
Martin Prikryl
flow'd
[View user's profile]

Joined: 2014-01-31
Posts: 7
Location: Christchurch NZ
prikryl 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
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25034
Location: Prague, Czechia
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).
flow'd
[View user's profile]

Joined: 2014-01-31
Posts: 7
Location: Christchurch NZ
"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
flow'd
[View user's profile]

Joined: 2014-01-31
Posts: 7
Location: Christchurch NZ
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.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25034
Location: Prague, Czechia
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
Advertisements

You can post new topics in this forum






Search Site

What is WinSCP?

It is award-winning SFTP client, SCP client, FTPS client and FTP client integrated into one software program for file transfer to FTP server or secure SFTP server. [More]

And it's free!

Donate

About donations

$9   $19   $49   $99

About donations

Recommend

WinSCP Privacy Policy

WinSCP License