Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Add Delay to Access VBA

    My Access 2003 VBA code exports data from a query to Excel and some edits are done to the worksheet from Access. I then save and close the workbook and use the Shell command to reopen the workbook for the user to view. I would like to be able to add a delay of a few seconds to the Access VBA code which I hope will ensure that the Excel file is closed before it is reopened with shell.

    Anyone know of a simple delay technique to do this. In Excel VBA I have used the code: "Application.Wait Now + TimeSerial(0, 0, 2)". When I tried this in Access, the text ".Wait" was highlighted and the message said "Method or data member not found".

    I am exploring this solution because, although I have no problem when I test the program with a desktop PC, my application tester (with a laptop) gets an error message that the file can not be found, but not every time, and yet the file does exist when looked for in Explore.
    Thanks.
    Jerry

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry - it is wine/cheese/TV time for me, but a thought:

    if you dump to a hidden, non-interactive Excel instance, manipulate the XLS until next Tuesday, and then show, make-interactive your Excel instance.

    1. no mess on the screen during export
    2. no user-originated stupidity like Auto-Filter half way throught the export (kills it stone dead!)
    3. no shell
    4. no reopening Excel delay

    all is in the early posts in the codebank (see stuff from pootle, and if you are insane, from me)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I am using this code to export to Excel:
    Code:
    DoCmd.TransferSpreadsheet acExport, 8,   "Query Name",  txtPath, False, ""
    I then create an Excel session to do the edits, then save and close, and then reopen with shell. What method do you recommend to do this all in one Excel session?
    Jerry

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    all is in the early posts in the codebank at the top of this forum.
    do have a browse there (the early posts, including pootle's Excel posts, are indexed).
    there is a lot of good work by pootle flump on Excel handling from Access that knocks .transferspreadheet into the dust.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ditto Izy.


    Check out the code bank - the method is automation.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ditto Izy. Look at pootle's example in the code bank.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ditto Paul...


    ....errr
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Recursive ditto

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are you guys planning to keep the "love in" going for most of today as well





    .....
    hint it is in the code bank, mebbe nbot the way you want to do it, but its there, it works......

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Not to suggest an alternate to the code bank (cause the actual solution to the problem is in there), a simple answer to the specific question follows.

    I would like to be able to add a delay of a few seconds to the Access VBA code ... Anyone know of a simple delay technique to do this.

    Some code that works ins any VB(A) app (with the one exception being when your delay crosses midnight):
    Code:
    Public Sub fnPause(dblSeconds As Double)
    
        Dim sngTime As Single
    
        sngTime = Timer + dblSeconds
        Do
        Loop Until Timer > sngTime
    End Sub
    And, anywhere in code:
    Code:
    fnPause(X) ' X = the number of seconds to pause
    Automation is the answer for what you want to do.

    have fun,
    tc

  11. #11
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Pootle Flump, I followed your VBA example for exporting data to an Excel workbook, performing Excel formatting edits, saving and having the spreadsheet open for the user, all done in one Excel session as opposed to my original 3-session scheme. I needed to make my recordset source a tempory table because I could not use my query (based on another query) as the source.
    Thank you for the exceptional code. This issue is resolved.
    Jerry

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Glad it worked for you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •