MrBiggz

Hi,

I'm trying to do something very similar. Basically I would like it to open a site, prompt for input of a 5 digit number and then have ME find it on the site and scrap the data around it and paste it into a text box for displaying. I would like it to do this without opening the webpage fully, if it can do it hidden or perhaps minimized would be best. This is far as I've gotten so far.

<CLEARVAR1:T:ALL><TVAR2:01:02:FEnter 5 Digit numberFFCenter:Center><HTTP2:1:T:http://bob/main/officeListingServlet?country=USA&state=state_all&div=select_all&next=USCA_LIST&search_type=USCA&country_list=USA&state_list=state_all&div_list=select_all><TEXTTYPE:<CONTROL>f><MSD:0150><TEXTTYPE:%T1%>

I GOT IT!! So here's the final code that got it to work!

Clear Text Variables: All

Variable Set String %T1% from Prompt

Text File Begin Process: "Test.csv"

If Variable %T2% Contains %T1%

Break

End If

Text File End Process

If Variable %T1% = variable %T2%

Text Box Display: DIA

End If

If Variable %T2 does not contain variable %T1%

Text Box Display: NO DIA

End If

Thanks. It kinda worked. So the CSV file has in the first column about 300 cells filled with information. I just need to do a check against that CSV file to see if the data entered from prompt (%T1%) is anywhere in that column. And if so display "Verified" and if not, display "NOT Verified". Unfortunately it's only reading the last cell that has data and putting that into variable %T2%. I'm not sure if I'm doing this correctly at all, I'm just kind of playing around to see if it is actually possible.

Thanks for the help!!

I need to create a Macro that prompts for an office number, and when the office number is given, it checks against a CSV file to see if that office is listed. If listed it returns a message on the screen of "VERIFIED" and if not's found in the file, returns a separate message of "UNVERIFIED". The problem I'm having is it checks against the file, but it returns both VERIFIED and UNVERIFIED messages even the office numbers are listed. Here's a copy of the code.

<CLEARVAR1:T:ALL><TVAR2:01:02:FEnter 5 Digit Branch NumberFFCenter:Center><BTFBEG:002:000001:000000:C:\Test.csv><IFVAR2:4:02:1:T1><TBOX4:T:4:CenterCenter000278000200:000:DIAVERIFIED><ENDIF><IFVAR2:4:02:8:T1><TBOX4:T:4:CenterCenter000278000200:000:NO DIAUNVERIFIED><ENDIF><BTFEND>

I'm hoping someone can help with an issue I'm having, not sure if it's possible but I would like to think it is. I haven't started the macro yet as I'm trying to figure out the best way to do this.

The two elements of the macro are entering information into a web page (intranet) that displays some information and then to take that information and verify it against a spreadsheet. If the information is found in the spreadsheet, the macro will display a message saying "verified", and if it does not find the information in the spreadsheet, it displays a message that says "Not Verified". Does anyone have experience doing something like this or know how to begin to do this? Ideally I'd like to keep the spreadsheet hidden, but that doesn't work, so keeping it minimized would be the next best option as it's doing the lookup/query in the spreadsheet.

Hopefully I've helped explain this clearly enough for everyone.

Thanks again in advance for any help given!

Mr. Biggz,

 

You're welcome! The only lines you need to manipulate are:

 

 

Line #3- This is the name of the Excel file you want to extract info from. If you want it to be kept closed, then change line #5 to False. Find the name of your weeksheet within your Excel file(the tab name- for example, Sheet1)where all your info is. Plug it into ("Sheet1"). If the tab is Sheet1 already, you don't have to do anything. On Line 7, this is your output file in .csv form. Where do you want your output to go? Put it there. Save this text file as .vbs. Click on the .vbs file to test run it and you will see your Excel file converted to comma separated and then code Macro Express to find the information you want.

 

Now you can run Macro Express to run this using whatever trigger you would like. It outputs to the .csv file overwriting the prior .csv file everytime.

 

If you have anymore questions, please ask!

 

Pat

GOT IT! Thanks for the help Pat. Now on to figure out how to extract the data!

Here you go:

 

http://blogs.technet.com/heyscriptingguy/archive/2005/03/22/how-can-i-save-a-single-excel-worksheet-to-a-csv-file.aspx

 

Const xlCSV = 6

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Testsheet.xls")
objExcel.DisplayAlerts = FALSE
objExcel.Visible = TRUE

Set objWorksheet = objWorkbook.Worksheets("Sheet1")
objWorksheet.SaveAs "c:\scripts\test.csv", xlCSV

objExcel.Quit

 

Just change obj.Excel.Visible to False and you could extract text from the text file and parse it with Macro Express. Just remember you need to save it as a .vbs extension and have the vbscript run however way you want it.

 

Pat

Thanks Pat. Unfortunately I don't know vbscripting to get this to work as you have entended. I've copied the contents to notepad and saved as .vbs file, however from there I'm not sure what else to do. I'm going to play with this for a bit and see what I can do.

Thanks again for the help

Hello Mr. Biggz,

 

I still don't know how to use Macro Express exclusively to do this. The only other way is to use a VBscript OR use AutoIT/Autohotkey to make the spreadsheet transparent. You can also use VBA in Excel. One other way is to move your open Excel file(using ME to move it) to a corner of your screen and mine your information that way. Most of these ways will show the file minimized in your task bar though. Some people want the file hidden or closed.

 

Pat

Hi Pat,

Well from balithag's post it sounds like he knows how to do what I'm trying to do, but I could be wrong. I would prefer this to file to be closed rather than open. Hopefully someone can help. :/

I'm still fairly new to ME (best program EVER) and what I'm trying to do I think is the same thing. I have an excel spreadsheet that has a list of locations (Store #/City/State). I want to be able to run a macro that prompts me to enter one of these pieces of information and it brings up the rest of the information. Such as if I enter the Store # 1234 it displays the City and State information. Or if I enter in the City, it displays the Store# and State information. Sometimes we close stores and open new one's so the spreadsheet is always changing. Using ME to search and display this would be much easier and quicker than having to find the spreadsheet, open it, search it, and hope you have the latest information.

Thanks in advance for the help!

Is there a way to log when a .mex file is edited and changed? So every time someone add's a new Macro, makes a change, to the .MEX file, it will add that to the file? We have a shared .MEX file that everyone uses. Some is doing something and we want to be able to identify who. I have been able to create a macro that can pull username from the registry, I just need to figure out the rest.

Thank you very much for the help

I have seen it occasionally, but I don't know the cause. I think it was while I was using the editor. When you say you were "not doing anything in ME" do you really mean you were not doing anything? Or were you using the editor?

 

Sounds like a good question to email to technical support.

Sorry for the late response. But yes, literally not in ME at all, then that message will pop up. However I haven't had that error in quite some time now. So maybe it was a corrupt macro that's now been deleted?? I dunno.

If all users are sharing the same macro file, and if that macro file has a floating menu in it, then all users will see the floating menu. Do you have a floating menu? And is it being enabled somewhere or by someone?

Yes there is floating menu's. However I tested activating them and seeing if they popped up on other user's screen's, and it did not. Its just kind of random.

We currently store our MEX file on a Shared Network Drive for everyone to access. And just lately we have had random floating menu's just pop up on everyone's screen's. Users will not even be touching their keyboards or doing anything to cause these to randomly pop up on the screen. Anybody experience this before?

Has anyone seen this error before? And what does it mean? I'm not doing anything in ME and this error will just pop up.

Thank you

That's kind of scary! LOL...I just did it, it worked, but scary!

Thank you all for your replies.

Rustywinger - I tried your suggestion, still doesn't work! I even went one step further and tried Variable Set Integer %N11% from Screen Height and Variable Set Integer %N11% from Screen Width to map X/Y coordinates based on Window size, but still no luck. I think I'm heading down the right path however. Since the Icon is in the upper right hand corner, I'm going to try to use that as my starting point with the mouse, and come down and left to the icon.

Ok I've found that, read the help section, and please forgive my ignorance, but I'm kind of getting it, but not really! lol

So I have this window, and in it there is a "Save" icon I want to get to. What I'd like for the macro to do is first determine the resolution of the screen, and then to be able to know which mouse movements to make (because "Click on Control" doesn't work) based on their screen resolution.

Or maybe this is a better way to do it. Based on the window size, move to X and Y location from left edge of current window. Now regardless of screen size, the distance from the left or right edge of that window to that icon "should" be the same, right?! Maybe this would work better?

So I'm not sure which commands to use. The "Set Value from Current window top" or "Set Value from Screen Width/Height" commands to get what I need.

Are you moving the mouse relative to the screen, or relative to the application window? I have found that window-relative works reliably for me at different resolutions, whereas screen-relative does not -- though I don't really understand why.

 

Other than that, there is a Get Control command, followed by the Mouse Click on Control command, that might work. There's a little tutorial within the Help screens that leads you through the process using the Windows Calculator application as an example. It's laborious to set up because you have to do each icon separately in your macro, using the Get Control Utility. I am told it works well for some applications but not for others.

 

I believe that, if Get Control works for you, you can then get the text associated with the control. That might be useful to assure that you are really pointing to the correct icon before you click on it.

Unfortunately the Windows Controls aren't programmed correctly into the App. When I try the "Get Control" and "Click on Control", there is a click, I can see it, but nothing happens. So I have to use the "Mouse left click" command, and that works.

Using the relative to window or screen is where I'm having problems. I've tried both and neither work.

Is there a way to say "IF Screen resolution is XYZ", then use these mouse positions, IF Screen resolution is ABC, then use these positions". Not sure if I'm explaining that right, but I hope you get the point!

That works. It's just not as elegant IMHO as there is only one variable to reference my way <DT:yyyy-mm-ddT:01:1:>. But your way is best if you want to use the file creation date instead of the time the macro fired. But in your post you said you wanted to use the "day's date" hence my suggestion.

 

Also when I create date strings for filing I like to put the date first and use a reverse order so that when you sort alphabetically they appear chronologically. If you have years of reports all the January reports appear in one block. For this reason I like to also pad my months and days EG 2008-08-06 instead of 2008-8-6. That way October doesn't appear after January. I used to use the creation date to view things chronologically but eventually a file is restored, moved or something to reset the creation date.

 

You can still use your method and pad as well. Just convert the integer to a string. Pad left with spaces for two. Replace space with zero. It's super simple, 3 lines, and you don't even need a condition.

<NMVAR:05:01:0:0000001:0:0000000><TMVAR2:14:01:00:002:000:><TMVAR2:21:01:00:000:000: 0>

Yeah honestly I like your way better! I'll be switching to your suggestion as it is more of what I'm looking for! Thanks again!

Use the Text > Date/Time command to create a date string you can use in the filename. You can customize the format as well to be any order you like. I like the "2008-08-05 11.54" format personally. If you saved it to T1 it would look like "Report_%T1%.pdf"

Thank you for the suggestion. I did get it to work, but did it a bit differently. Let me know if you see any problems with doing it this way:

=============================================

Copy file from C:\Reports\Report.pdf to G:\Group Reports\Report.pdf

Delay: 2 Seconds

Variable Set from File Date/Time (Get File Date/Time>Creation Date/Time <Month=N1/Day=N2/Year=N3>)

Rename File or Files to: G:\Group Reports\Report_%N1%-%N2%-%N3%.pdf

=============================================

It game me the desired naming convention I was looking for. Thoughts?

Well I guess not so much sequential, but looking to setup an automated move and rename a file macro to include the date in the file name.

I have a report that runs and kicks out a report and saves it to a PDF. What I want to do is have the macro run at some point during the night to move the file from one location to a shared location, but renaming the file with the day's date. I can move and rename the files no problem, but how do I set it up so when it's renaming the file, it knows today's date and rename's the file "Report_today's date.pdf"?

Hope this makes sense and it can be done.

Thanks,

Biggz

I'm new to using ME, and love it!! But have run into a snag. I'm trying to create a set of macro's for a group of people who basically do the same tasks day in day out. The problem I'm running into is that I'm trying to get control of entering data into a window that has no "Windows Controls", so no File/Edit/View/etc...drop down menu's from the top of the window, just built in Icon's in the app. In order to get to that Icon I've tried using mouse location, but because of monitor size and screen resolution differences, the location is on each of the desktop's, it puts the mouse on different locations for different people. End result is it either clicks on something it's not supposed to or nothing at all.

So I guess my question is can I tell it to find an Icon in the window and then click on it?

You know I tried that and it didn't work. However it's working now!

Thanks so much!

By pressing the key's "ALT+S" will send an email after typing it up. When trying to do that with a Macro it does not work. I've found that if MSWord is your email editor, this will not work. If MSWord is NOT your editor, it does work. Does anyone know why or how to send while MSWord IS the email editor?