?
Solved

Reading Data from Excel in VB

Posted on 2003-02-24
8
Medium Priority
?
198 Views
Last Modified: 2010-04-07
To read data  from Excel and Print in VB ?
0
Comment
Question by:Meenakshibansal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 1

Expert Comment

by:amkarambelkar
ID: 8014145
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
 
LVL 2

Expert Comment

by:BassTeQ
ID: 8014168
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
 
LVL 2

Expert Comment

by:venkatravi_78
ID: 8014554
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
Industry Leaders: 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!

 
LVL 2

Expert Comment

by:venkatravi_78
ID: 8014560
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
 
LVL 2

Expert Comment

by:venkatravi_78
ID: 8014578
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
 

Expert Comment

by:CleanupPing
ID: 8901167
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 8972720
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 9667949
PAQed - no points refunded (of 50)

Computer101
E-E Admin
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month7 days, 20 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question