Open Excel file from Visual Basic

Question

dtbn 0 Newbie Poster

19 Years Ago

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

visual-basic-6

13 Contributors
30 Replies
14K Views
10 Years Discussion Span
Latest Post 9 Years Ago Latest Post by ice23

All 30 Replies

seagull 0 Newbie Poster

18 Years Ago

Yep. Sorry,

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

harry.net 0 Newbie Poster

18 Years Ago

And the excel document.

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

thanks

Comatose 290 Taboo Programmer

18 Years Ago

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

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

seagull 0 Newbie Poster

18 Years Ago

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.

harry.net 0 Newbie Poster

18 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

mnemtsas 3 Posting Whiz in Training · Answer 1 · 2004-08-16T05:03:01+00:00

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,

manal 27 Junior Poster · Answer 2 · 2006-03-09T18:59:03+00:00

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

Comatose 290 Taboo Programmer Team Colleague · Answer 3 · 2006-03-10T04:33:22+00:00

Comatose 290 Taboo Programmer

18 Years Ago

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

manal 27 Junior Poster · Answer 4 · 2006-03-10T05:50:52+00:00

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

Comatose 290 Taboo Programmer Team Colleague · Answer 5 · 2006-03-10T05:54:19+00:00

Comatose 290 Taboo Programmer

18 Years Ago

Is cell 1, 1 empty?

manal 27 Junior Poster · Answer 6 · 2006-03-10T06:12:40+00:00

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

seagull 0 Newbie Poster · Answer 7 · 2006-03-22T22:58:41+00:00

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

harry.net 0 Newbie Poster · Answer 8 · 2006-03-29T13:44:07+00:00

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

seagull 0 Newbie Poster · Answer 9 · 2006-03-29T14:51:21+00:00

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

seagull 0 Newbie Poster · Answer 10 · 2006-03-29T16:32:54+00:00

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

Comatose 290 Taboo Programmer Team Colleague · Answer 11 · 2006-03-29T17:39:39+00:00

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.

harry.net 0 Newbie Poster · Answer 12 · 2006-03-30T15:10:27+00:00

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)

Comatose 290 Taboo Programmer Team Colleague · Answer 13 · 2006-03-30T17:24:25+00:00

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

harry.net 0 Newbie Poster · Answer 14 · 2006-04-01T10:37:10+00:00

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.

Comatose 290 Taboo Programmer Team Colleague · Answer 15 · 2006-04-01T17:26:50+00:00

Comatose 290 Taboo Programmer

18 Years Ago

Attach your project.

harry.net 0 Newbie Poster · Answer 16 · 2006-04-02T12:25:01+00:00

harry.net 0 Newbie Poster

18 Years Ago

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.

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

New_Folder.zip (4.26 KB)

Comatose 290 Taboo Programmer Team Colleague · Answer 17 · 2006-04-02T12:29:06+00:00

Comatose 290 Taboo Programmer

18 Years Ago

And the excel document.

dawson345 0 Newbie Poster · Answer 18 · 2009-12-10T21:10:38+00:00

New cannot be used on this interface???? Whats gone wrong?

amirtmn 0 Newbie Poster · Answer 19 · 2009-12-16T15:59:17+00:00

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)

Cool if AI 0 Newbie Poster · Answer 20 · 2010-02-16T19:56:15+00:00

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

nathan_jun2 -2 Newbie Poster · Answer 21 · 2010-02-18T08:26:43+00:00

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

Cool if AI 0 Newbie Poster · Answer 22 · 2010-02-19T02:23:12+00:00

Cool if AI 0 Newbie Poster

14 Years Ago

tnx

Deepak Shitole 0 Newbie Poster · Answer 23 · 2011-07-28T12:27:17+00:00

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

bettter reply to recent questions than digging 7 year old threads.

Jeganathan_1 0 Newbie Poster · Answer 24 · 2013-07-29T07:44:03+00:00

hi can you explane how to open exel sheet
by Visul Basi b ctick a butten

Open Excel file from Visual Basic

Recommended Answers Collapse Answers

All 30 Replies

Recommended Answers