[AccessD] Phun with Excel / automation

You can get at it this way using ADO 2.7 with something like this

Dim oConn As New ADODB.Connection
Dim Ors As ADODB.Recordset
Dim c as long
Set Ors = New ADODB.Recordset

With oConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties").Value = "Excel 8.0"
    .Open "C:\Excel\ExcelADO\results\Products.xls"
    .CursorLocation = adUseClient 'need otherwise recordcount=-1
    '....
Ors.Open "Select * from [Products]", oConn, adOpenStatic 'Sheet1$ or 
Products sheetname

 For c = 0 To Ors.RecordCount - 1
'the field order of columns does not always follow the sheet order
 Debug.Print Ors.Fields(0).Value; Ors.Fields(0).Name
 Debug.Print Ors.Fields(1).Value
  Ors.MoveNext
'just stuff these values in a table
 Next
 End With
  oConn.Close

------------------ or look at the schema of  the sheet for sheet names 
and field names and types

Dim oConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rstSch As ADODB.Recordset
Dim rsC As ADODB.Recordset
Dim Ors As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer

With oConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties").Value = "Excel 8.0"
    .Open "C:\Excel\ExcelADO\results\Products.xls"
    .CursorLocation = adUseClient 'need otherwise recordcount=-1
    '....
    'Me.cmbSheetCombo.RowSource = rst
   
    Set rstSch = oConn.OpenSchema(adSchemaTables)
intTblCnt = rstSch.RecordCount
intTblFlds = rstSch.Fields.Count
MsgBox intTblCnt & "*?" & intTblFlds
'List1.AddItem "Tables:  " & intTblCnt
'List1.AddItem "--------------------"
Dim strList As String
strList = ""
For t = 1 To intTblCnt
    strTbl = rstSch.Fields("TABLE_NAME").Value
   Debug.Print vbTab & "Table #" & t & ":    " & strTbl
   Debug.Print vbTab & "--------------------"
   MsgBox strTbl
   strList = strList & strTbl & ";"
    For f = 0 To intTblFlds - 1
        Debug.Print vbTab & rstSch.Fields(f).Name & _
vbTab & rstSch.Fields(f).Value
    Next
   ' List1.AddItem "--------------------"
    Set rsC = oConn.OpenSchema(adSchemaColumns, Array(Empty, Empty, 
strTbl, Empty))
    intColCnt = rsC.RecordCount
    intColFlds = rsC.Fields.Count
    For c = 1 To intColCnt
        strCol = rsC.Fields("COLUMN_NAME").Value
     Debug.Print vbTab & vbTab & "Column #" & c & ": " & strCol
     Debug.Print vbTab & vbTab & "--------------------"
        For f = 0 To intColFlds - 1
 '         Debug.Print vbTab & vbTab & rsC.Fields(f).Name & _
 '                   vbTab & rsC.Fields(f).Value
        Next
        'List1.AddItem vbTab & vbTab & "--------------------"
        rsC.MoveNext
        Next
        rsC.Close
        'List1.AddItem "--------------------"
       
Next

End With
 oConn.Close


Don Elliker wrote:

> Then I am back to the "There is no object in this control" error 
> reported earlier. This comes up when attempting to import using the 
> wizard....I just AM screwed am I not? Well , I have the no-help desk 
> personnel coming for a visit later
> _D
>
>
>
>
> "Things are only free to the extent that you don't pay for them".-Don 
> Elliker
>  
> >Once the Import Spreadsheet Wizard starts you should see two radio 
> button
> >(Show Worksheets and Show Named Ranges). Make sure the Show 
> Worksheets >one is checked and then you should see a list of the 
> worksheets within >your file. Then just select the one you want to 
> import and keep answering >the wizard's questions. Repeat the process 
> for each worksheet to import. > > > > >"Don Elliker" >Sent by: 
> accessd-bounces at databaseadvisors.com >06/12/2003 10:46 AM >Please 
> respond to accessd > > > To: accessd at databaseadvisors.com > cc: > 
> Subject: [AccessD] Phun with Excel / automation > > >Folks, >I am 
> trying to import ,on a one time basis , the data from an excel 
> >workbook into Access XP. I can't use the 'import data' option, it 
> only >gets me the first worksheet (or am I doing it wrong?). I can get 
> to the >workbook in code easily enough but there seems no easy way to 
> get the >individual worksheets imported into separate tables. I tried 
> using the >'transferspreadsheet' docmd thingie, but it gets hung up on 
> the file name. >I cannot just reference the worksheet object- it 
> doesn't like that. I >thought it was working out too easily...#-( - 
> Sooo...does anyone have >this experience...this is a one-off I just 
> want the data in tables. >"Things are only free to the extent that you 
> don't pay for them".-Don >Elliker > >Add photos to your e-mail with 
> MSN 8. Get 2 months 
> FREE*._______________________________________________ >AccessD mailing 
> list >AccessD at databaseadvisors.com 
> >http://databaseadvisors.com/mailman/listinfo/accessd >Website: 
> http://www.databaseadvisors.com > > 
> >_______________________________________________ >AccessD mailing list 
> >AccessD at databaseadvisors.com 
> >http://databaseadvisors.com/mailman/listinfo/accessd >Website: 
> http://www.databaseadvisors.com
>
> ------------------------------------------------------------------------
> MSN 8 helps ELIMINATE E-MAIL VIRUSES. 
> <http://g.msn.com/8HMCENUS/2752??PS=> Get 2 months FREE*.
>
>------------------------------------------------------------------------
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>  
>