Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Import / Export from FlexGrid to Excel

Posted on 2007-07-21
6
Medium Priority
?
4,317 Views
Last Modified: 2012-05-05
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


0
Comment
Question by:Takamine334
  • 2
  • 2
  • 2
6 Comments
 
LVL 38

Accepted Solution

by:
PaulHews earned 2000 total points
ID: 19542643
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
 
LVL 38

Assisted Solution

by:PaulHews
PaulHews earned 2000 total points
ID: 19542674
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
 
LVL 2

Expert Comment

by:JunHaan
ID: 19546231
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
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!

 

Author Comment

by:Takamine334
ID: 19551499
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
 

Author Comment

by:Takamine334
ID: 19551524
Hmmm. I took out "$" and it works.
0
 
LVL 2

Expert Comment

by:JunHaan
ID: 19553062
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

Featured Post

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!

Question has a verified solution.

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

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In this tutorial viewers will learn how to style a corner ribbon overlay for an image using CSS Create a new class by typing ".Ribbon":  Define the class' "display:" as "inline-block": Define its "position:" as "relative": Define its "overflow:" as …
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses
Course of the Month20 days, 21 hours left to enroll

810 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