The code that you quoted worked fine? If so, then you have the OCX control set properly. Make sure that you set it for other workbooks if needed.
Getting the right servername, username and password will vary. Notice the differences in the anonymous Simtel logon and the Earthlink logon. Get an ftp program like wsftp and try logging on with it first.
Wow... I guess this is why I use the BAT/Shell method exclusively, the Object method has always been problematic for me. This bugs me, because I generally prefer to use Object Models. BAT text files are sort of messy, but they do the job.
I actually have a function that can "look inside" CSV files on an FTP site to check an internal datestamp. It just starts the DL, waits a second, and then KILLs the Shell Process. (note, this ONLY works on text files)
Hey londoner, keep trying Hobs's code, sounds like you are pretty close. But if you give up, let me know and I can post some BAT method code for you.
If you do a BAT file example Dr.K, the Simtel examples in post #19 would be a good way to contrast both methods. If you don't post one soon, I may post one just to complete this thread.
While I do like DOS methods, I generally have to use an API method called ExecCmd() as shells to DOS can cause timing issues.
The object method has its own issues with the ActiveX enable message nag. However, it is a fairly decent method otherwise.
Of course we can easily download a text file using this method and get what we need. Funny you should mention that as I have been considering using a text file to store version information for some files and doing that very thing. The files that I would get are on an http site so I would probably use an API method or a winHTTP method.
Originally Posted by Dr.K
Dr K - thanks for your input. I would be grateful if you could post the code for the BAT method of downloading a file from a password protected FTP site.
I think that although I am close to getting there with the Object method, I may struggle to find the last step needed to eliminate my errors.
Many thanks again.
The first link that I posted shows how to do it using the BAT file method.
You know, I was thinking about this, and I've decided that using BAT files in a Shell is totally justified for FTP, becuase of how primitive FTP is. I mean, as much as I like to use Objects, windows has a built in command line tool for FTP, why bother adding a reference to an OS component?
Plus, for larger files, the Shell command-line window provides feedback about the process to the end user.
I was just looking at my subs, and I realized that they have proliferated over time. If anybody else finds this useful, I might submit it as a KB entry. Suggestions/improvements welcome.
Ok, first you need a Module for your "Shell Subs". You can use the window scripting host to run Shell commands, but here I use Windows API calls. Add a blank module, and paste this code in:
[vba]
Option Explicit
Option Private Module
'API Function calls used by the Shell Subs
Private Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
lParam As Any) As Long
Private Declare Function GetModuleBaseName Lib "psapi.dll" _
Alias "GetModuleBaseNameA" (ByVal hProcess As Long, _
ByVal hModule As Long, ByVal lpBaseName As String, _
ByVal nSize As Long) As Long
'Constants used by the API Function calls
Private Const WM_CLOSE = &H10
Private Const PROCESS_QUERY_INFORMATION = &H400
Private Const STILL_ACTIVE = &H103
'
Sub ShellAndWait(PathName As String, Optional WindowState)
'ShellAndWait uses Windows API calls to force Excel to wait
'for the window opened by the Shell command to close before
'continuing to execute the VBA script.
'Window States (Per Help for Shell function):
' 1, 5, 9 Normal with focus.
' 2 Minimized with focus.
' 3 Maximized with focus.
' 4, 8 Normal without focus.
' 6, 7 Minimized without focus.
Dim hProg As Long
Dim hProcess As Long
Dim ExitCode As Long
'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)
'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub
Sub ShellAndWait2(PathName As String)
'this version allows for blind FTP of small files
'Really brief FTPs will crash the shell without a wait
Dim hProg As Long
Dim hProcess As Long
Dim ExitCode As Long
hProg = Shell(PathName, 0)
'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Application.Wait (Now + TimeValue("0:00:01"))
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub
Sub PartialFTP(InputFN As String, OutputFN As String)
'PartialFTP uses Windows API calls to: run a Shell command, force
'Excel to wait a specified time, then to kill the running FTP
'process, and then to wait for the shell window to close before
'continuing to execute the VBA script.
'InputFN = full name of Batch file that invokes an FTP download
'OutputFN = full name of file being FTPed
'Start running shell routine
Shell InputFN, 0
'wait until output file exists before proceeding
'(ie, wait for FTP transfer to begin)
Do Until Dir(OutputFN) <> Empty
'wait in intervals of about a quarter second
Application.Wait Now + 0.000003
Loop
'Let the FTP transfer run for 2 full seconds
Application.Wait Now + TimeValue("00:00:02")
'Kill the FTP process.
'(this returns control to the batch file, which then continues.)
Kill_ProcByName ("FTP.exe")
End Sub
Sub Kill_ProcByName(NameOfExe As String)
Dim oProcList As Object
Dim oWMI As Object
Dim oProc As Object
' step 1: create WMI object instance:
Set oWMI = GetObject("winmgmts:")
If IsNull(oWMI) = False Then
' step 2: create object collection of Win32 processes:
Set oProcList = oWMI.InstancesOf("win32_process")
' step 3: iterate through the enumerated collection:
For Each oProc In oProcList
' option to close a process:
If UCase(oProc.Name) = UCase(NameOfExe) Then
On Error Resume Next
oProc.Terminate (0)
On Error GoTo 0
End If 'IsNullUCase(oProc.Name) = UCase(NameOfExe)
Next 'oProc In oProcList
End If 'IsNull(oWMI) = False
' step 4: clear out the objects:
Set oProcList = Nothing
Set oWMI = Nothing
End Sub[/vba]
Here are generalized functions for DLing and checking the DIR. I usually make custom versions of the functions for specific things like CDing through a directory structure on the FTP site.
[vba]Function FTPDownload(URL As String, LoginID As String, LoginPW As String, _
FileToGet As String, Optional OutputPath As String) As Boolean
Dim strTempDL As String
Dim strOutput As String
Dim oFS As Object
Dim oTS As Object
FTPDownload = False
'check for temp directory, create it if neccesary
strTempDL = Environ("TEMP") & "\FTPTemp"
If Dir(strTempDL, vbDirectory) = Empty Then MkDir (strTempDL)
'if target file exists, delete it.
If Not Dir(strTempDL & "\" & FileToGet) = Empty Then
Kill strTempDL & "\" & FileToGet
'if the file is still there, raise error
If Not Dir(strTempDL & "\" & FileToGet) = Empty Then Exit Function
End If
'invoke File System Object
Set oFS = CreateObject("Scripting.FileSystemObject")
'Build new FTP batch file (over-writes the old one):
Set oTS = oFS.CreateTextFile(strTempDL & "\FTP.bat", True)
With oTS
.writeline "@echo off"
.writeline "echo."
.writeline "Echo Contacting FTP server"
.writeline "echo."
.writeline "ftp -v -s:" & strTempDL & "\FTP.dat"
.writeline "echo."
.writeline "echo ALL DONE!"
.writeline "echo."
.writeline "exit"
.Close
End With
'Build new FTP .DAT file (over-writes the old one):
Set oTS = oFS.CreateTextFile(strTempDL & "\FTP.dat", True)
With oTS
.writeline "open " & URL
.writeline LoginID
.writeline LoginPW
.writeline "verbose"
.writeline "binary"
.writeline "get " & FileToGet & " " & Chr(34) & strTempDL _
& "\" & FileToGet & Chr(34)
.writeline "quit"
.Close
End With
Set oTS = Nothing
'**run FTP BAT through Shell and wait
ShellAndWait strTempDL & "\FTP.bat"
Application.Windows(1).Activate
'If file was sucessfully DLed, copy it over
If FileLen(strTempDL & "\" & FileToGet) = 0 Then
On Error Resume Next
Kill strTempDL & "\" & FileToGet
On Error GoTo 0
FTPDownload = False
Else
'get final path for output file
If OutputPath = Empty Then
'if none was specified, use the desktop
strOutput = Environ("USERPROFILE") _
& "\Desktop\" & FileToGet
Else
If Right(OutputPath, 1) = "\" Then
strOutput = OutputPath & FileToGet
Else
strOutput = OutputPath & "\" & FileToGet
End If
End If
'delete output file (incase its there)
On Error Resume Next
Kill (strOutput)
On Error GoTo 0
oFS.CopyFile strTempDL & "\" & FileToGet, strOutput
FTPDownload = True
End If
'delete temp FTP files
On Error Resume Next
Kill (strTempDL & "\FTP.bat")
Kill (strTempDL & "\FTP.dat")
Kill (strTempDL & "\" & FileToGet)
On Error GoTo 0
Set oFS = Nothing
End Function
Function FTPDir(URL As String, LoginID As String, LoginPW As String) As String
Dim strTempDL As String
Dim oFS As Object
Dim oTS As Object
Set oFS = CreateObject("Scripting.FileSystemObject")
'check for temp directory, create it if neccesary
strTempDL = Environ("TEMP") & "\FTPTemp"
If Dir(strTempDL, vbDirectory) = Empty Then MkDir (strTempDL)
'invoke File System Object
Set oFS = CreateObject("Scripting.FileSystemObject")
'Delete outputfile (if exists)
On Error Resume Next
Kill strTempDL & "\out.txt"
On Error GoTo 0
'Build new FTP batch file (over-writes the old one):
Set oTS = oFS.CreateTextFile(strTempDL & "\FFS.bat", True)
With oTS
.writeline "ftp -v -s:" & strTempDL & "\FFS.dat > " & strTempDL & "\out.txt"
.writeline "exit"
.Close
End With
'Build new FTP .DAT file (over-writes the old one):
Set oTS = oFS.CreateTextFile(strTempDL & "\FFS.dat", True)
With oTS
.writeline "open " & URL
.writeline LoginID
.writeline LoginPW
.writeline "dir"
.writeline "quit"
.Close
End With
'Run through shell and wait
ShellAndWait strTempDL & "\FFS.bat", 0
'read in output file
Set oTS = oFS.OpenTextFile(strTempDL & "\out.txt", 1)
FTPDir = oTS.readall
oTS.Close
Set oTS = Nothing
Set oFS = Nothing
'delete temp FTP files
On Error Resume Next
Kill (strTempDL & "\FTP.bat")
Kill (strTempDL & "\FTP.dat")
Kill (strTempDL & "\out.txt")
On Error GoTo 0
End Function[/vba]
Use these functions like this:
[vba]Private Sub TestFTPDownload()
Application.StatusBar = "Downloading file from FTP site..."
If FTPDownload("URL", "ID", "Password", "monbmk.txt") = False Then
MsgBox "File download unsucessful.", vbCritical, "Error!"
Else
MsgBox "FTP File transfer sucessful.", vbOK, "Download complete"
End If
Application.StatusBar = False
End Sub
Private Sub TestFTPDIR()
Dim strDIR As String
Application.StatusBar = "Checking the FTP DIR..."
strDIR = FTPDir("URL", "ID", "Password")
If strDIR = Empty Then
MsgBox "Could not get DIR information.", vbCritical, "Error!"
Else
MsgBox strDIR, vbOK, "DIR contents:"
End If
Application.StatusBar = False
End Sub
[/vba]
If you want to find information about specific files, you'll need to do some text string parsing with InStr() and Mid().
Hope this helps.
Thanks for the code Dr.K. You could look through the KB entries and if one is not there then post it. It takes a long time to get them reviewed so be patient.
Here is the code that I use to wait for shelled process to finish. The last part shows an example using it. I just put all this in a module and use it in others.
[VBA]'http://support.microsoft.com/kb/q129796/
Private Type STARTUPINFO
cb As Long
lpReserved As String
lpDesktop As String
lpTitle As String
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Long
hStdInput As Long
hStdOutput As Long
hStdError As Long
End Type
Private Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessId As Long
dwThreadId As Long
End Type
'Enum enSW
' SW_Hide = 0
' SW_NORMAL = 1
' SW_MAXIMIZE = 3
' SW_MINIMIZE = 6
'End Enum
'Enum enPriority_Class
' NORMAL_PRIORITY_CLASS = &H20
' IDLE_PRIORITY_CLASS = &H40
' HIGH_PRIORITY_CLASS = &H80
'End Enum
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
lpApplicationName As String, ByVal lpCommandLine As String, ByVal _
lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
lpStartupInfo As STARTUPINFO, lpProcessInformation As _
PROCESS_INFORMATION) As Long
Private Declare Function CloseHandle Lib "kernel32" _
(ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long
Private Const INFINITE = -1&
Private Const STARTF_USESHOWWINDOW = &H1
Public Function ExecCmd(cmdLine$, Optional windowstyle As Integer = 0, _
Optional priorityclass As Integer = &H20)
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ret As Long
' Initialize the STARTUPINFO structure:
start.cb = Len(start)
start.dwFlags = STARTF_USESHOWWINDOW
start.wShowWindow = windowstyle
' Start the shelled application:
ret& = CreateProcessA(vbNullString, cmdLine$, 0&, 0&, 0&, _
priorityclass, 0&, vbNullString, start, proc)
' Wait for the shelled application to finish:
ret& = WaitForSingleObject(proc.hProcess, INFINITE)
Call GetExitCodeProcess(proc.hProcess, ret&)
Call CloseHandle(proc.hThread)
Call CloseHandle(proc.hProcess)
ExecCmd = ret&
End Function
Sub Form_Click()
Dim retval As Long
retval = ExecCmd("notepad.exe")
MsgBox "Process Finished, Exit Code " & retval
End Sub
[/VBA]
I'm trying to persevere with this code but I get the following error using the same FTP details as above:
You called Download but only Download1 exists. Change one or the other.
It worked fine for me. It works better than the DOS method for me. It it doesn't work for you, I suspect even ftp by DOS would not work.
To try the ftp by DOS method, do it manually to test initially. Connecting to Simtel fails sometimes if there are too many users logged on. Doing it via DOS, it hung on the Password entry.
e.g
Start > Run > cmd
ftp ftp.simtel.net
anonymous
krhobson@aaahawk.com
cd pub/simtelnet/msdos/info
binary
get ftp-list.zip c:/temp/ftp-list.zip
bye
If I get time and remember, I will try another anonymous ftp site to test.
Posting Permissions
Reply With Quote