Working with ADO Recordset & Excel

[jgelpi16]

I am sure this is quite simple, but I have not clue as to how to do it...I have an Excel spreadsheet that has a new column of data added to it each month. I would like to go grab the last column of data and then dump it into a table. The spreadsheet is structured in a way that I can't really just link to it. Any help is much appreciated!

[ajetrumpet]

i knew I had written this:

Function GetSheetNamesAdo(dSource As String)

On Error GoTo Cleanup

'******************************************************************************
'_____________________________________________________________________________*
'                                                                             |
'THIS FUNCTION ASSUMES THAT YOU ARE USING MS OFFICE 8.0 OR LATER.  THIS       |
'WAS TESTED USING AN 8.0 VERSION (2002) SPREADSHEET IN OFFICE 2007.  LATER    |
'FILE EXTENSIONS MAY REQUIRE AN UPDATED ISAM DRIVER AS YOU MAY ENCOUNTER      |
'AN ERROR.                                                                    |
'_____________________________________________________________________________|
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 3/26/2010                                                              *
'Purpose: To print a list of worksheet names from an Excel workbook using ADO.*
'                                                                             *
'Arguments:                                                                   *
'dSource > Full path of the Excel file to read.                               *
'                                                                             *
'******************************************************************************

Dim cn As ADODB.Connection
Dim cnRs As ADODB.Recordset
Set cn = New ADODB.Connection
'Set cnRs = New ADODB.Recordset

   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & dSource & ";" & _
                          "Extended Properties=Excel 8.0;"
      .Open
   End With

      'cnRs.Open "SELECT * FROM [Sheet1$]", cn
      Set cnRs = cn.OpenSchema(adSchemaTables)
      
         With cnRs
            Do Until .EOF
               If InStr(.Fields("Table_Name"), "$") > 0 Then
                  Debug.Print .Fields("Table_Name")
               End If
                  .MoveNext
                  
            Loop
         End With

Cleanup:
   cnRs.Close
   cn.Close
   Set cnRs = Nothing
   Set cn = Nothing

      If err.Number <> 0 Then
         MsgBox (err.Description)
      End If

End Function '//LL

you can adopt that function to get what you need, I would assume. Also, named ranges in Excel, when accessed through an ADO connection, are considered tables. Hence, they will be in the tables collection, with the names of the ranges appearing as table names. Actually, now that I look at it though, I think I changed it to iterate through a sheet in the source spreadsheet. Either way though, I'm sure you get the idea!

[jgelpi16]

Wow....Wonderful! Thank you! I'll let you know how it goes.

[jgelpi16]

I fear I may need a little more direction on the named range piece. I have created two named ranges and what I want to do is import the last column in the named range. I thought I would put the named range here:

cnRs.Open "SELECT * FROM [NamedRange]", cn

However, that did not work. It appears to go into an endless loop and when I close the spreadsheet to break the code I get a Run-time error stating the Jet database engine could not find the object. Maybe my syntax is off in referencing the named range? I also though I should put the named range in this piece of code:

         With cnRs
            
            Do Until .EOF
               If InStr(.Fields("NamedRange"), "$") > 0 Then
                  Debug.Print .Fields("NamedRange")
               End If
                  .MoveNext
                   
            Loop
         End With

However, I get another run-time error "Item cannot be found". Thanks in advance for your help.

[ajetrumpet]

jg,

this is marked solved but you're asking questions. which is it, sir?

[jgelpi16]

Yea....about that. I got a little ahead of myself on that one.

[ajetrumpet]

remember, the dollar sign indicates actual sheets. if you want to manipulate ranges, leave it out of the sql statement. for instance, if I have a named range in my original example (in sheet1) and it's called "myrange", it has one column in it, and it spans 50 rows....

Lets say I want to print out the values in all 50 of those rows in that one column. Here's what I would write to do that (code is partial of function):

Dim cn As ADODB.Connection
Dim cnRs As ADODB.Recordset
Set cn = New ADODB.Connection
Set cnRs = New ADODB.Recordset

   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & dSource & ";" & _
                          "Extended Properties=Excel 8.0;"
      .Open
   End With

      cnRs.Open "SELECT * FROM [myrange]", cn
      
         With cnRs
            Do Until .EOF
               Debug.Print .Fields(0)
                  .MoveNext
            Loop
         End With

make sense? With ADO, I really think you have to iterate in order to import. I don't think you can import with one task like the import wizard. but I could be wrong.

[jgelpi16]

Thanks, it does make sense. I'll keep working on it.