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 ?
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 BufferEnd FunctionPrivate 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 IfEnd Sub
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 ?
Dim i as integer,var1 as string, var2 as string, var3 as string, var4 3, as string
Do until var1=""
Open in new window