How would I transfer data from Excel To Autoroute

simonwilliams · Post by **simonwilliams** » Tue Feb 20, 2007 9:50 am

Hi, am wondering if the following is possible & would appreciate any help.

I have to work out routes for drivers, I get maps & I put these into MS Excel in the following format.

Copthorne/Furnace Green/Godstone/Limpsfield/Deepdene/Westcott/Redhill/Reigate/

I would like macro scheduler to be able to add these addresses into MS Autoroute?

I think I can sort this all out with the exception of getting each address into autoroute.
So basically MS would have to copy one address, put it into Autoroute, then copy the next address etc.

Any ideas how anyone???
Thanks.

Post by **Marcus Tettmar** » Tue Feb 20, 2007 9:59 am

There is an example which comes with Macro Scheduler which reads data from an Excel spreadsheet and types it into Notepad. There are a number of ways of reading data from Excel directly (not having to manipulate Excel's user interface) and the example script shows one of them. If you search this forum for Excel you will find others.

The example script simply types the text into Notepad. A rather simple example, but to get the data into a different Window would use similar commands. So I would recommend you look at that example and use it as a skeleton for your own script.

simonwilliams · Post by **simonwilliams** » Tue Feb 20, 2007 11:08 am

Thanks for the quick reply & have done a search under excel & copy date from excel but haven't found anything I could really follow.

What I want is for MS to copy the data from row D (in excel) one town at a time, I input data into row D & divide it with a / ie Brighton/London/Bognor. So how do I tell MS to copy Brighton 1st, pop it into autoroute & then come back for London etc. until it reaches the last town?

Also row D in excel changes with every new job so one day Row D might be on number 1272, the next time on number 1300, so basically I need to tell MS to copy from the last line with data from row D?

Once I know how to do these I can deal with the rest, I think!

Post by **Marcus Tettmar** » Tue Feb 20, 2007 12:23 pm

Have you seen the Excel example that comes with Macro Scheduler 9.0? It does pretty much exactly what you want.

simonwilliams · Post by **simonwilliams** » Tue Feb 20, 2007 2:36 pm

No I haven't I have the registered version of 6.2.3.
Can I download the script your talking about or would I have to upgrade & pay again???

Post by **Marcus Tettmar** » Tue Feb 20, 2007 2:41 pm

You can download the sample from:
http://www.mjtnet.com/software/excelex.zip

This script should work with v6, but I have not tested it under that version.

simonwilliams · Post by **simonwilliams** » Tue Feb 20, 2007 3:40 pm

Thanks for the link, unfortunatly I'm not having a lot of luck with this, not sure if it's cos I'm thick or if its because I'm using 6.2 version.

//Specify the path of the Excel file here
Let>filename=%SCRIPT_DIR%\example.xls

I changed example.xls to d:\My Documents\leaflets.xls but got the error iffileexists has no label parameter.

Also what is meant to go here

IfFileExists>filename

Should this be the filename, ie leaflets.xls

???

filename doesn't show up in the contents under help in my version so not sure what to do.

???

//Start Excel
ExecuteFile>filename
WaitWindowOpen>Microsoft Excel -*

//Ask how many rows we should get
Input>maxrows,How many rows shall I get?,200

//As an example I'm going to paste the data into Notepad
Run>Notepad.exe
WaitWindowOpen>Untitled - Notepad

Let>r=2
Repeat>r
//get the fields for this row
DDERequest>Excel,filename,R%r%C1,field_1,60
DDERequest>Excel,filename,R%r%C2,field_2,60
DDERequest>Excel,filename,R%r%C3,field_3,60
DDERequest>Excel,filename,R%r%C4,field_4,60

//remove the CRLF that Excel adds
StringReplace>field_1,CRLF,,field_1
StringReplace>field_2,CRLF,,field_2
StringReplace>field_3,CRLF,,field_3
StringReplace>field_4,CRLF,,field_4

//For this example we'll just paste each row into Notepad
SetFocus>Notepad*
Send>Row: %r%
Press Tab
Send>%field_1%
Press Tab
Send>%field_2%
Press Tab
Send>%field_3%
Press Tab
Send>%field_4%
Press Enter
//you'll want to replace the above lines to send the data to your real app.

Let>r=r+1
Wait>0.05
Until>r=maxrows

Else
MessageModal>Could not find: %filename%
Endif

simonwilliams · Post by **simonwilliams** » Tue Feb 20, 2007 3:48 pm

Ok, have kinda got it working but am still getting the "Iffile exists no label parameter"

I changed the Let R=2 to Let R=1272 which is my most recent colum with town names in it & it then copies all of the data from that into notepad.

But I need it to copy the data up to the / one at a time ie Worthing/Brighton/Bognor so that when done it will copy one town at a time & know that the / is a stop???

Any more help?

Post by **Marcus Tettmar** » Tue Feb 20, 2007 4:07 pm

Version 6 doesn't support the full If/Then/Else implementation that this script uses so it is complaining that IfFileExists doesn't have a label parameter.

> But I need it to copy the data up to the / one at a time
>ie Worthing/Brighton/Bognor so that when done it will copy
>one town at a time & know that the / is a stop???

It might help to see the format of the spreadsheet. Don't you have each town name in a separate row? Where does the "/" come in?

simonwilliams · Post by **simonwilliams** » Tue Feb 20, 2007 4:21 pm

Thanks for your ongoing help on this, appreciate it.

Kinda hard to show you the excel format in this box I've tried spacing things out as they would be but it doesn't work here.

Basically one driver for one job might have to go to these towns.
I have excel laid out with 9 columns, A - I.
Column D is the one I have my towns listed in in this format.

Bexhill/Westerham/Pevensey Bay/Langney/Edenbridge

I can't have each town on a seperate line as this would just confuse things with matching job numbers also needing a seperate line but I would be prepared to lose the / for a - or a space or whatever works?

See the problem is I'm typing out the town names twice, once in Excel & then again autoroute as I prepare the route which seems a waste of time, there must be an easier way?

simonwilliams · Post by **simonwilliams** » Tue Feb 20, 2007 4:25 pm

Actually I could always put a pic of the spreadsheet up, thus

Post by **Marcus Tettmar** » Tue Feb 20, 2007 4:42 pm

Right. Not a normalised data structure then

Ok, what you need is another nested loop for each row. Split out each part of the cell with the Separate command:

Code: Select all

Separate>cell,/,fields
Let>k=1
Repeat>k
  Let>thisfield=fields_%k%
  MessageModal>thisfield
  Send>thisfield
Until>k=fields_count

At the moment the sample goes through each row. You need an extra loop within the main loop which splits the values into separate fields based on the "/" character. Inside the inner loop you send the data to your target window.

simonwilliams · Post by **simonwilliams** » Tue Feb 20, 2007 4:58 pm

Erm

what

Er sorry didn't follow that at all, your way ahead of me in this game!

Where does

Code: Select all

Separate>cell,/,fields
Let>k=1
Repeat>k
Let>thisfield=fields_%k%
MessageModal>thisfield
Send>thisfield
Until>k=fields_count

[/i]
Go?

I've pasted the original script below if your feeling kind enough to fill in the parts I need

'cos being honest I don't have a clue...
I've generally alsways used MS to run progs, copy some data to something else etc but this is getting very heavy into it...
Once I have it working I can look at it & try & understand what does what.

Also how do I get it to transfer the data into the MS Autoroute search box, all I've managed to do is to get it to copy the data to notepad & open autoroute?

//Specify the path of the Excel file here
Let>filename=%SCRIPT_DIR%\example.xls

IfFileExists>filename

//Start Excel
ExecuteFile>filename
WaitWindowOpen>Microsoft Excel -*

//Ask how many rows we should get
Input>maxrows,How many rows shall I get?,200

//As an example I'm going to paste the data into Notepad
Run>Notepad.exe
WaitWindowOpen>Untitled - Notepad

Let>r=2
Repeat>r
//get the fields for this row
DDERequest>Excel,filename,R%r%C1,field_1,60
DDERequest>Excel,filename,R%r%C2,field_2,60
DDERequest>Excel,filename,R%r%C3,field_3,60
DDERequest>Excel,filename,R%r%C4,field_4,60

//remove the CRLF that Excel adds
StringReplace>field_1,CRLF,,field_1
StringReplace>field_2,CRLF,,field_2
StringReplace>field_3,CRLF,,field_3
StringReplace>field_4,CRLF,,field_4

//For this example we'll just paste each row into Notepad
SetFocus>Notepad*
Send>Row: %r%
Press Tab
Send>%field_1%
Press Tab
Send>%field_2%
Press Tab
Send>%field_3%
Press Tab
Send>%field_4%
Press Enter
//you'll want to replace the above lines to send the data to your real app.

Let>r=r+1
Wait>0.05
Until>r=maxrows

Else
MessageModal>Could not find: %filename%
Endif