How to read .xls file using vb

[jalzaaal]

Hi all,

Is it possible to read an .xls file using vb unlike .txt file.

What i want to do is .....
i want to read the data of a cell of excel sheet and want to display it in a text box...... Is is possible?

[Comintern]

Hi all,

Is it possible to read an .xls file using vb unlike .txt file.

What i want to do is .....
i want to read the data of a cell of excel sheet and want to display it in a text box...... Is is possible?

Couple ways to do this. First, you can use the Excel automation objects to open the workbook and retrieve the value. The example uses early binding, but can be changed to late binding. This requires a reference to Excel:

Dim oXL As Excel.Application, oBook As Excel.Workbook, oSheet As Excel.Worksheet, vValue As Variant
 
Set oXL = New Excel.Application
Set oBook = oXL.Workbooks.Open("D:\Test.xls")
Set oSheet = oBook.Worksheets("Sheet1")
 
vValue = oSheet.Cells(1, 1).Value       'Get the value from cell A1
Debug.Print vValue
 
Set oSheet = Nothing
oBook.Close
Set oBook = Nothing
oXL.Quit
Set oXL = Nothing

Another way would be to use ADO to open the workbook into a recordset. This does not require a reference to Excel, but I'm not exactly sure if you need Excel installed on the machine to use Excel as a data provider. I don't have any machines without Excel to test this on. Maybe someone else could shed some light on this.

Dim oRS As ADODB.Recordset, oConn As ADODB.Connection, sConString As String, sXLFile As String, vValue As Variant
 
sXLFile = "D:\Test.xls"
sConString = "Provider= Microsoft.Jet.OLEDB.4.0;" & " Data Source=" & sXLFile & ";Extended Properties=Excel 8.0;"
Set oConn = New ADODB.Connection
 
With oConn
    .CursorLocation = adUseClient
    .Open sConString
End With
 
Set oRS = New ADODB.Recordset
 
With oRS
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Source = "SELECT * FROM [Sheet1$]"
    .ActiveConnection = oConn
    .Open
    .MoveFirst
End With
 
'Work with the RS
 
oRS.Close
oConn.Close
 
Set oRS = Nothing
Set oConn = Nothing

You can read more about this method here.

[kynn]

Hi Comintern,

I have saw ur reply but the code seems not completed. i cant compile it. Do you mind post the whole program again? Thank You so much... Your help will be greatly appreciated.

[Comintern]

It's not meant to be compiled alone -- it's just a couple code snippets to demonstrate how to do it. What exactly are you trying to do?

[kynn]

Hi Comintern,

Thanks for replying

[kynn]

We want to extract data from certain row/column of excel to text file automatically.
Example: (row 5: column B)

We tried alot of methods but we couldnt do it.

Do you have any idea on how to extract data from Excel to text file automatically?

Thank you

[Comintern]

Try using the first snippet. Add a reference to Excel, and replace the debug statement with a write to file. Or, you could wrap it in a function and have it return the vValue variable.

A search of the forum should give you all the info you need on writing the text file.

I don't have VB installed on this machine, or I could be more explicit.

[CDRIVE]

Kynn, I have a suspicion that you didn't add a reference to Excel and I also suspect that you didn't create 'Test.xls'. May I suggest that you read the Excel Tutorial here..
http://www.vbforums.com/showthread.php?t=391665
It's really the best way to learn VB/Excel.

[kynn]

thank u so much...

my excel is 2000. I am following the tutorial by adding a references but i couldnt find the references. My excel doesnt have the "project" tab. I search for the references but couldnt find it.

[CDRIVE]

thank u so much...

my excel is 2000. I am following the tutorial by adding a references but i couldnt find the references. My excel doesnt have the "project" tab. I search for the references but couldnt find it.

The Project tab is in Visual Basic, not Excel. Are you using VBA? If you're wondering what VBA is, it's the version of VB that runs behind Excel. If you're accessing VB by running Excel and then accessing VB from Tools/Macros/VB then your using VBA. VBA is not a stand alone version of VB.

[si_the_geek]

kynn, your profile says you are using VB2005. That is VB.Net, and is very different to VB6 and earlier (which is what the code here and in my tutorial are for).

For the kind of thing you need, take a look at the .Net articles in the third post of our Office Development FAQs (at the top of the Office Development forum)

If you need any help, it would be best to post a new thread in our VB.Net forum, or if interacting with Excel you could post in our Office Development forum instead.

[RobDog888]

If using VB.NET then I would also say take a look at this faq item which shows how to connect to an excel sheet from vb.net

http://www.vbforums.com/showthread.php?t=442232


The thread can be moved to VB.NET also

Ps, Thanks Si

[kynn]

hi all

I have seen the link above. However, all the tutorials there is automated from excel to office app.

I need the excel to send data to notepad(text file) consistently, like every 2 min send the data to text file automatically.

Is it possible?

[si_the_geek]

The link RobDog888 gave shows how to use Excel from VB.Net, as do several of the other items in the O.D. FAQs. You don't need Excel to do things, what you need to do is get your VB program to read the data from it and create the the text file.

However, as I said before, this is not the place to post your questions - not only is this forum for a different language you are using, but you are also hijacking somebody elses thread. Instead of replying here, post a new thread in the VB.Net or O.D. forum if you need any more help.