473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DoCmd.TransferSpreadsheet

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
4 48731
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: mcnewsxp | last post by:
how can i specify a particular worksheet using the docmd transferspreadsheet? thanks, mcnewsxp
1
by: Bill Agee | last post by:
How do you specify a specific worksheet? I know this works if the worksheet in question is the 1st, but suppose I am interested in importing several worksheets from the same workbook. There does...
3
by: user_5701 | last post by:
Hello, I'm getting an error with a Docmd.Transferspreadsheet line of code: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000, "tblTest", pathAndFilename, True The above line...
13
by: aleksandra_83 | last post by:
Hello, I have searched all over google groups and internet, but I have not found a solution to this problem that has worked. The solutions I found helped me single out the line that is causing...
3
by: sranilp | last post by:
Hey All, Actually I need to export the data from Access to Excel particular spreadsheet(ie.Raw Data),so I was using Docmd.Transferspreadsheet but in this syntax where i can give the spreadsheet...
2
by: amir369 | last post by:
Hi All, I'm trying to import an Excel sheet, but with more the one rang, couse the columns aint sequence. for example: DoCmd.TransferSpreadsheet acImport, , "DailyRprzntvRpt", _ ...
5
by: jerelp | last post by:
I have a form with one button the code for that button is On Error Resume Next DoCmd.TransferSpreadsheet acImport, 8, "FuelConsumption NewData", Me.tb_FileName, 1, "sheet2$" On Error Resume...
4
by: blammo04 | last post by:
My problem is that Access freezes up whenever I try to use the docmd.transferspreadsheet method, I can use the docmd.outputto and it works fine but I need to be able to use the transferspreadsheet...
0
by: Van Fitz | last post by:
I am importing an excel worksheet into an existing access 2000 table using TransferSpreadsheet. Im using a timestamp field as well in this table. This is going well, however, once the data is...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.