Jump to content
Macro Express Forums
foray

Macro fails to retrieve value from cell in Excel worksheet

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?


Share this post


Link to post
Share on other sites

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 Mouse
Text Type (Simulate Keystrokes): <SHIFTU><ALTU>
Text Type (Simulate Keystrokes): <HOME>
Text Type (Simulate Keystrokes): <SHIFT><SPACE>
Text Type (Simulate Keystrokes): <CONTROL>x
Text 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/>

Share this post


Link to post
Share on other sites

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 by terrypin
My mistake

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...