foray Posted July 15, 2009 Report Share Posted July 15, 2009 Hi, I have a macro which activates an Excel worksheet, moves to cell D3, reads the value of D3 and copies into Macro variable N3. It then moves to cell D18, reads the value of cell D18 and copies into variable N10. The last few days of testing, the macro has performed these executions flawlessly but this morning it started misbehaving: N3 was always zero, even though cell D3 = 500. The strange thing is that while the macro was not able to read cell D3, it was able to read cell D18 correctly. This is strange because the code block used to read each cell is identical (posted below). I re-ran the macro perhaps a dozen times but each time it failed to read cell D3 while at the same time correctly reading cell D18. Then it started working again. Sometimes I have the feeling that Macro Express behaves erratically and unpredictably, but I know there must be a logical explanation for it. I'd deeply appreciate having a deeper understanding of what is happening when Macro Express fails to read the Excel cell, so that I code the execution more reliably - thanks. // Switch to Excel worksheet Activate Window: "Microsoft Excel - Batch Process Symbol List.xls" Wait For Window Title: "Microsoft Excel - Batch Process Symbol List.xls" // Initialize Excel & Matrix cells - run Excel VBA code. Text Type: <ALT><F8> Delay 1 Seconds Text Type: InitializeCells Delay 1 Seconds // ///////////////////////////////////////////////////////////////////////////////////////// // Initialize variables that point macro to a specific Excel sheet & cell // ///////////////////////////////////////////////////////////////////////////////////////// // Set Text variable T53 = text string pointing to sheet 1, cell D3 Variable Set String %T53% "'Sheet1'!D3" // Set Text variable T55 = text string pointing to sheet 1, cell D18 Variable Set String %T55% "'Sheet1'!D18" // ///////////////////////////////////////////////////////////////////////////////////////// // Initialize variables tracking current and previous symbol numbers processed // ///////////////////////////////////////////////////////////////////////////////////////// // N2 is the ID value of the CURRENT symbol - initialize Variable Set Integer %N2% to 0 // N12 is the ID value of the PREVIOUS symbol - initialize Variable Set Integer %N12% to 0 // ///////////////////////////////////////////////////////////////////////////////////////// // INITIALIZE FINAL SYMBOL NUMBER (RETRIEVE FROM EXCEL SHEET) = N3 // ///////////////////////////////////////////////////////////////////////////////////////// // Go to Excel cell D3 Text Type: <CTRL>g Text Type: %T53% Text Type: <ENTER> // Copy Cell D3 contents to Clipboard Clipboard Empty Clipboard Copy // Paste into numeric variable N3 Variable Set Integer %N3% from Clipboard // ///////////////////////////////////////////////////////////////////////////////////////// // RETRIEVE USER DEFINED POLL DELAY (RETRIEVE FROM EXCEL SHEET) = N10 // ///////////////////////////////////////////////////////////////////////////////////////// // Go to Excel cell D18 Text Type: <CTRL>g Text Type: %T55% Text Type: <ENTER> // Copy Cell D18 contents to Clipboard Clipboard Empty Clipboard Copy // Paste into numeric variable N10 Variable Set Integer %N10% from Clipboard Quote Link to comment Share on other sites More sharing options...
foray Posted July 15, 2009 Author Report Share Posted July 15, 2009 I'm currently working on the theory that it might be the Clipboard copy command that fails sometimes, apparently a legacy of Windows. Currently trying monpasdg's Clipboard Copy (Advanced) macro subroutine (http://pgmacros.invisionzone.com/index.php?showtopic=3863). Will report back here once thoroughly tested a few days. Quote Link to comment Share on other sites More sharing options...
rberq Posted July 15, 2009 Report Share Posted July 15, 2009 Try going to Options | Preferences | Delays, and bump the clipboard delay up to some high value like 3000 milliseconds (3 seconds). If your macro works consistently with that value, then you have pretty much proved it is the clipboard copy that is the problem. I understand your feeling that "Macro Express behaves erratically and unpredictably". But ME is only passing keystrokes to Windows, for the most part, and I have found it is usually Windows behaving erratically and unpredictably. The "unpredictable" part is usually unpredictable timing. Think about your manual interaction with Windows applications. Sometimes the response is instantaneous, but when the Bill Gates Random Performance Function kicks in, the same response can take several seconds rather than the blink of an eye. Quote Link to comment Share on other sites More sharing options...
foray Posted July 16, 2009 Author Report Share Posted July 16, 2009 Point taken, thank you. Don't shoot the messenger (ME) springs to mind here. Quote Link to comment Share on other sites More sharing options...
foray Posted July 21, 2009 Author Report Share Posted July 21, 2009 After 50-60 hours of testing, I can report that there have been no further issues with the clipboard since using the Clipboard Copy (Advanced) function written by monpasdg - thank you to this member for making this code available. Quote Link to comment Share on other sites More sharing options...
randallf Posted July 21, 2009 Report Share Posted July 21, 2009 This has worked great for me for clipboard speed. As far as I know this is the fastest that it could possibly be because as soon as it has data it will stop: Repeat Until %T1% <> "" Clipboard Copy Variable Set String %T1% from Clipboard Repeat End Quote Link to comment Share on other sites More sharing options...
terrypin Posted February 25, 2017 Report Share Posted February 25, 2017 Some 7½ years have passed but maybe you're still around, Foray? I'm currently doing similar stuff in Excel 365 (using MX Pro) and I'm curious to know the details of your VBA macro InitializeCells, as used in this part of your macro: // Initialize Excel & Matrix cells - run Excel VBA code. Text Type: <ALT><F8> Delay 1 Seconds Text Type: InitializeCells Delay 1 Seconds Elsewhere I see you used the Go To dialog to access cells, so I'm puzzled why the above VBA is needed? (And why such a long delay after it?). -------------------- More generally, I'd appreciate hearing from any MX3 or MX Pro user who has written MX macros for any version of Excel. For example, what method do you use for copy/pasting between sheets? Mine is glacially slow, so I'm considering trying to re-learn the little I knew about VBA. And how do you run a VBA from your MX macro? Same method as Foray above? -- Terry, East Grinstead, UK Quote Link to comment Share on other sites More sharing options...
acantor Posted February 25, 2017 Report Share Posted February 25, 2017 Hi Terry, I have had good luck with the Alt+F8 method for running VBA scripts from Macro Express. You are correct, there is no need for such long delays. 10 ms should be enough, and no delay may work fine. Quote Link to comment Share on other sites More sharing options...
terrypin Posted February 26, 2017 Report Share Posted February 26, 2017 Morning Alan, Thanks, that's my method too so far in my brief experiments, like this: Text Type (Simulate Keystrokes): <ALT><F8> Delay: 0.1 seconds Text Type (Use Clipboard and Paste Text): PERSONAL.xlsb!PutCurrentRowInClipboard How about your approach for copy/pasting between cells or sheets? Quote Link to comment Share on other sites More sharing options...
acantor Posted February 27, 2017 Report Share Posted February 27, 2017 Copying and pasting between cells and worksheets are not tasks that I usually automate. But if I did, I would make use of built in hotkeys in Excel, e.g., F2 (to edit the current cell); Ctrl + PageUp/PageDown (to flip to the next/previous worksheet), Ctrl + spacebar (to select the entire column), and so on. My favourite Macro Express script for Excel moves an entire row down or up without selecting it, analogous to the two built-in Word commands that move an entire paragraph, without the need to select it first. My scripts assume the same shortcuts for both programs: Alt + Shift + Up/Down. Move Row Up: (Alt + Shift + Up) {Set to activate as soon as the keys are pressed} Lock Keyboard and MouseText Type (Simulate Keystrokes): <SHIFTU><ALTU>Text Type (Simulate Keystrokes): <HOME>Text Type (Simulate Keystrokes): <SHIFT><SPACE>Text Type (Simulate Keystrokes): <CONTROL>xText Type (Simulate Keystrokes): <ARROW UP>Text Type (Simulate Keystrokes): <SHIFT><CONTROL>=Text Type (Simulate Keystrokes): <SHIFTD><ALTD>Unlock Keyboard and Mouse Move Row Down: (Alt + Shift + Down) {Set to activate as soon as the keys are pressed} <LOCK KEYBOARD AND MOUSE/><TEXT TYPE Action="0" Text="<SHIFTU><ALTU>"/><TEXT TYPE Action="0" Text="<HOME>"/><TEXT TYPE Action="0" Text="<SHIFT><SPACE>"/><TEXT TYPE Action="0" Text="<CONTROL>x"/><TEXT TYPE Action="0" Text="<ARROW DOWN><ARROW DOWN>"/><TEXT TYPE Action="0" Text="<SHIFT><CONTROL>="/><TEXT TYPE Action="0" Text="<SHIFTD><ALTD>"/><UNLOCK KEYBOARD AND MOUSE/> Quote Link to comment Share on other sites More sharing options...
terrypin Posted February 27, 2017 Report Share Posted February 27, 2017 (edited) Many thanks, Acantor, very helpful. And sorry for calling you Alan! Do you select your cells (or a row) manually before triggering the macro? If not, what code do you use for making the selection? This example show my current method: Window Activate: %tSheetFilename% // Change focus to the worksheet Text Type (Simulate Keystrokes): <ALT>eg // Open 'Go To' dialog Delay: 0.1 seconds Text Type (Simulate Keystrokes): b5 // To select cell B5 Delay: 0.1 seconds Text Type (Simulate Keystrokes): <ENTER> Delay: 0.1 seconds Edited March 6, 2017 by terrypin My mistake Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.