[AccessD] Hide datasheet view of table when appending

Mon Jun 16 00:18:46 CDT 2008

Drew,

Could I please bug you for some advice with this?

I have tested your code with a range of data in a workbook that starts at Range A1 (with headers) and it worked great.  For most of the code I can what is happening but I am not sure how it can be tweaked in a couple of places.

' This line seems to get the names of all the sheets in the workbook.
Set rs = XLcnn.OpenSchema(adSchemaTables)

'This line goes to the first sheet in the workbook (or more accurately the schematables I would think)
rs.MoveFirst

' This line seems to define the data set to be used - not sure if "TABLE_NAME" can be anything or not? Actually not sure how this works at all.
strSheetName = rs.Fields("TABLE_NAME").Value

' No need to explain - however what if the sheet you want is not the first sheet or you know exactly which sheet you do want?
rs.MoveFirst

I was also wondering if it was possible to use Excel's Named ranges instead of sheets.  All of the dataset I use are in named ranges that automatically adjust shape as needed.  Ideally I would like something like:

Sheets("MyExcelSheet").Range("MyNamedRange") = rs.Fields("TABLE_NAME").Value

That would be very cool indeed.  - heh, actually I might try and play with that and let you know what happens.

cheers
Darryl.

-----Original Message-----
From: Darryl Collins 
Sent: Monday, 16 June 2008 10:45 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Hide datasheet view of table when appending

Drew,

Thanks heaps for this info, I will definately have a play with this technique later this week.

Great Stuff.

Cheers
Darryl

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Drew Wutka
Sent: Sunday, 15 June 2008 12:56 AM
To: Access Developers discussion and problem solving
Subject: Re: [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.

-- 
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.