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?
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???
Is cell 1, 1 empty?
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 textboxactually 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.ValueDan
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.Quitand 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 Functionand I called this function by this button click
Private Sub CommandButton1_Click()
Call show1
End SubSorry,
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.
Yep. Sorry,
I should check which forum I'm in before trying to be clever.
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 = nothingI 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
NextBut I have run time error '9' : Subscript out of range
Would you please help me?
Thanks.
Attach your project.
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.
And the excel document.
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
NextThanks a lot for those who helped me
New cannot be used on this interface???? Whats gone wrong?
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?
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
tnx
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
hi can you explane how to open exel sheet
by Visul Basi b ctick a butten
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.