Cory

Have you tried using controls to read the text?

I wasn't sure so I wrote a simple test. If you fire the macro manually before and the scheduled run time passes and then the macro ends the macro will fire again. Correction: It depends. If your macro is scheduled to fire at 0900 and you fire the macro at 0859:30 and let it run for 60 seconds to 0930:30 it will fire again. If you let it run for 120 seconds (0931:30) it will not. As i see it if the macro wakes up and the scheduler checks the time and it's a match it runs. Also I believe this would depend on what the scheduler interval is set to.

Floyd is right, this is not an ME error message, it must be something else. My observation is that you increased the playback speed 1000 times. IMHO this is not a good idea. I suggest that instead of moving around and copying line be line you instead have the user highlight the entire range to process, copy it to a string variable, then write that variable to file, and run the ASCII Text file process. Then write the results to a string variable using tabs to separate columns and CRLF to signify the end of a row. Then when you are all done you can simply paste that entire string variable back into Excel. In my experience this seems like more work but it actually saves a ton of hassle and runs very quick. Another issue could be your formatting of the spreadsheet. If you have digit grouping enable you must understand that if you copy 1,234 you get 1 comma 2 3 4 and not one thousand two hundred and thirty four. You might be pasting a text value (1,234) into a field that expects a numeric value like 1234. If this is the case try stripping out the commas before you do your thing. Just remember, no matter what the data type in Excel ME will only receive the text string representation. This is important to remember when dealing with dates as well. In effect when ME brings it into a variable all of the extra clipboard data is lost.

This is great. I can't wait ot play with it. Thank you very much Paul!

I find that if it's a long process that the constant closing and opening (trick: do the close before the open) will slow things down and or steal focus from other things you're trying to do. I usually calculate a percentage done, in fact I have a subroutine to do it, and update the box every 1% or sometimes 5%. In the beginning I would tell the user X/Y records where X was the current and Y was the total but when processing 3600 iterations this will slow the macro down a lot. Also I have a trick I use once in a while where the close/open part of the repeat is conditional depending on whether the box is on top. This way it won't attempt to do the close/open if I'm working on something else. If I then want to see how it's coming I highlight the box again and the next update will give me the current progress.

I'm going to need a cup o' joe for this one. It seems very elegant and powerful but at this point I only have a vague notion what's happening here. I'll have to play with it before I can make much comment. But let me answer your question from 2 posts ago. But before I do please understand I have no idea what the capabilities are for the VBScript but that pretty much looks like a SQL statement to me. My problem is that not knowing what it can do I don't know what the best approach should be. Hold that thought. For your question about how ME would handle a tabular query result I refer you back to Floyd's posts. It seems from what they do in Excel you could end up with a string variables that's delimited with tabs and CrLfs like one might get doing a clip copy from Excel. I could easily chew thru that in ME. Now back to that thought. Let me turn this around for you by giving you a couple of examples of one of my applications and you tell me how you think it would be best to use VBScript. 1- Imagine a simple table in access with client IDs and client names. I might want to return the client name associated with the client ID provided. 2- Using the same table imagine that I have a folder full of client folders who has the convention of [Client name] [Client ID] fo rthe name and I need to periodically audit this. It would be nice if I could, in effect, do an ASCII FP with the table contents checking each folder against each record. Does that help any?

Thanks Floyd. I can't wait to get into this and play around.

Woah. I need to sit down. Wait, I am sitting down... Why is the room spinning? This is terrific! But like an archeologist discovering the remnants of a lost society it might take me a while to make sense of it. Unfortunately I have some chores to attend to first today but I can’t wait. OK, OK, I LIKE ASCII Repeat! No really, I do. For things like this it would be fine. I just avoid it when it’s going to cost the user twiddle time. I know this is premature and I should just study this myself but is there a way I could, in effect, get a query result? For instance in a simple table could one ask nicely to simply return the client name associated with a certain client ID? I’m guessing there are a lot more capabilities which mean my whole approach in the macro should be different. As it is now I use ASCII FP to plow thru the entire exported client file until I find a client ID match then grab the client name in the next string variable over. Hmmmm…. My mind boggles. I had better post this before I go overboard...

I was evaluating all solutions in parallel and found that the VBA export was so simple even I could do it so implemented it as the current solution. DoCmd.TransferText acExportDelim ended up doing exactly what I needed in one command. Now I have one button the user clicks that dumps several Tab files. This is a huge improvement over having a user manually export several queries with wizards but I do still want to pursue a direct link as the final solution. I'm sorry, I didn't explain myself well. I do like the ASCII file process and if this is how the VBScript would pass the data back that would be great. What I meant was... Well an example would be a clipboard copy of a chunk of Excel cells. Once in a string var I would rather chew thru it in memory than write it to the hard drive and run the ASCII File Process. Also when looking up data in such tables it's a lot faster to plow thru data in RAM than the HDD. Also if I am generating a results file I will build the file contents in RAM with tabs and CRLF and make one write to the disk instead of thousands. It's just that in some cases reading and writing from the HDD can really slow things down. If the VBScript could pass this query results back to ME somehow in RAM, like the CLIP.exe, I would rather go that route than writing it to disk. But if generating a CSV file is the only way then I'm all over it. Does that make more sense? So do you have examples I might be able to try? Or should I just wait for the Functions Library to be expanded to Access?

There are several ways and many choose to use mouse clicks to accomplish this but in my opinion this always causes problems later. I tend to do one of two things. I either TAB X number of times to move to the box or I use the find utility to find and highlight the text in front of it and then TAB once to move to the field.

Who was it that was speaking about doing Db queries every morning? I think it was Paul, maybe he will chime in. For the record I don’t like using the ASCII File Process command unless I have to. If I have a chunk of tabular data like one might copy form an Excel worksheet I would rather chew thru it tab by tab in a string var. I don’t know what you mean by a SQL style dataset. I imagine a large chunk of data delimited in some fashion like the aforementioned Excel copy. If this is the case this is EXACTLY what I am looking for. Having said that I think that if I had to request the data in a more specific way that might actually make things easier. In access if I write a certain query and get a null set it tells me this doesn’t exist. If my macro I the given example were to query the Db and ask for “XYZ Corporation” and the response was a big fat “No” then that would work even better. Or, as a better example, query the Db for the client ID number for XYZ things would get a lot simpler and faster. How do you pass the results of a VBScript back to ME? I know you are working on a funtions library for Excel but you might consider expanding it in a like fashion to Access.

OK Pandora, you asked for it. Assuming for the moment this might be viable what kind of data would it return? I mean does it return the contents of the object in one variable or is there some way to plow thru records as we might do with ASCII File Repeat? I’m not sure about the viability of this for security reasons. If it’s a distributed macro I would need a way to…. Well, I suppose I could keep the user name and password in the local user’s preferences in the registry. I just don’t like the idea of them all using the Admin account all the time. But then again it’s only referential so I guess there’s no harm. But being able to access data real time would be so cool I could live with it if need be. So do you think you could give me a simple sample to play with?

Yeah, ideally I think peaking into the Db when the macro in example goes to audit folder names or whatever. This means it's always looking at the most recent data. The problem I'm anticipating is that the MDB file is secured with a WIF. I got more questions along this line as to the practicalities but I'll stop here. Can VBS query an MDB table that has been secured? In Excel I access the data in the DB but I can't do it directly and have to set up an OBDC link with the credentials. It's fine for a user or two but I don't want to have to do this for the entire company. Seems one should be able to do it thru the API.

Method 2 is out. Linking tables is a one way street making the data, for all practical purposes read-only. Method 3 VBA Docmd.TransferText looks promissing though. Sure wish there was a way that didn't require a use to push a button though...

1. Ummmm Yesss… Sort of. Understand that there is no macro currently to do this, only macros that require those text files to be current. Furthermore I am not resigned that a Macro Express macro will be the solution. Let me make an example. I have table of clients and a folder structure that has a file name convention consisting of the client name and client ID. My macro uses the exported tab file to audit the client folder names. There’s a lot more but this is conceptually simple. In this example my file audit macro might be modified to use a VBScript to look directly into the database. Or I create a text file link in Access that gets updated automatically. Or I write a VBA macro in Access. Or I write a new ME macro to export whenever a user makes a change. 2. No. Referential only. Clear as mud?

If it has spaces in the path you need to put it in quotes... Linguistics question: Is the upside down quotes some sort of international thing? Like the upside down question mark before a question in Spanish? I don't think I've ever seen that before. But then again Germans use a weird looking Bs for two Ss.

I have several macros that rely on data stored in an Access database. At the moment I export special queries that pump the data into a tab separated values file for ME which is obviously silly because it requires someone to manually jump thru a series of hoops every time something is changed in the Db. This morning I plan on writing a solution but I hope you can give me suggestions. Oh, and I fully accept that the suggestions might not involve ME directly. I have a few thoughts on how to do this so I’ll toss them out here for comment: 1. Read directly from the Db with vb script: Obviously the best method is not to export at all but link to the data. I know VBScript can tap Access databases but this one is secured with a WIF (Workgroup Information File) which requires every user to log in with a password in order to access the Db. Is there a way to use VBScript in this case? 2. Link queries or tables to a text table: I know one can link to text files and they appear as tables in Access but I’ve never experimented with them. Probably will this morning. 3. Export manually using VBA: Might be a simple way to have a user button in the Db GUI where a user can push a button and all would be exported. 4. Export with ME: As a last resort I can make the queries appear, copy them with the clipboard and run with them using ME. Probably the easiest method and I know I can do it quick but… Ick! I know a lot of you do databasey things all the time with ME so I’m hoping that this is not an uncommon problem and I can learn form other’s experience. Thanks!

You're missing a space between the EXE path and the parameter. As I see it what is and should be is: If you put this in your run line I am sure it will work. Trick is to get it to work their first then move on to other problems. As for running it from a link in a Word document I'm not so sure.... I will tell you one can not pass program parameters like that in a hyperlink. I'm thinking you need to create a Word macro that runs form a button click in line with the text or something like that. But maybe you could create a simple batch file and write a hypertext link to it. I'll have to think about that. It's an intriguing idea to launch macros form a hypertext link. You might also look into using an OLE embedded object.

I have a macro named "Scratch Pad" and this is the command I would use: "C:\Program Files\Macro Express3\meproc.exe" /AScratch Pad Since the path to ME has spaces you need to wrap it in quotes. %20? Link in Word? This sounds like you're trying to launch it from a hypertext link in a web page. Usually when I see %20 is when people use spaces in names or paths on the Internet. The space is an illegal character so systems substitute %20. For instance if I post a file with a space in the name your browser will display a %20 in the path instead. Start by typing your command in to the run line above the start menu. Or use the Win+R keyboard shortcut if you have Vista which removed it.

Pardon me, you English was so good I didn't suspect you were not a native speaker. Where are you from? One of the things I love about this forum is the wonderful diversity of folks from around the world that hang out here. I’m sorry if you feel misunderstood but before we go any further did you try my sample macro? And if you did what was the result. Don’t forget you need to use it while Window Activation Caching is disabled. I asked before but you made no indications. I feel that if you don’t that I don’t want to spend the time to review what I created then I’m wasting my time. It’s my contention that you’re not looking for help with a solution or workaround but rather making complaint/features lists. I think you’re missing some fundamental concepts here as is evident by your list. I won’t go into all of them as I need to get back to work but one of your items was to Wait Until Macro Finished. This makes no sense. It is a fundamental limitation of Macro Express that it can not run more than one macro at a time. This is why I think you’re seeing the Activation Cache. If you use my sample macro I think you will see what I mean. Turn on the activation cache and it goes haywire. Turn it off and everything works flawlessly. Good luck with your features request list and don’t forget to submit them to Insight’s Feature Request Form. Oh, and you should probably break them up into separate requests as each would require individual consideration and implementation. Sorry I could not be of more help, I hope someone else who is more experienced with floating menus can join in and help you.

Alexandra I'm going back to your original thread on the subject. Please look there.

You saw Floyd's post didn't you? Similar to what I did but much nicer.

That's nearly the same that I posted for Alexandra but I like your use of enable/disable where I used minimize. Much more elegant.

So did you try my macro? I would be interested to know because it works for me and does what I think you are describing. If it doesn't work for you and it does for me we're getting somewhere. Even with 4 monitors you need to mouse over to get them. I run 3 monitors and I'll grad race you to see if the FM's are faster than my middle click popups. So aren't you concerned that if you have a macro keyboard and Macro Express you might be having some conflicts? I'm not sure if that's possible but I thought I would mention it. I don't get into gamer hardware much.

I use generic purposed macros as subroutines and use the Macro Run command to call them. This way I can use them in several different macros. You can read about it here.