Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2002
    Location
    Kentucky
    Posts
    6

    Unanswered: Wait, pause, sleep, timer, AAAAAAAAAHHHHH

    I have a complex problem, but one I'm certain somebody out here can handle, as I am not yet a veteran to VBA world.

    I am running a sub inside of Access that is executed when a command button is pushed. It opens Excel and runs a macro. It then proceeds to run a second macro inside of Access based on the results of that macro run inside of Excel.

    Here's the issue. Excel opens and starts to run the macro. The macro takes approximately four-six seconds to run (depending on what else I'm doing). Then, before the macro is finished executing inside of Excel, the macro inside of Access starts running.

    I will post my code below. Please remember, I'm slightly new, so am just getting familiar with pointing and calling to other functions or subs and such.

    Feel free to change this code to make it more efficient, etc. However, if you change this code, please tell me exactly what you did and the reason behind doing it...don't say...make a function, then point to it. I'm not that brilliant yet.

    Ok...here goes...

    Kill "C:\my documents\inventory.xls"
    Call Shell("c:\program files\microsoft office\office\excel.exe", 1)
    SendKeys "%{F8}PERSONAL.XLS!FOAMEXUPLOAD{tab}{tab}{tab}{ent er}"
    DoCmd.RunMacro "mcrimportfoamex"

    The first two lines are pretty self-explanatory...the third line opens a macro inside of Excel called "Foamexupload" inside of the personal workbook. The fourth line returns to Access to run the macro, "mcrimportfoamex".

    Thanks for any and all help. I appreciate it.

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Check out the "Access and Excel" thread a little down this list. Part of your answer lies there.

  3. #3
    Join Date
    Apr 2002
    Location
    Kentucky
    Posts
    6

    Wait, pause, sleep, timer, AAAAAAAAAHHHHHH

    Practical Program,

    I appreciate your help. I am not yet capable of understanding what that little snippet of code does in the other thread (see below). When I place it into my sub, it does not give me the correct results. I just have not yet learned the language. There is hope for me yet...my company is sending me to school to learn programming this summer. Here's to higher education...

    Do you have a minute to spare for an explanation of this code that was referenced in your previous post? I appreciate all of your help.

    Dim o9 As Object
    Set o9 = CreateObject("WScript.Shell")
    o9.Run "c:\x1.xls", 1, True
    MsgBox "Done"

    Set o9 = Nothing

  4. #4
    Join Date
    Apr 2002
    Location
    Kentucky
    Posts
    6

    Wait, pause, sleep, timer, AAAAAAAAAHHHHH

    Please can anybody help me on this issue? Anyone?

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Sorry,

    Do this:

    Modify your Excel document to automatically run it's macro on launch and, upon completing the macro, quit the Excel program.

    In your Access program, use this code:

    Code:
    Dim oExcel As Object 
    
    Set oExcel = CreateObject("WScript.Shell") 
    
    oExcel.Run "c:\TheFullPathAndNameOfYourProgram.xls", 1, True 
    
    DoCmd.RunMacro "mcrimportfoamex"
    
    Set oExcel = Nothing
    What this will do is launch your Excel program and wait until it is finished running it's macro and has quit. Then, it will run your Excel macro.

  6. #6
    Join Date
    Apr 2002
    Location
    Kentucky
    Posts
    6

    Wait, pause, sleep, timer, AAAAAAAAAHHHHH

    I would and that is a great fix, save for the fact that the file that I'm calling from is a text file that is automatically importing from text upon running the macro, then it's saved as an Excel spreadsheet, so I'm back to square one, though I'm starting to see how that code works that you posted above.

    The original text file is coming from a tab delimited file from a barcoding system that we are implementing.

    So, I know that the automated macro thing can be done and I can run a macro that does that. The problem is, the macro starts an import of a text file...so now I don't know...hmmmm...frustrating this is, but I can truly see the wonderful merit of learning to program.

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I couldn't follow that first paragraph.

    You have a text file from a barcode system.

    Are you then manually importing that text file into Excel?

    Then are you running the macro to massage that data?

    Then, you want to import that massaged data into Access?

  8. #8
    Join Date
    Apr 2002
    Location
    Kentucky
    Posts
    6

    Wait, pause, sleep, timer, AAAAAAAAAHHHHH

    Sorry...

    I have a text file that is uploaded from a barcoding system to a PC. Then, a macro in Excel performs two text to column changes to that file and adds a couple of fields, readying it for import into Access.

    That's the macro that is run inside of Excel. It's currently stored in the Personal workbook because the flat file is replaced everytime the macro is run.

    So, to follow your steps above...yes, I am importing the text file into Excel, massaging the data inside of Excel, then importing the results of said massage into Access.

    Thank you again and again for your help. I wish you lived closer...I'd pay you bags of money to teach me what you know.

  9. #9
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    This WILL do what you want (I've added your filename to the code) as long as you can make that Excel macro and autoexec macro.

    Code:
    Dim oExcel As Object 
    
    On Error Resume Next   
    Kill "C:\my documents\inventory.xls"
    On Error Goto 0
    
    Set oExcel = CreateObject("WScript.Shell") 
    
    oExcel.Run "C:\my documents\PERSONAL.XLS", 1, True 
    
    DoCmd.RunMacro "mcrimportfoamex"
    
    Set oExcel = Nothing
    I know you already have a lot invested in this process, but just to let you know, Access can do all of this without having to involve Excel. In other words, the whole process could be made seamless.

  10. #10
    Join Date
    Apr 2002
    Location
    Kentucky
    Posts
    6

    Wait, pause, sleep, timer, AAAAAAAAAHHHHH

    AAAAAHHHHHHHHHHYYYYYYEEEEEEEEEEEE....give me the ceremonial suicide sword right now. I would like to do that.

    I was under the understanding that Access can only do one text to column function and not two when it imports a delimited file. This file is tab delimited, but the barcode field also needs to be split up seven different ways at the same time.

    My theory is to import the file as a tab delimited and then somehow break out the barcode field.

    If anyone has the time and patience to explain that process, the rewards will come back to you ten-fold.

    P.S. Remember the schooling I referenced in an earlier post. I broke down and signed up for an online tutorial on VB, so that I won't be going into class blind. The hope remains....the legacy persists.

    Thanks in advance for the help. My ancestors are cheering.

  11. #11
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Sorry Adam, but your assumption concerning Access' limitations is way off. There is very little that cannot be done in Access, most of that having to do with graphics.

    Anyway, why don't you eMail me (click the eMail icon on one of my posts).

    What you are asking for is moderately trival--I can probably get it to you in a half-hour.

  12. #12
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Hi!

    Why do you need Excell!? with the Access you can do the same that you do with Excell, even better.

    Import all the data from Excell to Access, and then workarround in Access, its easyer.

    I've done an inventory and Production control database, that calculates Economical Order Quantity, Lot sising, Time series exchange, Gantts Diagrams, etc. and lots of statistical calculations, and in the begining, i thought in use excell to, but it is easyer to do all in Access.

Posting Permissions

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