By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

DoCmd.TransferSpreadsheet

P: n/a
I try to use this command to import a spreadsheet

"DoCmd.TransferSpreadsheet 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.transferspreadsheet 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??

Feb 22 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
programmatically connects an Excel sheet to Access. Then it pulls data
from the connection table into a permanent table in Access.

Sub GetDataFromExcel()

Dim dbsTemp As Database
Dim tdfLinked As TableDef

Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateTableDef("tblSheet1")

tdfLinked.Connect = _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\Dir1\Book1.xls"

tdfLinked.SourceTableName = "Sheet1$"
dbsTemp.TableDefs.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:\Dir1\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.TableDefs 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 ***
Feb 22 '06 #2

P: n/a
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.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=5, _
TableName:="ReportTaskCompleted", _
FileName:=FullName, _
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

Feb 28 '06 #3

P: n/a
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

Feb 28 '06 #4

P: n/a
On Wed, 22 Feb 2006 21:34:08 GMT, Rich P <rp*****@aol.com> 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/

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
programmatically connects an Excel sheet to Access. Then it pulls data
from the connection table into a permanent table in Access.

Sub GetDataFromExcel()

Dim dbsTemp As Database
Dim tdfLinked As TableDef

Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateTableDef("tblSheet1")

tdfLinked.Connect = _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:\Dir1\Book1.xls"

tdfLinked.SourceTableName = "Sheet1$"
dbsTemp.TableDefs.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:\Dir1\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.TableDefs 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 ***


Wayne Gillespie
Gosford NSW Australia
Feb 28 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.