duncanb7
asked on
FTPputfile() failed in VBA
Dear Eg1044 and Expert,
I would like to know in your code provide at
https://www.experts-exchange.com/questions/26566712/How-to-send-command-to-cmd-exe-shell-in-windows-by-VBA.html#discussion,
why we need to ftpfileopen() to open since I just need upload files to the server. I run the code and found the
speed of FTP is really slow and three times slower than comparing to other softwer like pscp.exe. Is that because
you add some read file code in the subroutine or function to slow down
the upload process? Is it possible to modfiy the code to speed it up?
Each file to upload is just 10-20K only since it costs 30 seconds to upload ?
Why not using FTIPputfile() directly ? but I tried it fail .
FtpPutFile(hConnect, StrPtr(szServerFile), StrPtr(szLocalFile), FTP_TRANSFER_TYPE_UNKNOWN, 0), Why ?
Please advise
Duncan
I would like to know in your code provide at
https://www.experts-exchange.com/questions/26566712/How-to-send-command-to-cmd-exe-shell-in-windows-by-VBA.html#discussion,
why we need to ftpfileopen() to open since I just need upload files to the server. I run the code and found the
speed of FTP is really slow and three times slower than comparing to other softwer like pscp.exe. Is that because
you add some read file code in the subroutine or function to slow down
the upload process? Is it possible to modfiy the code to speed it up?
Each file to upload is just 10-20K only since it costs 30 seconds to upload ?
Why not using FTIPputfile() directly ? but I tried it fail .
FtpPutFile(hConnect, StrPtr(szServerFile), StrPtr(szLocalFile), FTP_TRANSFER_TYPE_UNKNOWN,
Please advise
Duncan
ublic Function FtpPutFileEx( _
ByVal szServer As String, _
ByVal szUser As String, _
ByVal szPassword As String, _
ByVal szLocalFile As String, _
ByVal szServerFile As String, _
ByVal filetype As Long) As Boolean
Dim dwStatus As Long
Dim dwLoFileSize As Long
Dim dwHiFileSize As Long
Dim dwPercent As Long
Dim a As Variant
hOpen = InternetOpenW(0, INTERNET_OPEN_TYPE_DIRECT, 0, 0, 0)
'// connect to the ftp server
hConnect = InternetConnectW(hOpen, StrPtr(szServer), INTERNET_DEFAULT_FTP_PORT, _
StrPtr(szUser), _
StrPtr(szPassword), INTERNET_SERVICE_FTP, INTERNET_FLAG_PASSIVE, 0)
If hConnect = 0 Then
CleanUp
Debug.Print "InternetConnectW()" & Err.LastDllError
FtpPutFileEx = False
Exit Function
End If
' Dim success As Boolean
' success = False
' Do Until success = True
' success = FtpPutFile(hConnect, StrPtr(szServerFile), StrPtr(szLocalFile), FTP_TRANSFER_TYPE_UNKNOWN, 0)
' Loop
' Exit Function
'MsgBox GetCurrentDirectory
'// get handle for filename that will be written to the ftp server
'Call FtpSetCurrentDirectory(hConnect, sdir)
' a = Time()
' Do Until hInternet <> 0 Or TimeValue(Time()) - TimeValue(a) > TimeValue("00:00:59")
hInternet = FtpOpenFileW(hConnect, StrPtr(szServerFile), GENERIC_WRITE, filetype, 0)
' Loop
If hInternet = 0 Then
CleanUp
Debug.Print "FtpOpenFile()" & Err.LastDllError
FtpPutFileEx = False
Exit Function
End If
'// get handle for local file to read bytes
hFile = CreateFileW(StrPtr("\\?\" & szLocalFile), GENERIC_READ, 0, 0, OPEN_EXISTING, 0, 0)
If hFile = INVALID_HANDLE_VALUE Then
CleanUp
Debug.Print "CreateFileW()" & Err.LastDllError
FtpPutFileEx = False
Exit Function
End If
' // get local file size for progress. This example supports
' only up to 2GB of status reporting progress.
dwLoFileSize = GetFileSize(hFile, dwHiFileSize)
'// read local file, write server file bytes
Do
If ReadFile(hFile, VarPtr(Buffer(0)), BUF_SIZE, dwReadBytes, 0) Then
If InternetWriteFile(hInternet, VarPtr(Buffer(0)), dwReadBytes, dwWrittenBytes) Then
' Track the amount of bytes written and percentage.
dwStatus = (dwStatus + dwWrittenBytes)
dwPercent = (dwStatus / dwLoFileSize) * 100
'Label1.Caption = dwPercent
End If
Else
Exit Do
End If
DoEvents
Loop Until dwReadBytes = 0
FtpPutFileEx = True
Debug.Print "Done"
'// cleanup
CleanUp
Erase Buffer
End Function
Private Sub CleanUp()
If hOpen <> 0 Then
InternetCloseHandle hOpen
hOpen = 0
End If
If hConnect <> 0 Then
InternetCloseHandle hConnect
hConnect = 0
End If
If hInternet <> 0 Then
InternetCloseHandle hInternet
hInternet = 0
End If
If hFile > 0 Then
CloseHandle hFile
hFile = INVALID_HANDLE_VALUE
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You don't want to remove the line because it creates the file on the ftp server it also is required to use InternetWriteFile. Think of it this way FtpOpenFile() is similar to Open "file.txt" for output as #1 (but rather its for working for ftp not local file system)
ASKER
Thanks for your reply
Duncan
Duncan
Duncan, I read your recent question, you appear to be uploading more than one file. You may want to seperate the connection and uploading so you only connect to the ftp server once and not 50 times as in the example.
The examples I showed will connect to server upload one file and disconnect. You may want to connect once then perform all uploads then disconnect it could increase the performance.
The examples I showed will connect to server upload one file and disconnect. You may want to connect once then perform all uploads then disconnect it could increase the performance.
ASKER
Hope you can asnwer me following question kindly
1-I still have question why you put hInternet = FtpOpenFileW(hConnect, StrPtr(szServerFile), GENERIC_WRITE, FTP_TRANSFER_TYPE_UNKNOWN,
otherwise you open szServer file is for what reasn ?
If I just need upload could I delete the those realted code like
2- I try to use application.ontime like as follows, ontime function is wokring and calling
the macro but it is still calling one by one, only second call is running until
the first of call ftpputfileEX is completed. Do you think any suggestion to let those
calling macro running at mutiltasking in order to speed up ftp upload ?
And I get other suggestion if ontime function is single tasking and I could try to
put the macro in new excel application and put it in the Thisworkbook and call
it at commandline with var1,var2,var3,var3 by shell command in VBA
like shell("ftpfile.xls var1, var2, var3, var4, Readonlymode", 0). And then looping shell
with different files name in order to simulate the mutltasking to ftp files. How do you think ?
Sub test()
Dim i as integer,var1 as string, var2 as string, var3 as string, var4 3, as string
Do until var1=""
var1= Range("A"&i)
var2= Range("B"&i)
var3= Range("C"&i)
var4=range("D"&i)
Call Application.OnTime(DateAdd
i=i+1
loop
Open in new window