MartyConnelly
martyconnelly at shaw.ca
Sun Jun 15 00:13:29 CDT 2003
Here is another way to import or export excel worksheets to and fro from
Access using ADO only
Lite on error checking.You will have to check for various tables, sheets
and files existance.
I was suprised you could write these types of SQL strings, but they work.
'sample call
'ExportSpreadSheet
"c:\excel\excelrankfunction.mdb","c:\excel\products.xls","BooksXLS","Books"
Sub ExportSpreadSheet(strAccessFile As String, strExcelFile As String, _
strAccessTable As String, strSheetName As String)
'Export Access table into named Excel worksheet
'needs ADO reference, using ADO 2.7
'Will not work if .xls file open or .xls file does not exist
Dim cnSrc As New ADODB.Connection
Dim num_copied As Long
Dim strSQL As String
cnSrc.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strAccessFile & ";"
strSQL = "SELECT * INTO [Excel 8.0;" & _
"Database=" & strExcelFile & "].[" & strSheetName & "] FROM " & _
strAccessTable
Debug.Print strSQL
cnSrc.Execute strSQL, num_copied
cnSrc.Close
MsgBox "Copied " & num_copied & " records."
End Sub
'sample call
'ImportSpreadSheet
"c:\excel\excelrankfunction.mdb","c:\excel\products.xls","BooksXLS","Books"
Sub ImportSpreadSheet(strAccessFile As String, strExcelFile As String, _
strAccessTable As String, strSheetName As String)
'Import named Excel worksheet into Access table
'needs ADO reference, using ADO 2.7
' Access table must not exist
Dim cnSrc As New ADODB.Connection
Dim num_copied As Long
Dim strSQL
cnSrc.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strAccessFile & ";"
strSQL = "SELECT * INTO " & strAccessTable & " From [Excel 8.0;" & _
"Database=" & strExcelFile & "].[" & strSheetName & "]"
Debug.Print strSQL
cnSrc.Execute strSQL, num_copied
cnSrc.Close
MsgBox "Copied " & num_copied & " records."
End Sub
MartyConnelly wrote:
> 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
>>
>>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>