• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

Reading Data from Excel in VB

To read data  from Excel and Print in VB ?
0
Meenakshibansal
Asked:
Meenakshibansal
1 Solution
 
amkarambelkarCommented:
Make User DSN (say MyDsn) with xls file and insert DataGrid and ADODC in your form Set DataSource for DataGrid as Adodc1
In ADODC1 custom properties
Refer to MyDsn and write query as
Select * from "Sheet1$"
now if you run the program you can find Datagrid with Data from Excel Sheet
0
 
BassTeQCommented:
Here is some code which you may be able to use for an example, I used this in a program of mine.
Just change the filename, sheetname accordingly.
The XlsRS.Fields refers to the heading in each column, so they will need to be changed accordingly also.
Good luck, hope this helps!

Dim myFileName As String
Dim XlsDB As Database
Dim XlsRS As Recordset
Dim filepath As String
Dim sheetname As String
 
 filepath = "test.xls"
 sheetname = "Sheet1$"
 Set XlsDB = OpenDatabase(filepath, False, False, "Excel 8.0;HDR=yes;")
 Set XlsRS = XlsDB.OpenRecordset(sheetname)

 XlsRS.MoveFirst
 Screen.MousePointer = 11
 While ((XlsRS.Fields("first name") <> "")
   
    txtMemID.Text = "?"
    txtGiven = XlsRS.Fields("first name")
    txtSurname.Text = XlsRS.Fields("SURNAME")
    txtHomePhone.Text = " "
   
    If XlsRS.Fields("mphone") <> "" Then
        txtMobile.Text = XlsRS.Fields("mphone")
    Else
        txtMobile.Text = " "
    End If
   
    If XlsRS.Fields("date of birth") <> "" Then
       txtDOB.Text = XlsRS.Fields("date of birth")
    Else
       txtDOB.Text = "00/00/0000"
    End If
   
    txtEmail.Text = XlsRS.Fields("email")
    txtStreet.Text = XlsRS.Fields("Street Details")
   
    XlsRS.MoveNext
 Wend

 Screen.MousePointer = 0
 XlsDB.Close




0
 
venkatravi_78Commented:
put a mshflexgrid, 1 combo box,1 command button, add references to the ado objects and excel objects and 1 text box, 1 common dialog control

Private Sub Command1_Click()
CommonDialog1.Filter = "Excel Documents (*.xls)|*.xls" '|All files (*.*)|*.*"
CommonDialog1.DialogTitle = "RAVI"
On Error Resume Next
CommonDialog1.ShowOpen

On Error Resume Next
CommonDialog1.CancelError = True

If Err.Number = cdlCancel Then
Err.Clear
End If

If CommonDialog1.FileName <> "" Then
Text1.Text = CommonDialog1.FileName & ";"
Txt1 = CommonDialog1.FileName
End If

If CommonDialog1.FileName <> "" Then
Set WB = XL.Workbooks.Open(Txt1)

Combo1.Clear

For Each SH In WB.Worksheets
    Combo1.AddItem SH.Name '& "$"
Next
End If

If Text1.Text <> "" Then
Combo1.SetFocus
End If

Set XL = Nothing
Set WB = Nothing
Set SH = Nothing

End Sub

Private Sub Combo1_lostfocus()
If Combo1.Text <> "" Then
MSHFlexGrid1.Clear
'On Error Resume Next

objc.Provider = "microsoft.jet.oledb.4.0"
objc.ConnectionString = _
        "Data Source= " & Text1.Text & _
        "Extended Properties=Excel 8.0;"

objc.CursorLocation = adUseClient
objc.Open

objr.Open "select * from" & "[" & Combo1.Text & "$" & "]""", objc, adOpenDynamic, adLockOptimistic
'[sheet1$] ", objc, adOpenDynamic, adLockOptimistic"

Set MSHFlexGrid1.DataSource = objr

With MSHFlexGrid1
    For z = 0 To .Cols - 1
        .Row = 0
        .Col = z
        .CellFontBold = True
        .CellFontName = "Verdana"
        .CellAlignment = flexAlignCenterCenter
       
    Next
End With

MSHFlexGrid1.Rows - 1

Set objc = Nothing
Set objr = Nothing
Set XL = Nothing
Set WB = Nothing
Set SH = Nothing
End If
End Sub
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
venkatravi_78Commented:
declare the object like this.

Dim objc As New ADODB.Connection
Dim objr As New ADODB.Recordset

Dim XL As New Excel.Application
Dim SH As Excel.Worksheet
Dim WB As Excel.Workbook
0
 
venkatravi_78Commented:
declare the object like this.

Dim objc As New ADODB.Connection
Dim objr As New ADODB.Recordset

Dim XL As New Excel.Application
Dim SH As Excel.Worksheet
Dim WB As Excel.Workbook
0
 
CleanupPingCommented:
Meenakshibansal:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
0
 
DanRollinsCommented:
Meenakshibansal, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Save as PAQ -- No Refund.

DanRollins -- EE database cleanup volunteer
0
 
Computer101Commented:
PAQed - no points refunded (of 50)

Computer101
E-E Admin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now