Seamless ME, Excel & Third Party Communication?

[brad6499]

Okay so I realize it isn't everyone's job to help me with mine, but I have a major project and very little time to finish it so I thought I'd "summon the powers that be" (Kevin /Floyd/Randall ...) to get some assistance. Here's the scene:

 

I have a program (we'll call it "CONSOLE") that continually updates data into a tabluar spreadsheet that interfaces with an Excel file (we'll call it "MASTER SHEET") through a DDE link. Every two minutes, new data is written to the tabluar file in Console, but for some reason (they're "looking into it") the DDE link DOES NOT update automatically into the Master Sheet as you'd expect with a DDE link. The DDE link in the Master Sheet comes across in an array formula that, in order to update, requires a few manual keystrokes to refresh and pull the new data. The DDE array formula comes across as

{=Console|HISTORY!'@S_P[iNTERVAL:MINUTE,COMPRESSION:2,SORT:ASC,NDATE:10/25/2007,NTIME:14:14,FDATE:10/25/2007,FTIME:7:30,VERSION:1]?DATE,TIME,OPEN,HIGH,LOW,CLOSE'}

Obviously, I can write a vb macro in Excel (or ME) that, through sending key strokes, auto-refreshes the data from Console, but that's definitely not where I'm hung up.

 

Once I have the updated data into the Master Sheet Excel file, a conditional formula is triggered that runs the appropriate .mxe program based on the values that came across on the DDE refresh. (Let me know if I've lost anyone yet.)

 

Now, once the .mxe file is running, I need to gather additional variables and data from Console, write it to a different Excel file (we'll call this one "Sub Sheet"). This Sub Sheet file is also linked (cell references) to the Master Sheet. As this new data is written into the Sub Sheet, I need the Master Sheet to be refreshed so that if certain conditions are met, the running .mxe file is either stopped, or triggered to do something else entirely.

 

After this, the whole process must repeat itself every two minutes as the new data is posted from Console to the Master Sheet.

 

The obvious answer (to me) is to continually flip back and forth between Console and the various Excel spreadsheets and "copy" and "paste" into the appropriate places, then copy a specified cell that would, if conditions were met, take the .mxe logic down another “if then” statement. While I would LOVE for this to work, there are too many variables coming too quick and toggling screens to do this has repeatedly built up errors that eventually cause: ME/Excel program failure or a system crash. Because the stability is so important, I need a solution that doesn't require me to toggle between screens more than once or twice every two minute cycle.

 

So, here are my conundrums:

- Can Macro Express write directly to the Sub Sheet Excel file without opening it, and if so, HOW? I saw an old posting (back in 2005) that said it was possible, but that you need ---new "ExcelComWIZ"; And only the "exe" in MEX directory from the zip; {_ExcelComWIZ}.mxe, MEbasicWizard.zip"--- Are these files still available? I cannot download them through the links I saw. If the process has been integrated into the new 3.XX versions, please let me know how to use it.

- If a .mxe macro is running, can it be stopped/changed without causing problems or error messages that need to be cleared before ME can launch a different macro? (Or better yet, can ME run more than one macro at a time?)

- What is the best method for capturing data from of a program that does not respond to key strokes and when the control function (ie. C1) does not return the correct information? If the answer is mouse clicks and movement, can I pause/stop a macro (that is controlling mouse movement to gather the data) when I move the mouse manually?

- Is it possible to display a dialog box that displays a message while the macro continues to run in the background behind the dialog box? Same question but now taking into consideration that the program does not respond to keystrokes (only mouse movement and clicks.) If so, is it possible to have this dialog box change its message as certain conditional variables are met through the data being captured in the background? (i.e. captured data shows that N1 > N2, Message displays "Over"; captured data shows that N1 < N2, Message displays "Under"; captured data shows that N1 = N2, Message displays "Equal Distribution" etc.)

 

That's all I can think of right now. I realize I'm breaking about a hundred posting rules with this one, but I'm desperate for help! Thank you in advance for anyone that takes the time to read about my project, or ideally, respond to it!

[joe]

I realize I'm breaking about a hundred posting rules with this one, but I'm desperate for help!.

Well, I'm not sure which posting rules those would be.

 

 

If a .mxe macro is running, can it be stopped/changed without causing problems or error messages that need to be cleared before ME can launch a different macro? (Or better yet, can ME run more than one macro at a time?)

You would need to build something into the macro to stop it automatically. Something like:

 

"Hey, if this file, or Registry Value, or Environment Variable exists now, then I quit. Otherwise, I will loop again."

 

And no, with Macro Express 3 only a single macro can be run at any given time. Floyd and I have, in the past, gotten two to run, but that was through VMWare sessions. It may be a possibility for you.

 

 

While I would LOVE for this to work, there are too many variables coming too quick and toggling screens to do this has repeatedly built up errors that eventually cause: ME/Excel program failure or a system crash.

I'll assume that you've already attempted some stability with this approach.

 

 

Can Macro Express write directly to the Sub Sheet Excel file without opening it, and if so, HOW?

Not directly. It can, however control a VBScript that does. Two things I've got to tell you right off the bat: 1) We (er, Professional Grade Macros) are working on an Excel/Macro Express interface that will do exactly what you want, but it won't be ready for a while yet. 2) Most of the work I do for one particular client is all Excel/Macro Express/VBScript. it's beautiful to see in action. What you need to learn is Excel's DOM (document object model). You may already know this, I am not sure.

 

 

I saw an old posting (back in 2005) that said it was possible, but that you need ---new "ExcelComWIZ"; And only the "exe" in MEX directory from the zip; {_ExcelComWIZ}.mxe, MEbasicWizard.zip"--- Are these files still available?

No. Randall disappeared. Randall and his website disappeared. Randall, his website, and the files you want disappeared. I will say no more about the files except that Randall DID try and some of the work WAS great.

 

 

What is the best method for capturing data from of a program that does not respond to key strokes and when the control function (ie. C1) does not return the correct information?

Okay, that question is a setup for: "It depends"

 

 

can I pause/stop a macro (that is controlling mouse movement to gather the data) when I move the mouse manually?

Absolutely. Use the Macro Pause command.

 

 

Is it possible to display a dialog box that displays a message while the macro continues to run in the background behind the dialog box? Same question but now taking into consideration that the program does not respond to keystrokes (only mouse movement and clicks.) If so, is it possible to have this dialog box change its message as certain conditional variables are met through the data being captured in the background? (i.e. captured data shows that N1 > N2, Message displays "Over"; captured data shows that N1 < N2, Message displays "Under"; captured data shows that N1 = N2, Message displays "Equal Distribution" etc.)

Sort of. In Macro Express 3 there is no "Update message box function". You can, however have more than a single message box that simulates what you are asking. Simply display a new Text Box on top of the old one and then use the Text Box Close command to close the old one.

[brad6499]

Sorry for not including you on my list! You definitely should have been on there.

 

I am trying some of your solutions and will post if I find anything else that would be helpful for anyone else who runs into a similar situation.

 

Can you email me when this new Excel connectivity module becomes available for beta testing? I'd love to give it a go.

 

Thanks again!

 

Brad

[zlatan24]

In this situation advise try-Excel worksheet recovery,as far as i know it is free,utility can repair corrupted Microsoft Excel worksheets,restore data after problems with corrupted worksheet problem as a result of virus attack, disk and network failures or any other problem,an scan a damaged Microsoft Excel worksheet and repair it,work with *.xls, *.xlam, *.xltx, *.xlt and *.xlsm formats,recover modified Excel worksheets and shows recovered data in the same way, like it will be displayed in Microsoft Excel worksheet.