Open Excel file from Visual Basic

0

hi
I want to know how to read from excel file
i know how to open it but how to store cell's value from excel to the variable
i wrote
varname=xlsheet.Cells(1, 1)

and i got run timeerror
"cast from range to "string not supported

manal

Junior Poster

122 posts since Mar 2006

Reputation Points: 37

Solved Threads: 17

0

varname=xlsheet.Cells(1, 1).value?

Taboo Programmer

Team Colleague

2,910 posts since Dec 2004

Reputation Points: 361

Solved Threads: 215

0

varname=xlsheet.Cells(1, 1).value?

thanks....
i don't have now run time error
but i tried to display result on textbox i always have empty textbox???

manal

Junior Poster

122 posts since Mar 2006

Reputation Points: 37

Solved Threads: 17

0

Is cell 1, 1 empty?

Taboo Programmer

Team Colleague

2,910 posts since Dec 2004

Reputation Points: 361

Solved Threads: 215

0

No......it dose have value
i want to write program that read the names of student of specific section from excel file and display it in textbox

actually each cell has name of student

manal

Junior Poster

122 posts since Mar 2006

Reputation Points: 37

Solved Threads: 17

0

No......it dose have value i want to write program that read the names of student of specific section from excel file and display it in textbox

actually each cell has name of student

Not sure if you've solved this yet. I had exactly the same problem. Got this to work eventually. I needed to explicitly declare a1 as a range

Dim xlApp As Excel.Application = New Excel.Application
xlApp.DisplayAlerts = False
xlApp.Workbooks.Open("c:\test.xls")
Dim xlSht As Excel.Worksheet = xlApp.Sheets(1)
Dim xlRng As Excel.Range = xlSht.Cells(1, 1)
Textbox1.Text = xlRng.Value

Dan

Newbie Poster

5 posts since Mar 2006

Reputation Points: 10

Solved Threads: 1

0

Not sure if you've solved this yet. I had exactly the same problem. Got this to work eventually. I needed to explicitly declare a1 as a range

Dim xlApp As Excel.Application = New Excel.Application xlApp.DisplayAlerts = False xlApp.Workbooks.Open("c:\test.xls") Dim xlSht As Excel.Worksheet = xlApp.Sheets(1) Dim xlRng As Excel.Range = xlSht.Cells(1, 1) Textbox1.Text = xlRng.Value

Dan

I used your code to open an Excel file and store its cells in an array
but it gives me run_time error7 : out of memory

and when I check the task manager Excel.EXE is still running and the computer become slow.

Private Sub Form_Load()
Dim xlTmp As Excel.Application
Set xlTmp = New Excel.Application
xlTmp.Workbooks.Open "C:\Book1.xls"
Dim xlSht As Excel.Worksheet
Set xlSht = xlTmp.Sheets(1)
ReDim Preserve Ucode2(50, 5)
For i = 2 To xlSht.Cells.Rows
    Ucode2(i, 0) = xlSht.Cells(i, 1)
    Ucode2(i, 1) = xlSht.Cells(i, 2)
Next

xlTmp.Workbooks.Close
xlTmp.Quit

and I wanted to display this array in two different boxes :

Function show1()

For i = 0 To UBound(Ucode2)
    textb0x3 = TextBox3 + Ucode2(i, 0)
    TextBox2 = TextBox2 + Ucode2(i, 1)
Next
End Function

and I called this function by this button click

Private Sub CommandButton1_Click()
Call show1
End Sub

Newbie Poster

6 posts since Mar 2006

Reputation Points: 10

Solved Threads: 1

0

Sorry,

I did a bad thing. I just put part of the code. You should release all the COM objects when done with them in reverse order. That's every workbook, sheet, range etc. something like:

Private Sub btn1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn1.Click
Dim xlApp As Excel.Application
Dim xlSht As Excel.Worksheet
Dim xlRng As Excel.Range

Try
xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlApp.Workbooks.Open("c:\test.xls")
xlSht = xlApp.Sheets(1)
xlRng = xlSht.Cells(1, 1)
Textbox1.Text = xlRng.Value

Catch ex As Exception
Textbox1.Text &= ex.ToString
Finally
xlApp.Workbooks.Close()
ReleaseComObject(xlRng)
ReleaseComObject(xlSht)
ReleaseComObject(xlApp)
xlSht = Nothing
xlApp = Nothing
GC.Collect()
End Try
End Sub

Dan

Newbie Poster

5 posts since Mar 2006

Reputation Points: 10

Solved Threads: 1

0

I forgot to say you need
Imports System.Runtime.InteropServices.Marshal for
ReleaseComObject()
Also I'm not sure if you actually need xlApp = nothing. Seems ok without it.

Dan

Newbie Poster

5 posts since Mar 2006

Reputation Points: 10

Solved Threads: 1

0

Nope, that's for .NET, which doesn't apply. Imports doesn't work in Legacy Vb's. However, setting the objects back to nothing is the proper way to go about this. Otherwise, you'll have tons of exe's (as objects) just floating around and eating up Ram.

Taboo Programmer

Team Colleague

2,910 posts since Dec 2004

Reputation Points: 361

Solved Threads: 215

0

Yep. Sorry,

I should check which forum I'm in before trying to be clever.

Newbie Poster

5 posts since Mar 2006

Reputation Points: 10

Solved Threads: 1

0

Nope, that's for .NET, which doesn't apply. Imports doesn't work in Legacy Vb's. However, setting the objects back to nothing is the proper way to go about this. Otherwise, you'll have tons of exe's (as objects) just floating around and eating up Ram.

As you have mentioned VB 6 does not support import.
So what should I do for solving my problem?
I want to put my excel cells in an array.

I added this code also but still Exel.EXE is remaining in memory

System.Runtime.InteropServices.Marshal.ReleaseComObject (xlSht)
System.Runtime.InteropServices.Marshal.ReleaseComObject (xlWBook)
System.Runtime.InteropServices.Marshal.ReleaseComObject (xlApp)

Newbie Poster

6 posts since Mar 2006

Reputation Points: 10

Solved Threads: 1

0

System.Runtime.InteropServices.Marshal.ReleaseComObject (xlSht)
System.Runtime.InteropServices.Marshal.ReleaseComObject (xlWBook)
System.Runtime.InteropServices.Marshal.ReleaseComObject (xlApp)

will not work in vb6. That, too, is vb.net. In order to have the excel.exe application close from the process list, you are going to need to SET the objects to Nothing. Just like you set the objects to something (be it a workbook, or excel.application, or whatever), you have to then set it back to nothing. One thing about programming (in any language) that I truly love, and that is a pretty strict rule, is that "Anything That You Open, You Must Also Close." Now, while it may not seem that creating an object is the same as opening one, it is. If you do an if statement, what else must you do? You must End If, if you while you wend, if you do you loop, if you open you close, if you create, you destroy.

set xlSht = nothing
set xlWBook = nothing
set xlApp = nothing
set xltmp = nothing

Taboo Programmer

Team Colleague

2,910 posts since Dec 2004

Reputation Points: 361

Solved Threads: 215

0

I have written this code but still Excel.EXE is in task manager
If I run the program 3 times 3 EXCEl.EXE is in task manager

I have another problem also
As I said I want to store the cells in an array as the form loads
The code is :

ReDim Preserve Ucode2(101, 100)
For i = 2 To xlSht.Rows.Count
    Ucode2(i, 0) = xlSht.Cells(i, 1).Value
    Ucode2(i, 1) = xlSht.Cells(i, 2).Value
Next

But I have run time error '9' : Subscript out of range

Would you please help me?
Thanks.

Newbie Poster

6 posts since Mar 2006

Reputation Points: 10

Solved Threads: 1

0

Attach your project.

Taboo Programmer

Team Colleague

2,910 posts since Dec 2004

Reputation Points: 361

Solved Threads: 215

0

Attach your project.

I have attached my project.
I want to store the cells of the Excel file in an array named Ucode2
but there is runtime error 9 : subscript out of range.

Attachments New_Folder.zip (4.26KB)

Newbie Poster

6 posts since Mar 2006

Reputation Points: 10

Solved Threads: 1

0

And the excel document.