1.11M Members

Open Excel file from Visual Basic

 
0
 

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

 
0
 

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

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,

 
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

 
0
 

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

 
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???

 
0
 

Is cell 1, 1 empty?

 
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

 
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

 
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
 
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

 
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

 
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.

 
0
 

Yep. Sorry,

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

 
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)
 
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
 
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.

 
0
 

Attach your project.

 
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)
 
0
 

And the excel document.

 
0
 

And the excel document.

you did not check my zipped folder.
you can find the excel document file also in the folder.

thanks

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article