Extrating 1 row out from spreadsheet

[RNIB]

I know the answer must be staring me in the fact but I cant see the wood for the trees today.

I have a spreadsheet which contains catalogue numbers in Column A and titles in Column B. In Cell E1 I also have a formula that counts the number of rows that have been entered in the spreadsheet.

What I need to do is to extract each cell from both columns and store as a variable so that I can then use them independently and together in other parts of the macro later on.

This is my code so far:

Code: Select all

//Locate Spreadsheet of DMO Titles
//ask for location of spreadsheet.
Input>file_name,Please Browse To The Spreadsheet Containing The Headings,

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

//get number of titles. Spreadsheet must be open!
DDERequest>Excel,xls_name,R1C5,title_no,60

//Remove invisible characters from spreadsheet value. NEEDED!
StringReplace>title_no,tab,,title_no
StringReplace>title_no,cr,,title_no
StringReplace>title_no,lf,,title_no

//Extract TB Numbers & Titles from spreadsheet
Let>k=0
Repeat>k
		Let>k=k+1
		Let>row=k+1
		DDERequest>Excel,xls_name,R%row%C1,TB_no_%k%,60
		DDERequest>Excel,xls_name,R%row%C2,Book_Name%y%,60
Until>k=title_no


//Test to see if it worked
MessageModal>%TB_no_1% %Book_Name1%

When I run this the message that comes up is showing the catalogue number and title from row 2 not row 1 and the title just shows as Book_Name1 so obviously it's not extracting row 1 properly but I can't work out what it is I've done wrong.

[JRL]

Assuming by "Spreadsheet" you mean Excel.
I use this in hundreds of scripts. Make sure the sheet name you want to access is in the SQL line.

Let>SQL=select * from [Sheet1$]

(you need to keep the brackets and the dollar sign)

After running you will have an array named "cell" that should contain everything in your speadsheet. Simply work with that array

Code: Select all

Input>DataFile,Select Excel data file
If>%DataFile%=
  Exit>0
EndIf

      //////////////////////////  Excel Connect  //////////////////////////
      Let>connStr=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%DataFile%;Extended Properties="Excel 12.0 Macro;HDR=YES";
      DBConnect>connStr,dbH
      Let>SQL=select * from [Sheet1$]
      DBQuery>dbH,SQL,Cell,nR_excel,nF
      DBClose>dbH
      /////////////////////////////////////////////////////////////////////

[RNIB]

Unfortunately I only have Macroscheduler 10 so I don't think the SQL route is supported. Plus my knowledge of working with arrays like that is somewhat pitiful

[JRL]

Just checked history for "dbConnect" and it was added in Version 10.1 dated 07/04/2008. So you should be able to use the script I provided.

Try the script and see if you can make it get results. If you can get results we can help you work with the array. Trust me that this is the better way to acquire data from excel. DDE is extremely slow.

If you have to use DDE, see Bob Hansen's post on the subject
viewtopic.php?f=2&t=2193&hilit=dderequest