Transferspreadsheet Range option in regards to worksheets in excel.

[ogg13]

Stupid question that im sure will be answered pretty darn quickly incoming 8)

The range option on the TransferSpreadsheet action allows you to specify a worksheet and cell range to work with. I got that working just fine, but is there any way to specify to pull all data thats not blank instead of a cell range?

IE:

I dont want to limit my import to a certain arbitrary amount of rows, and I dont want my users to have to specify how many rows are being imported from the excel file.

Thanks!

 

[Moniker]

You can either used a named range within your Excel sheet (which can be sketchy depending on inserting/deleting rows), you can use the UsedRange property of Excel (requires a connection to the Excel application object and some programming know how), or you can just run a query against the input to remove the blanks, which is the best method.

SELECT field_name FROM table_with_blanks_in_rows WHERE field_name IS NOT NULL

Assuming you are inputing one row, that's your record set minus the blanks.

 

[ogg13]

Cool, the last option was what I was using, just seemed like I was doing something... bad... by pulling 100 or so blank records into my query in the assumption that the excel worksheet wouldnt ever exceed that count. Thanks!