[AccessD] Hide datasheet view of table when appending

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

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hewson, Jim 
Sent: Friday, June 13, 2008 2:54 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Hide datasheet view of table when appending

I've used a Recordset before, I didn't think about using it.
I have the TransferSpreadsheet working as Lambert suggested and
everything is working.
My only problem now is that Excel won't shut down even though I tell it
to quit and set the reference to nothing.

Thanks everyone for your help.

Jim 
jhewson at nciinc.com 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Friday, June 13, 2008 2:48 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Hide datasheet view of table when appending

The difference is control, without opening Excel.  Have you ever used a
recordset in code?  With ADO, you can open an excel spreadsheet as a
recordset.  Don't even have to have Excel installed on the machine.

Drew

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.