• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 908
  • Last Modified:

VBA module running an API

I have a VBA module which downloads a file using FTPGETFILE. When the transfer is in progress the screen is open to repaint problems. How do I run the API so that windows still has control over the screen.
0
AS400John
Asked:
AS400John
  • 6
  • 4
  • 3
  • +2
1 Solution
 
nigelroweCommented:
What type of VBA module?
0
 
AS400JohnAuthor Commented:
It is called within Access
0
 
nigelroweCommented:
Does the DoEvent method, after the API call, make any difference?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AS400JohnAuthor Commented:
nigel- No, the thread stops at the FTPGETFILE and only starts again when it has finished so the DoEvent will not action until then.
0
 
nigelroweCommented:
OK John, What's the API call? Could you use the MSINET OCX for this? This returns immediate control to the calling app and runs in the background?
0
 
nigelroweCommented:
I've just been fiddling around with access, you can indeed insert this object in an access form (Insert, ActiveX Control, Microsoft Internet Transfer Control . This object contains probably all of the FTP functionalites provided by API calls. However, I haven't got time to test this as I'm at work. I use this thing in Web pages, I put it in an ActiveXControl of my own with this type of function..

Public Function FTPTransfer(ByVal a_ftpfilelist As String, ByVal a_idproclist As String, ByVal targetpath As String, ByVal a_url As String, ByVal a_idofficer As Long, ByVal file_ext As String) As String
Dim ftpfilelist As Variant, idproclist As Variant, i As Integer, present As String, filename As String, ftp_command As String
    ftpfilelist = Split(a_ftpfilelist, "|")
    idproclist = Split(a_idproclist, "|")
    FTPControl.URL = a_url
    FTPTransfer = ""
    For i = 0 To UBound(ftpfilelist)
        Randomize
        present = CStr(CLng(Rnd * 10000))
        While Len(present) <> 8
            present = "0" & present
        Wend
        filename = a_idofficer & "_" & present & "_" & idproclist(i) & file_ext
        ftp_command = "SEND " & targetpath & ftpfilelist(i) & " ptofiles/" & filename
        FTPControl.Execute , CStr(ftp_command)
        While FTPControl.StillExecuting
            DoEvents
        Wend
        If i = 0 Then
            FTPTransfer = filename
        Else
            FTPTransfer = FTPTransfer & "|" & filename
        End If
    Next
End Function

Public Sub FTPGet(ByVal a_ftpfilelist As String, ByVal targetpath As String, ByVal a_url As String)
Dim ftpfilelist As Variant, i As Integer, filename As String, ftp_command As String
    ftpfilelist = Split(a_ftpfilelist, "|")
    FTPControl.URL = a_url
    For i = 0 To UBound(ftpfilelist)
        filename = ftpfilelist(i)
        ftp_command = "GET ptofiles/" & filename & " " & targetpath & ftpfilelist(i)
        FTPControl.Execute , CStr(ftp_command)
        While FTPControl.StillExecuting
            DoEvents
        Wend
    Next
End Sub

You'll notice that I have a doevents in the two function and sub, as for me, it's exactly the opposite type of problem.
0
 
Richie_SimonettiIT OperationsCommented:
You could use a little trick with a timed event.
I mean, in a function your enable the timer and continue working with other code/procedures and so on. In the meantime, FTPGETFILE is called from within timer event and set timer enable to false.
I don't know with access but in excel you have an ontimer method.
If not, it is easy to create a True timer with an API cal and a callback procedure.
Search on MSDN for a working example (Article ID:Q180736 )
0
 
Richie_SimonettiIT OperationsCommented:
By the way, it is the best way to create a timer without a form in VB!
And, for the record, it could be used to do asyncronical calls too. Not pretty elegant but works and is less resource eater that timer control.
0
 
AS400JohnAuthor Commented:
Nigel- Thanks, being a relative novice to VB it may take me a while to try your code. I will keep you informed.

Richie- I tried your Ontimer event using SLEEP (Instead of FTPGETFILE) in the timer procedure. After SLEEP, I set the timer interval to 0 (Access does not execute the timer event when the interval = 0). I had a click event on a command button that executed DoEvents until the timer interval= 0. I still had re-paint problems. I will also have a look at MSDN Q180736. Thanks  
0
 
Richie_SimonettiIT OperationsCommented:
Well, if you have repainting problem and you have the hwnd for that problematic window, you could send a little message to that window disabling repainting and enabling again when you finished with FTP stuff.
See this link:

http://hjem.get2net.dk/vcoders/cm/tips/wincontrol_lockcontrols2.htm
0
 
Richie_SimonettiIT OperationsCommented:
Optionally, Access has a property to do that. I don't remember what but it supposse similar to Excel ScreenUpdating.
0
 
Richie_SimonettiIT OperationsCommented:
Echo is the property i just talking about.
0
 
DanRollinsCommented:
Hi AS400John,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept nigelrowe's comment(s) as an answer.

AS400John, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
Richie_SimonettiIT OperationsCommented:
Waiting...
0
 
Computer101Commented:
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now