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

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.