bfuchs
asked on
Looking to convert script into VBA code.
Hi Experts,
I have the following script downloading new files from a FTP server into a pc local folder.
Wondering if someone can help me convert it to VBA code, so I can integrate into my MS Access application and have full control over it?
Thanks in advance.
I have the following script downloading new files from a FTP server into a pc local folder.
Wondering if someone can help me convert it to VBA code, so I can integrate into my MS Access application and have full control over it?
# Load WinSCP .NET assembly
Add-Type -Path "C:\Program Files (x86)\WinSCP\WinSCPnet.dll"
# Set up session options
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
Protocol = [WinSCP.Protocol]::Sftp
HostName = "sftp.MySite.com"
UserName = "MyUserName"
Password = "MyPWD"
SshHostKeyFingerprint = "1234567890="
}
$session = New-Object WinSCP.Session
try
{
# Connect
$session.Open($sessionOptions)
# Transfer files
$remotePath = "H:\FTP\*"
$sourcePath = "/Outbox/*"
$destPath = "H:\FTP\"
$destPathNew = "H:\FTP\Caspio\"
$transferOptions = New-Object WinSCP.TransferOptions
$transferOptions.FileMask = "*PAT*.*;*Sch*.*|*Full*.*"
while($True)
{
try
{
$transferResult = $session.GetFiles($sourcePath, $destPath, $False, $transferOptions)
$transferResult.Check()
}
finally
{
foreach ($transfer in $transferResult.Transfers)
{
$session.GetFiles($transfer.FileName, $destPathNew, $False, $transferOptions)
Write-Host "Download of $($transfer.FileName) succeeded"
}
$destPathNew1
}
}
Write-Host "Waiting..."
Start-Sleep -Seconds 5
}
finally
{
$session.Dispose()
}
Thanks in advance.
Since you are using WinSCP, try the suggestions on their website for integrating into VBA:
Using WinSCP .NET Assembly from Visual Basic for Applications (VBA)
Using WinSCP .NET Assembly from Visual Basic for Applications (VBA)
ASKER
Hi Experts,
The script is meant to do 2 tasks.
1- Check for new files under that FTP folder and download.
2- Copy the downloaded files to a separate folder.
From my understanding, those links provided are for basic FTP download only.
How will I accomplish the above?
PS. See following for more details
https://www.experts-exchange.com/questions/29112257/How-to-copy-only-new-files-from-FTP-site.html?anchor=a42644007¬ificationFollowed=210596930&anchorAnswerId=42644007#a42644007
@Eddie,
Your link does not contain an example of download.
Thanks,
Ben
The script is meant to do 2 tasks.
1- Check for new files under that FTP folder and download.
2- Copy the downloaded files to a separate folder.
From my understanding, those links provided are for basic FTP download only.
How will I accomplish the above?
PS. See following for more details
https://www.experts-exchange.com/questions/29112257/How-to-copy-only-new-files-from-FTP-site.html?anchor=a42644007¬ificationFollowed=210596930&anchorAnswerId=42644007#a42644007
@Eddie,
Your link does not contain an example of download.
Thanks,
Ben
Did you even check out the link I posted so that you could continue to use WinSCP? The way to do that would be to download a file listing from the FTP site, compare it to the local file listing and download only the files that are different.
Now, if you only want to copy NEW or changed files from FTP to a directory, I would suggest using Rsync. The algorithm used in this product has been around since the 70's and is bullet-proof and faster than FTP. Now, as far as using it in VBA, I don't know.
Now, if you only want to copy NEW or changed files from FTP to a directory, I would suggest using Rsync. The algorithm used in this product has been around since the 70's and is bullet-proof and faster than FTP. Now, as far as using it in VBA, I don't know.
ASKER
Hi,
https://rsync.samba.org/download.html
Would you have sample of code for that?
Thanks,
Ben
I would suggest using Rsync.Are you referring to this?
https://rsync.samba.org/download.html
Would you have sample of code for that?
Thanks,
Ben
Not for VBA, Ben. There is a Nuget package out that works using the same algorithm called OctoDiff.
ASKER
Hi Experts,
So far I'm really stuck with this script,-:(
See following
https://www.experts-exchange.com/questions/29112971/Script-causing-an-error-after-running-for-long-time.html
I understand that converting the full contents of it into VBA will be out of the scope of one post, however perhaps we can split this into a few parts...
So lets start by the task of downloading the newest files from a FTP server (in VBA of course).
Can someone post here a working example?
Thanks,
Ben
So far I'm really stuck with this script,-:(
See following
https://www.experts-exchange.com/questions/29112971/Script-causing-an-error-after-running-for-long-time.html
I understand that converting the full contents of it into VBA will be out of the scope of one post, however perhaps we can split this into a few parts...
So lets start by the task of downloading the newest files from a FTP server (in VBA of course).
Can someone post here a working example?
Thanks,
Ben
Ben, did you even check out the link I posted? It shows some VBA code that does what you are asking.
ASKER
Hi Eddie,
I did replied to that above
https://www.experts-exchange.com/questions/29112291/Looking-to-convert-script-into-VBA-code.html?anchorAnswerId=42644658#a42644658
Thanks,
Ben
I did replied to that above
https://www.experts-exchange.com/questions/29112291/Looking-to-convert-script-into-VBA-code.html?anchorAnswerId=42644658#a42644658
Thanks,
Ben
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
I knew your 30 seconds can make my day-:)
Will test that and keep you posted.
Thanks,
Ben
I knew your 30 seconds can make my day-:)
Will test that and keep you posted.
Thanks,
Ben
ASKER
Hi,
Having trouble creating a reference to this dll
Tried installing the package, and had to close all thousand things on my pc, in order to restart..
but still cant find that library listed, and when I try to locate it manually I get a message you cant add a reference to that dll...
see attached.
Thanks,
Ben
Untitled.png
Having trouble creating a reference to this dll
Tried installing the package, and had to close all thousand things on my pc, in order to restart..
but still cant find that library listed, and when I try to locate it manually I get a message you cant add a reference to that dll...
see attached.
Thanks,
Ben
Untitled.png
You must have 64-bit DLL? VB6 is 32-bit only, if I'm not mistaken, Why you using VB6, anyway? Oh, my mistake. It is MSAccess. Is it 64-bit?
ASKER
its Access 32 bit.
You may have to DL a 32-bit version of WinSCP to get the DLL. Let me try something else.
Register for COM by using this and then it appears in the list of references:
https://winscp.net/eng/docs/library_install#registering
https://winscp.net/eng/docs/library_install#registering
ASKER
okay, I got pass that already...
Now how do I call this sub, what do I pass as param?
Ben
Now how do I call this sub, what do I pass as param?
Public Sub Download(ByRef mySession As Session)
Thanks,Ben
This will create a new WinSCP session:
That is what is passed. Please read the link I provided, that would have prevented the last 2 exchanges.
Dim mySession As New Session
That is what is passed. Please read the link I provided, that would have prevented the last 2 exchanges.
ASKER
Thanks Eddie.
This at least started to work, now will test that and open new post for the remaining tasks.
This at least started to work, now will test that and open new post for the remaining tasks.
ASKER
Hi Eddie,
Here are some additional's...
https://www.experts-exchange.com/questions/29113458/WinSCP-function-modifications.html#questionAdd
Thanks,
Ben
Here are some additional's...
https://www.experts-exchange.com/questions/29113458/WinSCP-function-modifications.html#questionAdd
Thanks,
Ben
[ http://vbnet.mvps.org/index.html?code/internet/ftpdownload.htm ]
PS. I have not copy'n'pasted the content here due to Randy's copyright banner:
Open in new window