Search the screen for spacific text contained in a variable

only1platinum · Post by **only1platinum** » Thu Aug 30, 2012 2:58 pm

Hi there. First time poster. I am new to MS but not new to scripting. Have done a bunch of VB scripting in excel and some limited programming. I am hoping its possible to do what I need to do with MS Pro. I just purchased a few days ago.

Basically I have an Excel file containing customer contact and order information that I am bringing in to variables in my script. I am doing this just fine. I am using these variables to pass information in to our order processing software (Epicor) to initiate orders and process them for shipment. What I would like to do is use the variable %customer_name% and search the screen for that name and if it exists then proceed one way and if it does not then proceed another. I am guessing I can use the screen capture feature some how to do this. Here is the logic in sort of code view that may help.

If (screen capture)>Search entire screen= %customer_name% Then EndIF

Else Start creating customer account EndIf

So basicly if the screen shows that the %customer_name% is already in the database then it will skip that customer record. Otherwise it will create a new entry.

Any help would be greatly appreciated. Worst case scinario I can have a popup window asking the user to physicaly look at the screen pick a yes or no option if the customer exists in the database. I am just trying t eliminate that interaction if I can.

Nick Sanders

Rain · Post by **Rain** » Thu Aug 30, 2012 3:22 pm

Hi only1,

I don't personally use Excel so all I can do is try to point you in the right direction until someone that's more qualified can help.

Macro Scheduler comes with a number of functions to automate excel. Eg. XLGetCell, XLAddSheet, XLCreate, XLDelCol, XLDelRow, XLDelSheet, XLGetSheetDims, XLOpen, XLQuit, XLRun, XLSave, XLSetCell

I think the function you are looking for is XLGetCell

Taken from the help file.

XLGetCell>XLBookHandle,SheetName,Row,Col,Result

This function requires Microsoft Excel to be installed.

Retrieves the value in cell of given row,col coordinates from specified sheet name in workbook referenced by XLBookHandle. XLBookHandle is a handle returned by XLCreate/XLOpen.

Result is a variable name to store the retrieved value in.

Abbreviation: XGC
See also: XLAddSheet, XLCreate, XLDelCol, XLDelRow, XLDelSheet, XLGetSheetDims, XLOpen, XLQuit, XLRun, XLSave, XLSetCell

Example
XLOpen>%USERDOCUMENTS_DIR%\mybook.xls,1,xlBook

XLGetCell>xlBook,Sheet1,2,2,strValue

I also found this blog entry from Marcus http://www.mjtnet.com/blog/2010/12/15/m ... -vbscript/

And this topic "Excel Search Help" http://www.mjtnet.com/forum/excel-searc ... ch%20excel

only1platinum · Post by **only1platinum** » Thu Aug 30, 2012 5:35 pm

thanks for the reply.

however I already have the excel functions worked out. I am able to draw out the information from the spread sheet. I have each one assigned to a variable that I need. What I need to do it capture whats on screen (guessing to a JPG or BMP) then search the screen for a spacific customers name. If it exists then proceed with an action based on the results. Does that make more since. I am terrible with explaining these sorts of things in word.

JRL · Post by **JRL** » Thu Aug 30, 2012 6:26 pm

Have you looked at using GetWindowTextEx>, GetWindowText> or GetTextInRect>? I think using those would be preferable to trying to find the customer name using image recognition.

That said, your best bet is to poll your software's data base to see if the customer is already there. I did a quick search to determine what Epicor uses for its data base software but did not find an answer. Someone in your organization should be able to tell you that information as well as the table and field names for the customer list. Once you know the data base you can create a connection string (you might also need a user name and password for the connection). Then its a simple matter of writing an SQL statement to poll the table and field to see if %customer_name% exists in the list.

If you can get the data base information, we can probably help you create a working connection and SQL query.

only1platinum · Post by **only1platinum** » Thu Aug 30, 2012 6:36 pm

thanks for the input. I would love to be able to communicate directly with the database. I am about the best you will get for knowing the database but thats not much. LOL I am the IT director and took over for someone who also didn't know much about epicor. I do know it is a progress database that is some how funneled through SQL in some fashion... I think. LOL Databases are not my strong point by any stretch.

I can get you the data source and Data Field info. would that be helpful. thats how I customize the program for its internality.

If you can give me more specifics I can try looking for that information.

JRL · Post by **JRL** » Thu Aug 30, 2012 7:00 pm

Code: Select all

//See Progress data base connection string info at
//http://www.connectionstrings.com/progress

Let>TableName=YourTable
Let>FieldName=YourField
Let>DSN=YourDSN
Let>ServerAddress=YourServerAddress
Let>DataBase=YourDataBase
Let>Username=YourUsername
Let>Password=YourPassword


  Let>connstr=DSN=%DSN%;HOST=%ServerAddress%;DB=%DataBase%;UID=%Username%;PWD=%Password%;PORT=2055;
  DBConnect>connstr,dbH
  Let>SQL=select * from %TableName% where %FieldName% = '%customer_name%
  DBQuery>dbH,SQL,var,NumOfRecs,NumOfFields,1
  DBClose>dbH
  
  

**BREAKPOINT**

//At this point look in the "Watch List" to see what values (if any) the variable "var" has acquired.
//You may have to experiment with the connection string and/or the SQL.  I'm uncertain what the DataBase parameter
//in the connection string designates.  If its the table name,  you might have to remove "from %TableName%" from the SQL statement.

//With a little experimentation you might figure this out.  Once you do,  the sky's the limit.

only1platinum · Post by **only1platinum** » Thu Aug 30, 2012 7:01 pm

I just used GetWindowText and GetWindowTextEx and was able to retrieve a lot of text from the search window that displays the results of a customer search however it doesn't find any of the customer names. Just the Text on all the buttons and tabs and what not. So thats a no go it seems. I guess thats because it is graphically displayed somehow. So I would have to either do a screen capture or figure out how to communicate directly with the database.

only1platinum · Post by **only1platinum** » Thu Aug 30, 2012 7:02 pm

wow, that looks like it just might work. Thank you soooooo much. this is going to be very helpful I think. I am going to mess around with this and see what I can come up with. Thanks for this input! This forum is great!