We help IT Professionals succeed at work.

FTPputfile() failed in VBA

duncanb7
duncanb7 asked
on
3,193 Views
Last Modified: 2012-05-10
Dear Eg1044 and Expert,


I would like to know in your code provide at
https://www.experts-exchange.com/OS/Microsoft_Operating_Systems/MS_DOS/Q_26566712.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


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

Open in new window

Comment
Watch Question

Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
THe looping code I put in your code  that is only for testing and it  doesn't run Thanks, I change the buffer size to 65k that get it faster as normal.

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, 0) in your  FtpPutFileEx ()  ?whether your code is for both upload and download,
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("s", 10, Now()), "'FtpputfileEX" & var1 & var2 & var3 & var4'"
i=i+1
loop





  

Open in new window

Top Expert 2010

Commented:
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)

Author

Commented:
Thanks for your reply

Duncan
Top Expert 2010

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.