Flabberghasted by shellexecuted (VBA)

Question

hi everyone,

I'm trying to extract some values out of a file and into my excel 2007 workbook, using VBA and the dreaded Sendkeys function (copy-paste) after a shellexecuteEx call. Now, my macro is working fine when the file in question has already been loaded into the system memory. But if I'm running it with the file never having been loaded before (such as immediately after system boot) then the delay to load the file is too long and the sendkeys are sent too fast. So I placed a little Sleep call between the shellexecuteEx and the Sendkeys. I set the Sleep parameter at 100, and tried running the macro. It worked, but the file had already been loaded before, so I rebooted the system, and ran the macro again. Turned out 100ms was too short. So I changed it to 1000 ms, and rebooted the system. Now thats where I'm completely bedazzled. At 1000ms, the delay is long enough for the sendkeys to work as intended, but the pasting in the workbook doesn't work anymore. I tried setting it back to 100ms and the pasting started working again. I set it back to 1000ms and it wouldnt work.

So here's my question, why would the following code work with a 100ms Sleep, but not with a 1000ms Sleep ? ( I tried gradually bringing down the 1000ms and the pasting started working at 112ms, but not all the time... about 6 times on 7)

Here's the code :

     Sub extractData()

       Dim retval As Long

       On Error Resume Next

       retval = ShellExecuteEx(0, "open", "expV4-1-1.edat2", "", _ 
                "C:\Documents and Settings\etudiant\emile2010" _ 
                & "\E-Prime\My Experiments\TouchScreen\Andreane", SW_SHOWMINIMIZED)

        Sleep 100
        AppActivate ("expV4-1-1.edat2 - E-DataAid")
        copyColumns
        pasteColumns
    End Sub

    Sub copyColumns()
        Dim ctrlL As String,  ctrlC As String, ctlShiftRight As String

        ctrlL = "^l"
        ctlShiftRight = "^+{RIGHT}"
        ctrlC = "^c"

        SendKeys ctrlL, True
        SendKeys ctrlL, True
        SendKeys ctlShiftRight, True
        SendKeys ctrlC, True
    End Sub

    Sub pasteColumns()
        Worksheets("Data").Activate
        Worksheets("Data").Cells(1, 1).Select
        Worksheets("Data").Paste
    End Sub

Answer 1

What file format is your file in? Would it be easier to import the data via a query instead and not go down this “screen scraping” route?

Answer 2

I would want to close this question, but I dont see how. Anyways, I've given up on trying to understand this problem. thanks for the help.