Drew Wutka
DWUTKA at Marlow.com
Thu Jul 10 18:38:36 CDT 2008
Thanks for digging that up. I was coming in remotely at lunch when I
replied to this thread, and I had that email archived on my machine at
work, too lazy to remote into my machine to dig it up. ;)
Drew
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl
Collins
Sent: Thursday, July 10, 2008 6:05 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Pulling all sheets from a spreadsheet
Mark,
The thread was cunningly hidden under "Re: [AccessD] Hide datasheet view
of table when appending"
I kept a copy of the original email as it was something I would
definately be able to utilise.
' -------------- COPIED EMAIL FOLLOWS --------------------
Ok, well if you want to try the recordset method, here's an example.
I've commented the important parts:
Function ImportExcelData()
Dim XLcnn As ADODB.Connection
Dim XLrs As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim strSheetName As String
Dim i As Long
'Create Excel ADO Connection object
Set XLcnn = New ADODB.Connection
XLcnn.Provider = "Microsoft.Jet.OLEDB.4.0"
'Define Connection Object to connect to excel
'Note, HDR=No will not use the first fields as field names
XLcnn.Properties("Extended Properties") = "Excel 8.0;HDR=Yes"
'Open the excel file
XLcnn.Open "E:\tblRequests.xls"
'Get the list of tables names (sheet names, with a $)
Set rs = XLcnn.OpenSchema(adSchemaTables)
'This line gets the first table/sheet
rs.MoveFirst
strSheetName = rs.Fields("TABLE_NAME").Value
rs.Close
'Create and open the Access table recordset
Set rs = New ADODB.Recordset
rs.Open "tblRequests", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTableDirect
'Create and open the Excel recordset
Set XLrs = New ADODB.Recordset
XLrs.Open strSheetName, XLcnn, adOpenKeyset, adLockReadOnly,
adCmdTableDirect
If XLrs.EOF = False Then XLrs.MoveFirst
Do Until XLrs.EOF = True
'We are adding a new record for each Excel record.
rs.AddNew
For i = 0 To XLrs.Fields.Count - 1
'This example uses an excel file and table with the same field
names
'other methods could be used to match the fields
'We are also checking for nulls before we add data to the field
If Not IsNull(XLrs.Fields(i).Value) Then
rs.Fields(XLrs.Fields(i).Name).Value = XLrs.Fields(i)
End If
Next i
rs.Update
XLrs.MoveNext
Loop
'close everything up
rs.Close
Set rs = Nothing
XLrs.Close
Set XLrs = Nothing
XLcnn.Close
Set XLcnn = Nothing
MsgBox "Done"
End Function
There are several advantages to this method. One, Excel is never
opening. It's all done with Jet's 'understanding' of Excel. Two, it's
fast. A few years ago, I had to create a routine that automatically
imported several spreadsheets into a network database. Using Automation
took several minutes to import a few hundred. Using recordsets, it ran
several hundred in a few seconds. I'm sure TransferSpreadsheet is
pretty fast too. Three, unlike TransferSpreadsheet, you have complete
control at the single field level to perform any kind of logic you might
need (verifying data, etc). Four, though this probably won't apply, but
you don't have to have Access OR Excel installed on a machine to run
this kind of code (though my example is using currentproject.connection,
because I wrote it in an Access .mdb. Put this code in VB, and a
non-Office program can copy data from an .xls file to an .mdb file,
without having office installed).
Another advantage is that I believed you mentioned that there could be
multiple spreadsheets, and a variable number of columns in each. Not
sure what you are doing to determine these variables, using
TransferSpreadsheet, but with the example above, you should be able to
see that you can get how many sheets are in the spreadsheet, and how
many fields/columns there are.
Drew
' ---------- END OF COPY
regards
darryl.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mark Porter
Sent: Friday, 11 July 2008 6:03 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Pulling all sheets from a spreadsheet
I'm checking the archives but not having any luck. I'm probably looking
under the wrong keywords.
What was the subject line?
Mark Porter
Sr. Technologist
Nana Development Corp.
Desk: 907-265-4156
Fax: 907-343-5656
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Thursday, July 10, 2008 11:35 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Pulling all sheets from a spreadsheet
Look in the recent posts, the ADO code I posted a few weeks ago would do
that for you.
Drew
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Porter
Sent: Thursday, July 10, 2008 2:16 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Pulling all sheets from a spreadsheet
Does anyone have some code that pulls data from all tabs in a
spreadsheet, assuming all data is in the same format?
I'm getting spreadsheets with 50+ tabs of budget data for loading into a
database, and it's very cumbersome.
Thanks,
Mark Porter
Sr. Technologist
Nana Development Corp.
Desk: 907-265-4156
Fax: 907-343-5656
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the
person or entity to which it is addressed and may contain II-VI
Proprietary and/or II-VI Business Sensitive material. If you are not the
intended recipient, please contact the sender immediately and destroy
the material in its entirety, whether electronic or hard copy. You are
notified that any review, retransmission, copying, disclosure,
dissemination, or other use of, or taking of any action in reliance upon
this information by persons or entities other than the intended
recipient is prohibited.
--
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
This email and any attachments may contain privileged and confidential
information and are intended for the named addressee only. If you have
received this e-mail in error, please notify the sender and delete this
e-mail immediately. Any confidentiality, privilege or copyright is not
waived or lost because this e-mail has been sent to you in error. It is
your responsibility to check this e-mail and any attachments for
viruses. No warranty is made that this material is free from computer
virus or any other defect or error. Any loss/damage incurred by using
this material is not the sender's responsibility. The sender's entire
liability will be limited to resupplying the material.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.