DoCmd.TransferSpreadsheet

[Tom Louchbaum]

I try to use this command to import a spreadsheet

"DoCmd.Transfer Spreadsheet acImport, 8, tablename & " -tbl", DIR &
"Book2.xls" , True"

and it errors out. If I try to import that same spreadhseet with menu
commands directly it works fine. The Docmd.transfers preadsheet command
has always worked for me in the past, I will admit the excel file I'm
importing is now slghtly different but I cannot figure out why the
transfer does not work.

TIPS??

[Rich P]

Re: DoCmd.TransferS preadsheet

Here is a VBA routine that I wrote which you could try from a Standard
Access Code module (as opposed to a Form code module). This procedure
programmaticall y connects an Excel sheet to Access. Then it pulls data
from the connection table into a permanent table in Access.

Sub GetDataFromExce l()

Dim dbsTemp As Database
Dim tdfLinked As TableDef

Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateT ableDef("tblShe et1")

tdfLinked.Conne ct = _
"Excel 5.0;HDR=YES;IME X=2;DATABASE=C: \Dir1\Book1.xls "

tdfLinked.Sourc eTableName = "Sheet1$"
dbsTemp.TableDe fs.Append tdfLinked
DoCmd.RunSql "Select * Into tbl1 From tblSheet1"

End Sub

Here is an explanation of what is going on. First, you create a
tableDef object to receive the Excel table you are going to connect to.
In this example I call the object tdfLinked. Note the connection
string. The only part of the connection string that you can modify is
the location of the Excel workbook you are going to connect. Here I
connect to DATABASE=C:\Dir 1\Book1.xls. But you can write whatever path
you need. Leave the rest of the connection string as is. Next, in my
example, I am connecting to Sheet1. Use whatever the sheet name is.
Note also that you have to follow the sheet name with a $ sign or it
won't work. Then, of course, you append the tableDef object to the
database.TableD efs collection. Now you run a sql query to import the
data into a new table. Assuming tbl1 does not already exit you run this
query from the VBA routine

DoCmd.RunSql "Select * Into tbl1 From tblSheet1"

And you have now imported your Excelsheet to Access.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Ron2005]

Re: DoCmd.TransferS preadsheet

Sometimes you may want to do the transfer spreadsheet anyway. I have
found that sometimes a linked spreadsheet will have fields that to not
transfer over whereas the transfer works just fine.

example:

DoCmd.TransferS preadsheet _
TransferType:=a cImport, _
SpreadsheetType :=5, _
TableName:="Rep ortTaskComplete d", _
FileName:=FullN ame, _
HasFieldNames:= True

This was in subroutine in the code module. I had to research and play
with the spreadsheetType to get one that worked with the spreadsheets I
was receiving.

In fact I had one spreadsheet that was being "exported" from a web page
that I had to develop a separate access subroutine to open it in excell
and then re-save it as a specific spreadsheet because the import
routine would not consider it a spreadsheet when executed through code.

Ron

[Ron2005]

Re: DoCmd.TransferS preadsheet

I just noticed that you were specifying a spreadsheet type of 8 whereas
I had used a 5.

You may want to reseach some on the difference and try the different
posibilities, expecially since you indicated the spreadsheet was
different, it may be that it is being saved differently at the source.

Ron

[Wayne Gillespie]

Re: DoCmd.TransferS preadsheet

On Wed, 22 Feb 2006 21:34:08 GMT, Rich P <rpng123@aol.co m> wrote:

It should be noted that if the data in the linked spreadsheet is to be editable
from Access, then this method will no longer work in Access 2003 (or Access 2002
if the patch dated 18/10/2005 has been installed).

MS have removed this functionality due to a current legal dispute over patents
(apparently). Excel spreadsheets linked to Access as TableDefs are now
ReadOnly.

This is a royal PITA which has bitten me recently when a client converted to
A2003 and all the export routines in their current database stopped working.

As would be expected from MS there is very little information about the details
of the dispute, however the following articles mention the removal of the
functionality.

http://support.microsoft.com/kb/904953/

[color=blue]
>Here is a VBA routine that I wrote which you could try from a Standard
>Access Code module (as opposed to a Form code module). This procedure
>programmatical ly connects an Excel sheet to Access. Then it pulls data
>from the connection table into a permanent table in Access.
>
>Sub GetDataFromExce l()
>
>Dim dbsTemp As Database
>Dim tdfLinked As TableDef
>
>Set dbsTemp = CurrentDb
>Set tdfLinked = dbsTemp.CreateT ableDef("tblShe et1")
>
>tdfLinked.Conn ect = _
> "Excel 5.0;HDR=YES;IME X=2;DATABASE=C: \Dir1\Book1.xls "
>
>tdfLinked.Sour ceTableName = "Sheet1$"
>dbsTemp.TableD efs.Append tdfLinked
>DoCmd.RunSql "Select * Into tbl1 From tblSheet1"
>
>End Sub
>
>Here is an explanation of what is going on. First, you create a
>tableDef object to receive the Excel table you are going to connect to.
>In this example I call the object tdfLinked. Note the connection
>string. The only part of the connection string that you can modify is
>the location of the Excel workbook you are going to connect. Here I
>connect to DATABASE=C:\Dir 1\Book1.xls. But you can write whatever path
>you need. Leave the rest of the connection string as is. Next, in my
>example, I am connecting to Sheet1. Use whatever the sheet name is.
>Note also that you have to follow the sheet name with a $ sign or it
>won't work. Then, of course, you append the tableDef object to the
>database.Table Defs collection. Now you run a sql query to import the
>data into a new table. Assuming tbl1 does not already exit you run this
>query from the VBA routine
>
>DoCmd.RunSql "Select * Into tbl1 From tblSheet1"
>
>And you have now imported your Excelsheet to Access.
>
>Rich
>
>*** Sent via Developersdex http://www.developersdex.com ***[/color]

Wayne Gillespie
Gosford NSW Australia