Retrieving data from a n Excel file?

[jwarmuth]

Okay, so here's my situation. I have a supplier that lists their products and pricing daily on their website. Problem is, they use ASP 1, and have no support for exporting XML or any thing for that matter.

So can save teh asp file as an html doc and import it into Excel with no problems. It displays all the columns and rows exactly as I need them.

Now I just need to figure out how to extract the info from the workbook. Unfortunately, everything I can find on working with excel objects is always (rolls eyes) under teh assumption I want to put data INTO the spreadsheet. I don't! I want only to extract info.

So let's say I wanted to read cell 1,5 (from file c:\temp\product.xls) and throw it into a text box in my vb.net app.

How can I do this? Even the MSDN only has info about entering and creating, nothing on retrieving. :/ TIA!

Jeff Warmuth
MCSE, CNE
ICQ 129152989

 

[JignasuK]

Jeff,
Here it is. It is very simple. Below is the code on how to do that. I am assuming you know how to create object to workbook and open them! So here it is....

someVariable = xlsSheet.Range("J18").Cells 'individual cells
someVariable = xlsSheet.Range("J19").Cells 'individual cells

you can even do this...

someVariable = xlsSheet.Range("A1:E1").Cells 'Range of cells

Or even this...

someVariable = xlsSheet.Cells(1, 3).Value

I hope this is what you are looking for. If not let me know...

Jignasu (Jim) Khant

 

[jwarmuth]

That looks bang on! Thanks, I'll get back to work experimenting with that code.

Jeff Warmuth
MCSE, CNE
ICQ 129152989

 

[jwarmuth]

Okay, i'm pulling info by the single cell only. What's teh best way to work with multiple cells variably?

For example:

Let's say I wanted to find out how many rows there are in the worksheet, then go row by row and insert the cells of said rows into an array.

I tried accessing the worksheet.rows.count method but can't get it to convert properly.



Jeff Warmuth
MCSE, CNE
ICQ 129152989

 

[jwarmuth]

Got it.

dim rowNum

rowNum = xlsheet.rows.count


Problem now is that it returns 65536! lol

How do I limit it to the last row?

Jeff Warmuth
MCSE, CNE
ICQ 129152989

 

[JignasuK]

Jeff,
The way I usually do is look at few column I know for sure should have some data. If there are no data in for three continous rows, that is end of the file. I don't know any build in functions that does that.

Here is something that might work for you...

Do Until blnExit = False

'You can check more than one cell just to make sure it is indeed eof. At one time I used upto 15 cells!
if xlsSheet.Cells(i, j).value = ""
emptyCounter += 1
else
emptyCounter += 0
end if

if emptyCounter = 3 then
blnExit = True
end if

rest of the processing...
Loop

Jignasu (Jim) Khant