Extract data from Excel based on value in cell

[RNIB]

I'm trying to write a macro to extract data from an Excel spreadsheet but am stuck with one issue. I should point out that I only have an old version of Macro Scheduler (Version 10) and we are using Excel 2007 so I'm having to use DDERequest.

Basically I want to save the content of each cell in a column as individual variables but the number of cells in the column will differ each time. The data in the spreadsheet always starts in cell A4 but could go down to A12 or A112 or anything else. I've added another cell in C1 which shows the number of populated cells counting from cell A4 so I've then come up with this in Macro Scheduler:

Code: Select all

//ask for location of spreadsheet.
Input>file_name,Please Browse To File Containing Headings,

//get the file name on its own
Separate>file_name,\,parts
Let>xls_name=parts_%parts_count%

//get number of headings. Spreadsheet must be open!
DDERequest>Excel,xls_name,R1C3,headings_no,60

//extract first heading
DDERequest>Excel,xls_name,R4C1,headings_1,60

So lets say headings_no =8 how do I get it to repeat this last line a further 7 times but drop down a row each time and change the variable name each time e.g.

Code: Select all

DDERequest>Excel,xls_name,R5C1,headings_2,60
DDERequest>Excel,xls_name,R6C1,headings_3,60
DDERequest>Excel,xls_name,R7C1,headings_4,60
DDERequest>Excel,xls_name,R8C1,headings_5,60
etc

[Marcus Tettmar]

Code: Select all

Let>k=0
Repeat>k
  Let>k=k+1
  Let>row=k+3
  DDERequest>Excel,xls_name,R%row%C1,headings_%k%,60
Until>k=headings_no

This will yield:

headings_1 = R4C1
headings_2 = R5C1
headings_3 = R6C1
etc

[RNIB]

Cheers Marcus!

I'm very much a newbie to all of this and only understand the basics but I can understand what you've written. However when I add this in, it doesn't seem to actually do anything. For example if I just add

Message>headings_1 after it no message is displayed

Yet if I enter Message>headings_no before it a message does pop up saying the correct number. So the script works up to this code but then seems to stop although it still shows as running.

[RNIB]

Ahh just remembered the debugger (sorry it's been a couple of years since I last used Macro Scheduler) and I can see it's getting caught in a loop.

In my example spreadsheet there are 8 rows and so the script should stop after k=8 but it doesn't and just keeps going for ever.

Still not sure why it isn't stopping though

[RNIB]

This is what is coming up in debugger

HEADINGS_17=
HEADINGS_16=
HEADINGS_15=
HEADINGS_14=
HEADINGS_13=
HEADINGS_12=
HEADINGS_11=
HEADINGS_10=
HEADINGS_9=
HEADINGS_8=Chapter 8
HEADINGS_7=Chapter 7
HEADINGS_6=Chapter 6
HEADINGS_5=Chapter 5
HEADINGS_4=Chapter 4
HEADINGS_3=Chapter 3
HEADINGS_2=Chapter 2
HEADINGS_1=Chapter 1
HEADINGS_NO=8

[Marcus Tettmar]

Need to see your code to say why, but clearly k never reaches headings_no or headings_no isn't actually set how you think. The debugger will tell you. But without your code I can't really help.

[RNIB]

This is the code, its as it was before with repeat code you kindly provided me with:

Code: Select all

//ask for location of spreadsheet.
Input>file_name,Please Browse To File Containing Headings,

//get the file name on its own
Separate>file_name,\,parts
Let>xls_name=parts_%parts_count%

//get number of headings. Spreadsheet must be open!
DDERequest>Excel,xls_name,R1C3,headings_no,60


//Extract Headings from spreadsheet
Let>k=0
Repeat>k
  Let>k=k+1
  Let>row=k+3
  DDERequest>Excel,xls_name,R%row%C1,headings_%k%,60
Until>k=headings_no

[RNIB]

If I add message>headings_no

Before:

//Extract Headings from spreadsheet
Let>k=0
Repeat>k
Let>k=k+1
Let>row=k+3
DDERequest>Excel,xls_name,R%row%C1,headings_%k%,60
Until>k=headings_no

Then the message does correctly say 8, so headings_no is being correctly read and according to the debugger K=8 so with my limited knowledge it's like the Until is being understood.

I've tried this with several different spreadsheets, tried several different numbers to define the headings_no variable but nothing works, it just keeps looping forever.

[RNIB]

Further to this is I amend the repeat code to say:

Code: Select all

Repeat>k
  Let>k=k+1
  Let>row=k+3
  DDERequest>Excel,xls_name,R%row%C1,heading_%k%,60
  Message>%k%,%headings_no%
Until>k=headings_no

Then I get a message box that comes up that counts through

1,5
2,5
3,5
4,5
5,5

So K DOES=5 and headings_no DOES=5

BUT it keeps going
6,5
7,5
etc

So for whatever reason macroscheduler isn't understanding Until>k=headings_no

[JRL]

Haven't really been following this thread but this little chunk of code should work since you are seeing the correct variable values in the message. Is there possibly a space at the end of the "Until>k=headings_no" line?

[RNIB]

Haven't really been following this thread but this little chunk of code should work since you are seeing the correct variable values in the message. Is there possibly a space at the end of the "Until>k=headings_no" line?

Unfortunately not. I've even tried rewritting the whole thing from scratch but it's just not stopping. I completely agree that it should be working and indeed it does work right up until the "Until" line.

If for example I change the until line to:

Until>k=8 (the number that is in cell C1 in my spreadsheet)

Then it does stop looping when K=8. So that would suggest that either headings_no is not being stored or that it's value isn't 8. Yet if I add this just before Until:

Message>heading_no

It does show the value of 8 (counting up from 1-8 - and beyond)

So is their a bug/limitation in MS 10.1.21 that means that you can't use a variable with an Until command?

I would love to upgrade to the latest version of MS in the hope that this fixes the problem but as a charity we simply don't have the money at the moment.

[JRL]

Version 10? Try:

Until>k,%headings_no%

[RNIB]

Version 10? Try:

Until>k,%headings_no%

Cheers for that but no joy. Also just download a trial version of MS14 and the original code doesn't work in that either.

Code: Select all

//ask for location of spreadsheet.
Input>file_name,Please Browse To File Containing Headings,

//get the file name on its own
Separate>file_name,\,parts
Let>xls_name=parts_%parts_count%

//get number of headings. Spreadsheet must be open!
DDERequest>Excel,xls_name,R1C3,headings_no,60

//Extract Headings from spreadsheet
Let>k=0
Repeat>k
		Let>k=k+1
		Let>row=k+3
		DDERequest>Excel,xls_name,R%row%C1,heading_%k%,60
Until>k=headings_no

I know it should work and Debugger shows everything is working but for some reason it never understands that k & heading_no equal the same value when they do.

This is a screen grab showing the debugger when K=8

This is a screen grab showing the code and the debugger results after K has now reached 10

I'm rapidly loosing what little hair I have

As I say, if I change the last line to
Until>k=8
It stops when it should, therefore K is working.

If I add before Until:
Message>heading_no
I see the number counting from 1 -8 and beyond as it repeats each time. Therefore heading_no is working.

So why won't they work together???????

[JRL]

Until>k=8 (the number that is in cell C1 in my spreadsheet)

Then it does stop looping when K=8. So that would suggest that either headings_no is not being stored or that it's value isn't 8. Yet if I add this just before Until:

If you're pulling the value out of a spreadsheet it may have a tab or a CRLF attached at the end. You might need to strip extra characters off of the number. Use whichever of these that is appropriate immediately after variable "headings_no" is assigned its cell value.

Code: Select all

StringReplace>headings_no,tab,,headings_no
StringReplace>headings_no,cr,,headings_no
StringReplace>headings_no,lf,,headings_no

[RNIB]

Until>k=8 (the number that is in cell C1 in my spreadsheet)

Then it does stop looping when K=8. So that would suggest that either headings_no is not being stored or that it's value isn't 8. Yet if I add this just before Until:

If you're pulling the value out of a spreadsheet it may have a tab or a CRLF attached at the end. You might need to strip extra characters off of the number. Use whichever of these that is appropriate immediately after variable "headings_no" is assigned its cell value.

Code: Select all

StringReplace>headings_no,tab,,headings_no
StringReplace>headings_no,cr,,headings_no
StringReplace>headings_no,lf,,headings_no

Thanks again but still no dice.

However you got me thinking.

I've just tried amending the code to this:

Code: Select all

//ask for location of spreadsheet.
Input>file_name,Please Browse To File Containing Headings,

//get the file name on its own
Separate>file_name,\,parts
Let>xls_name=parts_%parts_count%
Input>headings_no,How Many Headings Are There?,0

//get number of headings. Spreadsheet must be open!
//DDERequest>Excel,xls_name,R1C3,headings_no,60

//Extract Headings from spreadsheet
Let>k=0
Repeat>k
		Let>k=k+1
		Let>row=k+3
		DDERequest>Excel,xls_name,R%row%C1,heading_%k%,60
Until>k=headings_no

Now it asks for a number and if I enter 8 this DOES work.

This would suggest that your thought that there may be other characters in the excel spreadsheet is correct. But I've checked the formatting of that cell and it's set to Number and 0 decimal places.

I then thought that perhaps the fact that the value of that cell is calculated via a formula might be the issue so removed the formula and just entered the number 8 but again that doesn't work.

Yet if I just use this code:

Code: Select all

DDERequest>Excel,xls_name,R1C3,headings_no,60
message>headings_no

A message box comes up showing 8

So headings_no is being captured correctly.

Ahhhhhhhhhh!!