paul

Yes, I also didn't find anything useful with Google. I have been using Unlocker (it's freeware) for some time, and it apparently includes a command-line facility. You might be able to persuade it to do what you want.

Unlocker

Well, you need only one macro. Each time the macro runs (whenever the Notepad window opens), it retrieves the previous value of the counter, increments it and stores it external to Macro Express (either in a file or in the registry). When the counter is divisble by 5, run the rest of the macro, otherwise return immediately.

Actually, you can change the size of almost all dialog boxes throughout windows; but, at least in the case of the If Message box, it makes no difference because the extra lines are not displayed even though there's plenty of room after resizing!

Try downloading ResizeEnable from Digitalis

Consider the Activate/Launch Program dialogue.

Your Program/Path name should be exactly this - note there are no double-quotes!:

C:\Program Files\Microsoft Access Runtime\OFFICE11\StartAccess_2003.exe

And your Progam Parameters should be this - note you must provide the double-quotes here, because you have embedded spaces:

-wait=30000 /excl /runtime /profile "ImagineTime"

/wrkgrp "C:\Program Files\BothWorldsSoftware\ImagineTime\system.mdw" "C:\Program Files\BothWorldsSoftware\ImagineTime\TB2000.mde"

Try the Help file:

Macro Activation / Macro Activation / Windows API calls / Example VBA

This will give you the required VB code. If you want to recode it for a scripting language, I believe you'll need to use something like AutoIt3 because, AFAIK, VB Script doesn't support API calls.

2 methods spring to mind.

1) Use Excel; load the text file, delete the relevant columns, save the file, then load it into ME

2) Process the file in ME by loading it with the Text File command, count your way through each line to locate the "columns" you don't want (looking for the tab character as your delimiter between columns), erase the text between whichever tab values you no longer want, write the file back out, then reprocess it with the Ascii command.

What about this?

Variable Set String %T1% from Clipboard
If Variable %T1% >= "A" --Ignore case is ticked
 AND
If Variable %T1% <= "Z" --Ignore case is ticked
 Do something
Else
 Don't do something
End If

Take a look at the { String Example - Proper } macro and read the Help file (PGM Functions Library.pdf)!

When I execute the macro in notepad, I do not get T2 with the proper casing. Actually, I get nothing ...

t1--> my name is ali

t2-->

what am i missing?

Here is my version of your macro, which worked perfectly - its display showed:

Old string is my name is ali

New string is My Name Is Ali

<TVAR2:01:01:my name is ali><REGWSTR:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ParameterString1><MACRUN2:{ String - Proper }><REGRSTR:2:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnString1><TBOX4:T:1:CenterCenter000278000200:000:Old string is %T1%
New string is %T2%>

Your 1st statement initializes N1 to 0 (you don't need to do this since numeric variables are always initialized to 0).

Your 4th statement uses a Repeat with Variable command using N1.

Here's the relevant Help:

"Start Repeat With Variable

This option allows you to repeat a portion of your macro. The number of times

the macro will repeat is based on the value placed in the variable you have selected."

So you've asked the macro to repeat something 0 times, which from your description, is exactly what it's doing! I bet it runs fast <g>!

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.

As far as VB Script is concerned, it knows nothing, and cares even less, about SQL. I've simply created an instance of an ADO connection and passed in various parameters and instructions, one of which is some SQL code, and another of which is to go and execute that SQL code. Since this SQL code returns a recordset, I further instructed ADO to return that recordset, which I then read line by line. Each line is written to the registry (columns are separated by, in this case, "/", but it could just as well have been a tab character). ME then reads the value in the registry and processes it accordingly. Since a recordset usually contains several rows, I've incorporated a simple protocol to allow ME to communicate with the VB Script (ME: "I need some [more] data", or "I don't want any more data"; VBS: "Here's some more data", and "I'm done"), where each party to the conversations waits politely for the other to finish a sentence before speaking again).

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.

My question was not how ME would handle a result set - it was more to do with how you would handle it.

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.

Both of these requirements could readily be met with the above code, unaltered in any way (apart from the specification of the query itself).

Well, it was easier than I'd thought and it responds quickly.

ME Macro
-----------

<REM2:Disable this line if you want to get all the data from the query><REM2:Otherwise data is returned until the first 2 characters = 13><IVAR2:10:01:1><REM2:CrLf to separate returned rows in final display><ASCIIC:10:1:13><ASCIIC:1:1:10><TMVAR2:08:10:01:000:000:><REM2:Query><TVAR2:02:01:SELECT StatusId, StatusDesc FROM Status WHERE DatabaseId = 1 ORDER BY StatusId><REGWSTR:2:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ParameterString1><REM2:More Data please><IVAR2:01:01:-1><REGWINT:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnInteger1><REM2:The VB Script><LAUNCHDEL2:0:01D:\TestAccess.vbs1><REM2:The Loop><REP3:08:000002:000002:0001:1:01:N1><REM2:Wait for the request><REP3:08:000004:000002:0001:0:01:0><REGRINT:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnInteger1><ENDREP><IFVAR2:2:01:1:2><REM2:We're done><EXITREP><ENDIF><REM2:Get the data><REGRSTR:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnString1><REM2:Save it><TMVAR2:08:11:01:000:000:><REM2:Append CrLf><TMVAR2:08:11:10:000:000:><REM2:If N10 = 1 then we'll stop early><TMVAR2:10:02:01:001:002:><IFVAR2:2:10:1:1><AND><IFVAR2:1:02:1:13><REM2:No More Data please><IVAR2:01:01:-2><REGWINT:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnInteger1><EXITREP><ELSE><REM2:More Data please><IVAR2:01:01:-1><REGWINT:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnInteger1><ENDIF><ENDREP><REM2:Display all the output><TBOX4:T:1:000379000105000702000769:000:Returned data%T11%>

VB Script
-----------

Dim MyConn, MyQuery, MyRs, WshShell
Dim intI, Output, Response 

'Creates the ADO connection
Set WshShell = WScript.CreateObject("WScript.Shell")

MyQuery = WshShell.RegRead("HKCU\Software\Professional Grade Macros\Parameters\ParameterString1")
Set MyConn = CreateObject("ADODB.Connection")
With MyConn
 .Provider = "Microsoft.Jet.OLEDB.4.0;"
'  .Properties("Jet OLEDB:System database") = _
'	"C:\Documents and Settings\pault\Application Data\Microsoft\Access\SystemTest.mdw"
'  .Properties("Password") = "xxxxx"
'  .Properties("User Id") = "Test"
 .Open "Data Source=" & "G:\Ceru\CeruStudies.mdb;"
End With  

Set MyRs = MyConn.Execute(MyQuery)

With MyRs
 Do Until .EOF
Do Until Response < 0
  Response = WshShell.RegRead("HKCU\Software\Professional Grade Macros\Parameters\ReturnInteger1")
Loop
If Response = -2 Then Exit Do
Response = 0

Output = ""
For intI = 0 To .Fields.Count - 1
  Output = Output & .Fields(intI) & "/"
Next
Output = Left(Output, Len(Output) - 1)

WshShell.RegWrite "HKCU\Software\Professional Grade Macros\Parameters\ReturnString1", Output, "REG_SZ"
WshShell.RegWrite "HKCU\Software\Professional Grade Macros\Parameters\ReturnInteger1", 1, "REG_DWORD"
.MoveNext
 Loop
 .Close
End With

WshShell.RegWrite "HKCU\Software\Professional Grade Macros\Parameters\ReturnInteger1", 2, "REG_DWORD"

'It's vital to clean up properly after using ADO, otherwise you may experience memory leaks
Set MyRs = Nothing
MyConn.Close
Set MyConn = Nothing

Notes:

- I've disabled the Access database security stuff, simply because I don't have a secured database on my home machine!

- I'm using the following registry values:

* HKCU\Software\Professional Grade Macros\Parameters\ReturnInteger1 (Numeric1 in previous message)

* HKCU\Software\Professional Grade Macros\Parameters\ParameterString1 (String2 in previous message)

* HKCU\Software\Professional Grade Macros\Parameters\ReturnString1 (String1 in previous message)

* If you change these, change the corresponding references in both the macro and script

- The script name is D:\TestAccess.vbs (if you relocate it or change its name, change the corresponding reference in the macro)

- The Access database is G:\Ceru\CeruStudies.mdb (change the drive, path and database name in the script)

- There's no error-checking in the script

- String2 must be valid SQL defining your query; it wouldn't be difficult to allow a query name as an alternative

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?

The term "query result" of course means a singleton result like "How many people qualify?", OR a single row, like "What is the name and address of my best customer?", OR many rows, like "What are the names and addresses of my top 10 customers?"

How would you deal in ME with the output from the last 2 types of queries?

I'm going to try out the performance of an alternative solution to returning results to ME.

ME is going to communicate with the VB script via 3 registry values, Numeric1, String1 and String2.

Numeric1 value will represent a status flag:

1 = Returned data from VB Script

2 = VB Script has finished returning data

-1 = Data requested by ME

-2 = No more data requested by ME

String1 will represent a row of data from the query run by VB Script (each column being delimited by /)

String2 will represent EITHER the name of a query, or the SQL code needed for the query

ME
 set string2 to query or query name
 write Numeric1 = -1
 launch vbscript  
 repeat until n1 = 2
repeat until n1 <> n1
  read Numeric1
  if Numeric1 > 0 repeat exit
repeat end
if Numeric1 = 2 repeat exit
read string1
process string 1
write Numeric1 = -1 (or -2 if we don't want any more data)
 repeat end

VBScript
 initialize
 run query
 do until RS.EOF
write String1 = RS row (delimit columns with /)
write Numeric = 1
do until Numeric1 < 0
loop
if numeric1 = -2 exit do
 loop
 cleanup

Without having tried it, I don't see why this wouldn't work well.

Who was it that was speaking about doing Db queries every morning? I think it was Paul, maybe he will chime in.

Ding, ding!

Your question has 2 parts:

1) How to connect to an Access database secured using Workgroup Security

2) How to communicate the results back to ME

Here's some VB Script code to achieve this (using MS Access 2003); I'm indebted (not for the first time!) to guru Garry Robinson for the connection string values (http://www.vb123.com/toolshed/02_docs/accessadosecure.htm):

Dim MyConn, MyRs, WshShell, RecCount

'Creates the ADO connection
Set WshShell = WScript.CreateObject("WScript.Shell")

Set MyConn = CreateObject("ADODB.Connection")
With MyConn
 .Provider = "Microsoft.Jet.OLEDB.4.0;"
 .Properties("Jet OLEDB:System database") = _
"C:\Documents and Settings\pault\Application Data\Microsoft\Access\SystemTest.mdw"  'Fully qualified workgroup security file name
 .Properties("Password") = "xxxxx"   'password for authorized user
 .Properties("User Id") = "Test" 'authorized user
 .Open "Data Source=" & "G:\Ceru\CeruMensHealth.mdb;"  'Fully qualified database name
End With  

Set MyRs = MyConn.Execute("SELECT COUNT(*) FROM Person")  'A valid query that returns a result (more on this below)
RecCount = MyRs(0)

'It's vital to clean up properly after using ADO, otherwise you may experience memory leaks
MyRs.Close
Set MyRs = Nothing
MyConn.Close
Set MyConn = Nothing

WshShell.Run("""E:\Program Files\Macro Express3\MeProc.exe"" /AAccessReturn /VN1:" & RecCount) 'return the singleton result to ME

This example shows one way of returning a result (singleton) to ME. Another way would be for your macro to launch the script and use a Repeat loop to wait for the result (e.g. initialize a registry value to -1, change the script above to write the result into this registry value, loop in ME until the registry value is other than -1).

Returning a recordset to ME is harder, since ME can't deal with arrays. I'd probably make the .vbs script loop through the recordset and write it out to a text file (or use DoCmd.TransferText), then process that text file in ME (even though you say you don't like this approach). And the registry value returned from the script could indicate (e.g. value=-2) that some other registry value contains the name of the file it's just created for you.

You're limited only by your imagination as to what you can do.

I don't really understand this.

- You've presented a problem you're experiencing.

- I've suggested a solution.

But now you say you can't implement the solution (for reasons that are not clear to me). If it is indeed your left-click command that is causing the problem (certainly the case for me), then continuing to use code that contains this left-click statement will continue not to work. How then can you ever hope to solve your problem?

I've followed your example and now have this ultra-simple macro, for use on any selected filename in a Windows folder.

 

Mouse Left Button Click

Delay 500 Milliseconds

Macro Return

 

<LCLK><MSD:500><MRETURN>

 

As before, with any shortkey it fails. It somehow goes 'up the tree' so that the result displayed is My Computer or sometimes the Desktop folder. With any hotkey it does what it should. Namely opens the filename for editing/copying.

Please try what I suggested, by replacing the first line with a Text Type F2 command, and see if that cures the problem.

Returning to your test, are you saying that the initial left click failed to get into edit mode even with activation by a hotkey?

Yes. I'm also saying the left click somehow causes the Explorer window to lose focus!

I could have used F2 instead, but this keeps it like the original.

How ironic! It's the left click that fails (for me, at any rate)!

I inserted a Macro Return immediately after the mouse click. Running the macro any which way you like always resulted in the filename being highlighted momentarily (but not in Edit mode as far as I could see), followed immediately by the Explorer window losing Focus.

Replacing the mouse click with a Text Type of F2 gets the macro working. Then I removed my Macro Return statement, and assigned a shortkey. And it works as expected.

Even on a single machine, I have several examples of macros containing "Get Control" commands that may work one time, but not another! Microsoft Access and Sql Server are 2 examples where controls may vary from one run to another.

There's a product called True Launch Bar (which can act as a better replacement for the Quick Launch toolbar) which contains this functionality (it can invoke a different toolbar for any number of programs). Perhaps you could use this to display the correct ME floating menu.

http://www.truelaunchbar.com/

Several times in the last few months I've had straightforward macros that fail because they don't capture text to the clipboard. I now think I've found the cause. Each of these macros had Shortkeys assigned. For example, I would assign 'm1' and type '##m1' to run the macro. If I change the activation to Hotkeys, they work correctly. Change back to (any) shortkey, and it fails again.

I wrote this simple macro:

Variable Set String %T1% "This is some text"
Variable Modify String: Save %T1% to Clipboard

First I assigned a hotkey to run it, pressed the hotkey, and it ran as expected.

Then I removed the hotkey and assigned a shortkey of qq (where my shortkey combination is ;; this also ran perfectly every time

One caveat: I use Clipmate as by clipboard handler; I don't know whether this has any bearing.

No. Its intended purpose IS to control the speed of the Text Type command. If, for example, you have Keystroke Speed set for 50, and you are using Text Type to type-out "Macro Express Explained", a 50 ms delay will occur between each character in the string. Therefore, the 23 characters will require 1,150 ms (about 1 second) to type-out.

And your chosen value doesn't need to be reset at the end of your macro, since this happens automatically.

I use a ton of shortkeys for text abbreviations. Problem is that in one window of one program, ME is just too fast.

Does anyone have any suggestion as to how to either:

b. Some nifty way to insert the "If _______ Window Active, then Run Keystroke Delay Macro" lines as the first lines in 150 or more macros?

You'll need to write a macro that moves the cursor down through each macro within the selected category, opens each macro, insets the required line, saves and closes the macro (all this within a repeat group). And you need to recognize when the macro you've just opened is the same as the macro you opened last time so that you can exit the loop.

It's not too hard, I've done it heaps of times.

P.S. Any time I write a macro that needs to type out some text, I always set the keyboard speed to 30 or more.

Anyone with any thoughts on how to do this please?

Try the registry - it's an easy setting to locate.

I am running specific macros that are called from another program when certain parameters are met (through the: [C:\Program Files\Macro Express3\MeProc.exe /A**MEX Name**] code). This program doesn't only send the "run" command once, it continues to send the "run" command until the parameters are no longer met. This is fine when the same macro is being called, but my problem arises when A DIFFERENT macro is called out while the first one is still running.

How does this other program run MeProc? Is this a program for which you can change the source code? Or is there a .bat file controlling these processes?