excel and vb - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

excel and vb

anto12345anto12345 Posts: 1Member
[b][red]This message was edited by anto12345 at 2006-3-15 13:39:15[/red][/b][hr]
Hi guys
I just started using vb and i am having trouble transfering data from
a existing excel file.I can open the file in vb but i cant transfer the
columns, i want, to listboxes in vb .
Any help would be great
thanks



Comments

  • DrMartenDrMarten Posts: 748Member
    : [b][red]This message was edited by anto12345 at 2006-3-15 13:39:15[/red][/b][hr]
    : Hi guys
    : I just started using vb and i am having trouble transfering data from
    : a existing excel file.I can open the file in vb but i cant transfer the
    : columns, i want, to listboxes in vb .
    : Any help would be great
    : thanks

    ========================================================================
    Hi,

    You could save the EXCEL file as a CSV (Comma-Separated-Value) file.

    This is a text file like>>

    1,2,3
    4,5,6
    7,8,9

    Read the file in a line at a time and use the SPLIT function to separate the various values off.

    If you want an example of the SPLIT function, please say so.


    Regards,

    Dr M.

  • lionblionb Posts: 1,688Member ✭✭
    [b][red]This message was edited by lionb at 2006-3-16 5:56:41[/red][/b][hr]
    : [b][red]This message was edited by anto12345 at 2006-3-15 13:39:15[/red][/b][hr]
    : Hi guys
    : I just started using vb and i am having trouble transfering data from
    : a existing excel file.I can open the file in vb but i cant transfer the
    : columns, i want, to listboxes in vb .
    : Any help would be great
    : thanks
    :
    IMO the best way to do that is using ADO object. To do that go to Project - Reference and select Microsoft ActiveX Data Objects 2.6 Library. If you have 2.7 version or later, select it.
    Then write code like that
    [code]
    Private Sub Command1_Click()
    Dim cnConnection As New ADODB.Connection
    Dim sConnect As String
    Dim strExcelFile As String
    Dim rsTemp As New ADODB.Recordset
    'Dim iCount as Intefger

    strExcelFile = "C:MyExcel.xls" 'directory where yours Excel file located


    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "
    strExcelFile & ";Extended Properties=Excel 8.0;"


    rsTemp.CursorLocation = adUseClient

    strExcelSQL = "select * from [Test$]" '[blue][Test$] name of Sheet [/blue]

    rsTemp.Open strExcelSQL, cnConnection

    Do Until rsTemp.EOF

    Debug.Print rsTemp.Fields(1).Name
    Debug.Print rsTemp.Fields(1).Value
    ' [blue]Fiellds(1) means Column 1. If you know Column name you can use it like rsTemp.Fields("FirstName").Name/Value
    'You must modify following line of code accorditng to your needs[/blue]
    List1. AddItem rsTemp.Fields(1).Name & " " & rsTemp.Fields(1).Value

    rsTemp.MoveNext

    Loop

    Set rsTemp = Nothing

    cnConnection.Close

    Set cnConnection=Nothing
    End Sub
    [/code]



Sign In or Register to comment.