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

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,

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

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

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

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

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

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

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

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.

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

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.

And the excel document.

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

thanks

Oops, I don't have excel installed.... :(

I thought it was part of the vb project files, I'll check it out

I don't know VB6 but I do have Excel.
In .net I get xlApp.Rows.Count = 65536 (i.e. it doesn't recognise where you've stopped inputting values) . If you get this too, this is your subcript out of range problem, and an unhandled error will stop your cleanup code from running, which is why you still have exes running.
You need a do .. until loop (or similar) and check for an empty cell.
You also need to handle runtime errors and put the set xlApp = nothing etc in this section.
I don't know vb6, so I won't embarass myself by trying to write any code.
Sorry I can't be more helpful but hopefully this helps with the array problem.

I don't know VB6 but I do have Excel.
In .net I get xlApp.Rows.Count = 65536 (i.e. it doesn't recognise where you've stopped inputting values) . If you get this too, this is your subcript out of range problem, and an unhandled error will stop your cleanup code from running, which is why you still have exes running.
You need a do .. until loop (or similar) and check for an empty cell.
You also need to handle runtime errors and put the set xlApp = nothing etc in this section.
I don't know vb6, so I won't embarass myself by trying to write any code.
Sorry I can't be more helpful but hopefully this helps with the array problem.

Thanks a lot it helped me too much.
I removed xlApp.Rows.Count , I replaced it with an integer number
now my program is working and Excel.EXE is not remaining in task manager any more.

For i = 0 To 200 
    If xlSht.Cells(j, 1).value = "" Then Exit For
    Ucode2(i, 0) = xlSht.Cells(j, 1).value
    Ucode2(i, 1) = xlSht.Cells(j, 2).value
    j = j + 1
Next

Thanks a lot for those who helped me

additional question...
how to run vba form from excel using VB 6
i means when i click button at vb 6 , it will automatically open the form (created by excel)
please email an answer to me (amirtmn@yahoo.com)

What happend when tha .xls file is not exists?
How can I except it?

on error goto err
your codes here

exit sub
err:
msgbox "File does not exist",vbcritical

Edited 6 Years Ago by nathan_jun2: n/a

Hi all
here is a code that will open the excel file and will read all sheets

'Started By Deepak Shitole  27/07/2011
Option Explicit
Dim objExcel As Excel.Application
Dim objXlSht As Excel.Worksheet
Dim i As Double
Dim blnOpenExcelObj As Boolean

Private Sub cmdBrowse_Click()
    CommonDialog1.ShowOpen
    txtpath.Text = CommonDialog1.FileName
End Sub

Private Sub cmdImport_Click()
    Dim dblBlankRows As Double
    Dim dblBlankCols As Double
    Dim intExcelRow As Integer
    Dim intExcelCol As Integer
    Dim intNoOfSheets As Integer
    Dim intCurrentGrid As Integer
    Dim intCurrentSheet As Integer
    If txtpath.Text = "" Then
        MsgBox "Please select the file name properly", vbCritical
        Exit Sub
    End If
        
    Set objExcel = New Excel.Application
    objExcel.Workbooks.Open txtpath.Text
    blnOpenExcelObj = True
                                            
    intNoOfSheets = objExcel.Sheets.Count
    intCurrentGrid = 0
    objExcel.Visible = False
    For intCurrentSheet = 1 To intNoOfSheets
            Set objXlSht = objExcel.Sheets(intCurrentSheet)
            dblBlankCols = 0
            For i = 1 To 50
                Debug.Print Trim(objXlSht.Cells(1, i).Value)
                If Trim(objXlSht.Cells(1, i).Value) = vbNullString Then
                    dblBlankCols = i - 1
                    Exit For
                End If
            Next
            dblBlankRows = 0
            For i = 1 To 65000
                If Trim(objXlSht.Cells(i, 1).Value) = vbNullString Then
                    dblBlankRows = i
                    Exit For
                End If
            Next
            
            grdImportSheet1(intCurrentGrid).Rows = dblBlankRows
            grdImportSheet1(intCurrentGrid).Cols = dblBlankCols
            
            For intExcelRow = 1 To dblBlankRows
                For intExcelCol = 1 To 7
                    If objXlSht.Cells(intExcelRow, intExcelCol).Value <> 0 Then
                        grdImportSheet1(intCurrentGrid).TextMatrix(intExcelRow - 1, intExcelCol - 1) = objXlSht.Cells(intExcelRow, intExcelCol).Value
                    End If
                Next intExcelCol
            Next intExcelRow
        FG_AutosizeCols grdImportSheet1(intCurrentGrid), -1, -1, False
        intCurrentGrid = intCurrentGrid + 1
    Next
    Exit Sub
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyEscape Then
        End
    End If
End Sub

Private Sub Form_Load()
    blnOpenExcelObj = False
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
    If blnOpenExcelObj = True Then
        objExcel.Quit
    End If
End Sub

Public Function FG_AutosizeCols(myGrid As MSFlexGrid, _
                                Optional ByVal lFirstCol As Long = -1, _
                                Optional ByVal lLastCol As Long = -1, _
                                Optional bCheckFont As Boolean = False)
  
    Dim lCol As Long, lRow As Long, lCurCol As Long, lCurRow As Long
    Dim lCellWidth As Long, lColWidth As Long
    Dim bFontBold As Boolean
    Dim dFontSize As Double
    Dim sFontName As String
  
    If bCheckFont Then
        ' save the forms font settings
        bFontBold = Me.FontBold
        sFontName = Me.FontName
        dFontSize = Me.FontSize
    End If
  
    With myGrid
        If bCheckFont Then
            lCurRow = .Row
            lCurCol = .Col
        End If
    
        If lFirstCol = -1 Then lFirstCol = 0
        If lLastCol = -1 Then lLastCol = .Cols - 1
    
        For lCol = lFirstCol To lLastCol
            lColWidth = 0
            If bCheckFont Then .Col = lCol
            For lRow = 0 To .Rows - 1
                If bCheckFont Then
                    .Row = lRow
                    Me.FontBold = .CellFontBold
                    Me.FontName = .CellFontName
                    Me.FontSize = .CellFontSize
                End If
                lCellWidth = Me.TextWidth(.TextMatrix(lRow, lCol))
                If lCellWidth > lColWidth Then lColWidth = lCellWidth
            Next lRow
            .ColWidth(lCol) = lColWidth + Me.TextWidth("W") + 400
        Next lCol
    
        If bCheckFont Then
            .Row = lCurRow
            .Col = lCurCol
        End If
    End With
  
    If bCheckFont Then
        Me.FontBold = bFontBold
        Me.FontName = sFontName
        Me.FontSize = dFontSize
    End If
End Function

'Ended By Deepak Shitole 27/07/2011

Edited 5 Years Ago by Nick Evan: Added CODE-tags

Comments
bettter reply to recent questions than digging 7 year old threads.
This question has already been answered. Start a new discussion instead.