Solved

VBA module running an API

Posted on 2001-07-20
15
899 Views
Last Modified: 2013-11-27
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
Comment
Question by:AS400John
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +2
15 Comments
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6301655
What type of VBA module?
0
 

Author Comment

by:AS400John
ID: 6301689
It is called within Access
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6301817
Does the DoEvent method, after the API call, make any difference?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:AS400John
ID: 6302366
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
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6302449
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
 
LVL 3

Accepted Solution

by:
nigelrowe earned 150 total points
ID: 6302516
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6304213
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6304216
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
 

Author Comment

by:AS400John
ID: 6308728
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6310030
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6310036
Optionally, Access has a property to do that. I don't remember what but it supposse similar to Excel ScreenUpdating.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6310050
Echo is the property i just talking about.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7156256
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7157051
Waiting...
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7182268
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question