Selecting the correct column in Excel

[simonwilliams]

I am trying to create a macros that will take a string of towns from MS Autoroute & pop them into the next empty row in my location section of Excel

So in autoroute I might have
Brighton
London
Hove
Hastings

& I want these all copied into Excel in the Location Column in the next empty row, so they read like the following :-
Brighton, London, Hove, Hastings

So 1st off

1. How do I get MS to select the Location field & then the next empty row of that column in Excel?
2. How do I get MS to add a comma & a space to the end of the town name in Excel?
3. Once it has added one town name how do I get it to add the next town name without deleting the previous town name?

So far I have

SetFocus>Worthing - Worthing.axe - Microsoft AutoRoute
Wait>1
MouseMove>46,264
LClick
Press ALT
Send Character/Text>E
Send Character/Text>C
SetFocus>Microsoft Excel - Leaflets.xls

Appreciate any help. Thanks

[Marcus Tettmar]

There's an example script which comes with Macro Scheduler showing how to directly set and retrieve cells in Excel spreadsheets.

This will also be helpful:
http://www.mjtnet.com/blog/2007/07/02/m ... xcel-data/

To add a comma and a space to a string just concatenate them:

Let>string=%string%,%SPACE%

[simonwilliams]

Thanks for the reply however the script that came with MS is for extracting data from excel & the link you gave me, I read through it, it was a bit on the tech side, head was spinning & I could see no reference to what I want.

Maybe if I break it down.

I have something in my clipboard.

I want to paste it into the next empty line in Excel.
In this case the column is named Location & as far as Excel is concerned it is column D

Any idea how to do that? Thanks

[Marcus Tettmar]

Both examples show the code for adding data to Excel. The simplest is to use DDEPoke:

DDEPoke>Excel,c:\my stuff\mybook.xls,R2C4,some_text

Where R2C4 means Row 2, Column 4

The examples provided demonstrate how to read data from Excel. Using DDERequest we could loop through the column until we find no data. I notice that an empty cell actually has one line feed in it. So we can look for that.

So the following code finds the first empty cell in column D and then puts the contents of the clipboard in it:

Code: Select all

Let>xlFilename=d:\ex2.xls

//find first empty row in column D by looping through all rows until nothing is returned
Let>k=0
Label>scan
  Let>k=k+1
  DDERequest>Excel,xlFilename,R%k%C4,result,0
  If>result<>CRLF,scan

//So k is first empty cell, put clipboard contents in it
GetClipBoard>data
DDEPoke>Excel,xlFilename,R%k%C4,data

Now, assuming your script is looping through lots of input data you need only do that first scan once, and there on increment k each time you add new data. Or put that scan code into a subroutine and call it each time you want to add data (though that would in theory be slower).

You may find it easier to do it by sending keystrokes, thereby simulating a user. In which case remember that CTRL-HOME goes to cell A1. RIGHT goes one cell to the right and CTRL-DOWN goes to the last cell in the range. And pressing DOWN from there will get you to the empty cell at the end of that range, where you want to put your data. And CTRL-V does a paste. Putting that lot together, all you need to do is:

Code: Select all

//Focus Excel
SetFocus>Microsoft Excel*

//Press CTRL-HOME to get to cell A1
Press CTRL
Press Home
Release CTRL

Wait>0.5
//pressing right 4 times from A1 will get you to D1
Press Right * 4

Wait>0.5

//Press CTRL-DOWN to get to last cell in column
Press CTRL
Press Down
Release CTRL

Wait>0.5

//Press Down once more to get to first empty cell
Press Down

Wait>0.5

//Paste:
Press CTRL
Send>v
Release CTRL

But you already knew that because you use Excel. So all you have to do is tell Macro Scheduler to do what you do. ;-)