Solved: Automating FTP in Access

[Imdabaum]

http://www.15seconds.com/issue/981203.htm

I found this link and everything compiles, but I'm having an issue creating the connection.


....
Function GetFTPFile(sFile As String)
Dim lngINetConn
Dim lngINet
Dim blnRC As Boolean
Dim UserName As String
Dim Password As String
Dim serverName As String
Const ASCII_TRANSFER = 1
Const BINARY_TRANSFER = 2
serverName = "myftp.server.com (ftp://ftp.server.com)"
UserName = "USER"
Password = "*******"
blnRC = False
lngINet = InternetOpen("MyFTPControl", 1, vbNullString, vbNullString, 0)
If lngINet > 0 Then

'Breaks here
lngINetConn = InternetConnect(lngINet, serverName, ServerPortnServerPort, UserName, Password, 1, 0, 0)
'Need help on the above line. How do I determine what port I should be using?

....


Anyone have ideas on how I discover what port should be used?

[Imdabaum]

Okay. I've determined that we have a firewall problem. But I've found another way and so I am on to the next requirement in this procedure... Clicking the save button.

Const SW_SHOWMAXIMIZED = 3
Const SW_SHOWMINIMIZED = 2
Const SW_SHOWDEFAULT = 10
Const SW_SHOWMINNOACTIVE = 7
Const SW_SHOWNORMAL = 1
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hWnd As Long, _
ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Private Function OpenLocation(URL As String, _
WindowState As Long) As Long
'PURPOSE: Opens default browser to display URL
'RETURNS: module handle to executed application or
'Error Code ( < 32) if there is an error
'Use one of the constants in the declarations as
'the window state parameter
'can also be used to open any document associated with
'an application on the system (e.g., passing the name
'of a file with a .doc extension will open that file in Word)
Dim lHWnd As Long
Dim lAns As Long
lAns = ShellExecute(lHWnd, "open", URL, vbNullString, _
vbNullString, WindowState)

OpenLocation = lAns
'ALTERNATIVE: if not interested in module handle or error
'code change return value to boolean; then the above line
'becomes:
'OpenLocation = (lAns > 32)
End Function

I use this code to open the url with the filename at the end ie
-ftp://username:password@ftp.mysite.com/filename.csv.

Now when I run the process it simply opens the xls OpenDialog prompt. Open, Save, or Cancel. Does anyone know how to automatically force the Save button to click?
If I can get that far, I can massage it enough to get it saved in my desired directory.

[HiTechCoach]

I use this for FTPing:

FTP Client functions (http://www.hitechcoach.com/index.php?option=com_docman&task=doc_details&gid=24&Itemid=28)

You might be able to use this:

Download a file from a URL (http://www.hitechcoach.com/index.php?option=com_content&view=article&id=44:download-a-file-from-a-url&catid=27:vba)

Hope this helps ...

[Imdabaum]

I use this for FTPing:

FTP Client functions (http://www.hitechcoach.com/index.php?option=com_docman&task=doc_details&gid=24&Itemid=28)

You might be able to use this:

Download a file from a URL (http://www.hitechcoach.com/index.php?option=com_content&view=article&id=44:download-a-file-from-a-url&catid=27:vba)

Hope this helps ...

I have a similar FTP function that I would use. My problem is that my boss wants me to automate the ftp(Easy, with batch or vba) but internally we have a firewall that blocks us from connecting to the ftp unless we go through a browser and append the username and password into the url. Something I can't seem to do with batch or vba at this point aside from the way I did it... which opens the web browser and keeps it hidden until the FileDialog box appears.

[HiTechCoach]

I have a similar FTP function that I would use. My problem is that my boss wants me to automate the ftp(Easy, with batch or vba) but internally we have a firewall that blocks us from connecting to the ftp unless we go through a browser and append the username and password into the url. Something I can't seem to do with batch or vba at this point aside from the way I did it... which opens the web browser and keeps it hidden until the FileDialog box appears.

Did you look at the example for Downloading a file from a URL?

It is possible to pass the username and password on the URL like this:

http://username:password@domain.com/path/to/item

or

ftp://username:password@domain.com/path/to/item (ftp://username:password@domain.com/path/to/item)

[Imdabaum]

Does your download function get around the save file dialogue box? Because downloading by ftp://username:password@ftp.mysite.com is what I am currently using as previously posted.

I'll test it when I get to work tomorrow to get a visual of what your code produces. Thanks for your help. BTW. I am really enjoying the batcave. :thumb

[Imdabaum]

The download by URL code that you posted, doesn't actually do anything for me. It does compile, but no file is ever saved to my computer.

[HiTechCoach]

The download by URL code that you posted, doesn't actually do anything for me. It does compile, but no file is ever saved to my computer.

If there is no file, then it there was a problem.

Just to test it, try another URL. Even http://www.google.com can be used as a test.

[Imdabaum]

If there is no file, then it there was a problem.

Just to test it, try another URL. Even http://www.google.com can be used as a test.

I was able to download the image from http://www.google.com/intl/en_All/images/srpr/logo1w.png (http://www.google.com/intl/en_All/images/srpr/logo1w.png) without any problems. Any ideas on why it would fail for the ftp://username;password@ftp.site.com?

Yes, there was an error. Not a very helpful error (URLDownloadToFile = -2147024891), but it was noted. I wonder if this is still a problem with the Webwasher utility we have. Thanks for the ideas so far. I'll keep working on it.

Microsoft website says that the function returns S_OK, E_OUTOFMEMORY, & INET_E_DOWNLOAD_FAILURE. Is there a way to show what value is represented by each return value? Is E_OUTOFMEMORY = -2147024891? etc..

[Imdabaum]

I appreciate everyone's help on this issue. I spoke with help desk and then network adminstrators and it appears that the network administrators won't budge on opening ftp access. I think the solutions for Automating FTP downloads is answered in this thread, and so I am marking it solved. Especially since most of the problem seems to be the network administrators more so than our code.
:thumb

[HiTechCoach]

You're welcome.