Open Excel file from Visual Basic
 

Hi,

I already have an excel file (MyXL.xls) and a Visual Basic (Form1.frm) with a command button. Now, I want to write a code for the Visual Basic so that when I click on the command button, the excel file will be opened.

Any help is significant to me! Thank you in advance.

DTBN

Re: Open Excel file from Visual Basic
 

Include excel type library in the project references and then use the following:

  Dim xlTmp As Excel.Application

  

  Set xlTmp = New Excel.Application

  xlTmp.Workbooks.Open "MyXL.xls"

Cheers,

Re: Open Excel file from Visual Basic
 

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

Re: Open Excel file from Visual Basic
 

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

Re: Open Excel file from Visual Basic
 

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

Re: Open Excel file from Visual Basic
 

Is cell 1, 1 empty?

Re: Open Excel file from Visual Basic
 

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

Re: Open Excel file from Visual Basic
 

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

Re: Open Excel file from Visual Basic
 

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

Re: Open Excel file from Visual Basic
 

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