Import / Export from FlexGrid to Excel

How do I export all records and fields from a flexgrid using ADO code to an excel spreadsheet? How do I import them as well?

The fields I have are:
ID
Name
Age
City
State


Takamine334Asked:
Who is Participating?
 
PaulHewsCommented:
Set up an excel sheet with those column names across the top of the first row.  Note that this file will be the file name in the connection string below.  Now you should be able to use this code:

Option Explicit

Private Sub Command1_Click()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Mode = adModeReadWrite
    With cn
        .Provider = "MSDASQL"
        .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
    "DBQ=C:\Temp\TestADO.xls; ReadOnly=False;"
        .Open
    End With
   
   
    Dim rs As Recordset
    Set rs = New Recordset
   
    rs.Open "SELECT * FROM [Sheet1$]", cn, adOpenKeyset, adLockOptimistic

   
    rs.AddNew
    rs("ID") = 2
    rs("Name") = "Jimmy"
    rs("Age") = 20
    rs("City") = "Dallas"
    rs("State") = "TX"
    rs.Update
    rs.Close
    cn.Close
   
    Set rs = Nothing
    Set cn = Nothing


End Sub

I got the error about collating sequence the first time I ran it, but not subsequently.  According to MS, that's a bug.
http://support.microsoft.com/kb/246167/EN-US/
0
 
PaulHewsCommented:
Here's the same sample with the flexgrid handling added:

Option Explicit

Private Sub Command1_Click()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Mode = adModeReadWrite
    With cn
        .Provider = "MSDASQL"
        .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
    "DBQ=C:\Temp\TestADO.xls; ReadOnly=False;"
        .Open
    End With
   
   
    Dim rs As Recordset
    Set rs = New Recordset
   
    rs.Open "SELECT * FROM [Sheet1$]", cn, adOpenKeyset, adLockOptimistic
   
    Dim i As Integer
    With MSFlexGrid1
        For i = 1 To .Rows - 1
   
            rs.AddNew
            rs("ID") = CLng(.TextMatrix(i, 0))
            rs("Name") = .TextMatrix(i, 1)
            rs("Age") = CLng(.TextMatrix(i, 2))
            rs("City") = .TextMatrix(i, 3)
            rs("State") = .TextMatrix(i, 4)
            rs.Update
        Next
    End With
    rs.Close
    cn.Close
   
    Set rs = Nothing
    Set cn = Nothing


End Sub

Private Sub Form_Load()
    With MSFlexGrid1
        .Cols = 5
        .FixedCols = 0
        .FixedRows = 0
        .Rows = 0
        .AddItem "ID" & vbTab & "Name" & vbTab & "Age" & vbTab & "City" & vbTab & "State"
       
        .AddItem "1" & vbTab & "Mike" & vbTab & "25" & vbTab & "New York" & vbTab & "NY"
        .AddItem "2" & vbTab & "Timmy" & vbTab & "21" & vbTab & "New York" & vbTab & "NY"
        .FixedRows = 1
   
    End With
End Sub
0
 
JunHaanCommented:
Here are two functions that you can use, complete with error handling.
ImportFromExcel() -- Pass it a path to your source excel file and the target flexgrid. This function will fill it up
ExportToExcel() -- Pass it a path to another existing but empty excel file and the source flexgrid. This function will dump whatever is in the source flexgrid to the target excel file.
---------------------------------
Create an MSFlexGrid and two buttons.
Code is as below:
---------------------------------

Private Sub Command1_Click()
    Call ImportFromExcel("C:\testimport.xls", MSFlexGrid1, "SELECT * FROM [Sheet1$]")
End Sub

Private Sub Command2_Click()
    Call ExportToExcel("C:\testexport.xls", MSFlexGrid1, "[Sheet1$]")
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function ImportFromExcel(strSrcPath As String, flxDest As MSFlexGrid, _
                                strSQL As String)
' Usage: ImportFromExcel("C:\testimport.xls", MSFlexGrid1, "SELECT * FROM [Sheet1$]")
                               
On Error GoTo ERR_ROUTINE

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim strRow As String
   
    ' Setup Connection Object
    Set cn = New ADODB.Connection
    With cn
        .Provider = "MSDASQL" ' Microsoft OLE DB Provider for ODBC Drivers
        .ConnectionString = _
            "Driver={Microsoft Excel Driver (*.xls)};" & _
            "DBQ=" & strSrcPath & ";" & _
            "FirstRowHasNames=1;"
        .Open
    End With
   
    Set rs = cn.Execute(strSQL)
       
    ' Reset flexgrid
    flxDest.Clear
    flxDest.Rows = 0
    flxDest.Cols = 0
           
    ' Set up the headers for the flexgrid
    If Not rs.EOF Then
        strRow = ""
        ' Iterate fields
        For i = 0 To rs.Fields.Count - 1
            strRow = strRow & rs.Fields(i).Name & vbTab
        Next
        flxDest.Cols = rs.Fields.Count
        flxDest.AddItem strRow, 0
    End If
   
    ' Fill up the flexgrid
    While Not rs.EOF
        strRow = ""
        ' Iterate fields
        For i = 0 To rs.Fields.Count - 1
            strRow = strRow & rs.Fields(i) & vbTab
        Next
       
        ' Add row to flexgrid
        flxDest.AddItem strRow
       
        rs.MoveNext
    Wend
   
    flxDest.FixedRows = 1 ' Set header (fixed row)
   
RESUME_ROUTINE:
    If Not rs Is Nothing Then If rs.State = adStateOpen Then rs.Close
    If Not cn Is Nothing Then If cn.State = adStateOpen Then cn.Close
    Set rs = Nothing
    Set cn = Nothing
   
Exit Function
   
ERR_ROUTINE:
    MsgBox "There was an undefined error in ImportFromExcel: " _
                        & Err.Description
    Resume RESUME_ROUTINE
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function ExportToExcel(strDestPath As String, flxSrc As MSFlexGrid, _
                            strSheet As String)
' Usage: ExportToExcel("C:\testexport.xls", MSFlexGrid1, "[Sheet1$]")

On Error GoTo ERR_ROUTINE

    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim i As Integer, j As Integer
    Dim strRow As String
   
    ' Setup Connection Object
    Set cn = New ADODB.Connection
    With cn
        .Provider = "MSDASQL" ' Microsoft OLE DB Provider for ODBC Drivers
        .ConnectionString = _
            "Driver={Microsoft Excel Driver (*.xls)};" & _
            "DBQ=" & strDestPath & ";" & _
            "ReadOnly=False;"
        .Open
    End With
   
    ' Build CREATE TABLE columns list
    strRow = ""
    For j = 0 To flxSrc.Cols - 1
        flxSrc.Row = 0
        flxSrc.Col = j
        strRow = strRow & "[" & flxSrc.TextMatrix(0, j) & "]" & " TEXT,"
    Next j
    strRow = Left(strRow, Len(strRow) - 1) ' Remove last comma
   
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cn
   
    cmd.CommandText = "CREATE TABLE " & strSheet & "(" & strRow & ")"
    cmd.Execute ' Create the table
   
    ' Iterate through flexgrid and insert
    For i = 1 To flxSrc.Rows - 1
        strRow = ""
       
        For j = 0 To flxSrc.Cols - 1
            flxSrc.Row = i
            flxSrc.Col = j
             strRow = strRow & "'" & flxSrc.TextMatrix(i, j) & "'" & ","
        Next j
       
        strRow = Left(strRow, Len(strRow) - 1) ' Remove last comma
       
        cmd.CommandText = "INSERT INTO " & strSheet & " VALUES (" & strRow & ")"
        cmd.Execute
       
    Next i
   
RESUME_ROUTINE:
    If Not cn Is Nothing Then If cn.State = adStateOpen Then cn.Close
   
    Set cmd = Nothing
    Set cn = Nothing
   
Exit Function
   
ERR_ROUTINE:
    MsgBox "There was an undefined error in ExportToExcel: " _
                        & Err.Description
    Resume RESUME_ROUTINE
End Function






0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Takamine334Author Commented:
JunHaan - I get:

there was an undefined error in ExportToExcel: [Microsoft][ODBC Excel Driver]
'Sheet$1' is not a valid name. Make sure that it doesn not include invalid
characters or punctuation and that it is not too long.
0
 
Takamine334Author Commented:
Hmmm. I took out "$" and it works.
0
 
JunHaanCommented:
Oops it didn't like the "$".
Do take care when manipulating Excel using ADO though as it is known to be buggy, especially with older versions of MDAC.

How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q257/8/19.asp&NoWebContent=1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.