excel and vb

hi,

i am new to vb and was figuring how to get my data in excel worksheet to vb. can anyone help me? thanks in advance.

Hi,

I have written some function to read data from the excel file ,


'FileName should be physical path like "c:\temp\test.xls"
Private Sub ReadXLSFileFormat(FileName As String)

Dim objInputXLS As Object
Dim ObjInputWorkBook As Object
Dim ObjInputSheet As Object

Set objInputXLS = CreateObject("Excel.Application")

Set ObjInputWorkBook = objInputXLS.Workbooks.Open(FileName)
Set ObjInputSheet = ObjInputWorkBook.Worksheets(1)

MsgBox ObjInputSheet.Cells(1,1) ' It will give the data in the 1st row 1st column data in the excel file.

objInputXLS.ActiveWorkbook.Close True, FileName
Set ObjInputSheet = Nothing
Set ObjInputWorkBook = Nothing
Set objInputXLS = Nothing

End Sub


There is another way also there to read excel file using ADOBD.Connection and ADOBD.RecordSet if need that , u ask me again
i can provide u.....


Cheers,

Satish Kumar J...

hi,

thanks.. may i know where did i write the code at? what are the neccessary steps that i need to do? i appreciate your help.

Hi,

Create New Vb EXE project and draw a command button on the form and paste the following code in the form1's code place


Private Sub Command1_Click()
'File shoud be there in the specified location
ReadXLSFileFormat ("c:\test.xls")
End Sub

Private Sub ReadXLSFileFormat(FileName As String)
Dim objInputXLS As Object
Dim ObjInputWorkBook As Object
Dim ObjInputSheet As Object
Set objInputXLS = CreateObject("Excel.Application")
Set ObjInputWorkBook = objInputXLS.Workbooks.Open(FileName)
Set ObjInputSheet = ObjInputWorkBook.Worksheets(1)
MsgBox ObjInputSheet.Cells(1, 1) ' It will give the data in the 1st row 1st column data in the excel file.
objInputXLS.ActiveWorkbook.Close True, FileName
Set ObjInputSheet = Nothing
Set ObjInputWorkBook = Nothing
Set objInputXLS = Nothing
End Sub


Regards,
Satish Kumar J...

thanks, will it be possible to load the whole excel worksheet into vb? sorry for your time. Thanks.

What version of VB? What do you mean "load"? Load into what?

cyq, spend a little time with some basic tutorials. www.mvps.org is a good starting place.

HI, you can also try like this

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

'fromload
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
con.Open

rs.Open "Select * from [sheet1$]", con


Private Sub Command1_Click()
MsgBox rs.GetString

End sub

Hi,
My situation is I will generate a report from vb in excel.user update the report and once he clicks on update I need to upadte this data into DB.Here neither user nor program(developer) will save the excel file.Application simply opens EXCEl application in such case how to read data from the opend sheet using ADODB.

Hi,
My situation is I will generate a report from vb in excel.user update the report and once he clicks on update I need to upadte this data into DB.Here neither user nor program(developer) will save the excel file.Application simply opens EXCEl application in such case how to read data from the opend sheet using ADODB.