access novice trying to export access table to excel - using 'DoCmd.TransferSpreadsheet'

fingermouse

Board Regular
Joined
Dec 13, 2013
Messages
117
Hi,

Not sure if this should be in the vba, excel or access forum but ill put it in here, hope someone can help. I should mention that I'm a complete novice in Access.

I was unable to export more than 65,000 records to excel from a table within Access using the export wizard (I unticked the formatting box but it didn't help). Someone suggested using VBA 'DoCmd.TransferSpreadsheet' function. Here is the code ive used.

Code:
Option Compare Database
----------------------------------


Sub ExportToXl()


On Error GoTo ErrorHandler


Dim dbTable As String
Dim xlWorksheetPath As String


xlWorksheetPath = "\\lonfs01\home\mckellarc\"


xlWorksheetPath = xlWorksheetPath & "transfer.xls"


dbTable = "Source Table"


DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="dbTable", FileName:=xlWorksheetPath, hasfieldnames:=True


ErrorHandlerExit:


Exit Sub


ErrorHandler:


MsgBox "Error No: " & Err.Number & ";Descriptions; " & Err.Description


Resume ErrorHandlerExit


End Sub
I haven't had any errors in the code itself but I do get the following error upon execute:

"Run-time Error '3011': The Microsoft Access database engine could not find the object 'dbTable'. Make sure the object exists and that you spell the path name correctly. If dbTable' is not a local object, check your network connection or contact the server administrator."

So obviously I'm doing something wrong. The table 'Source Table' is the name of the table within the access database file. Am I referencing this properly or using the correct path? Any help would be much appreciated.

Cal.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,067
Office Version
365
Platform
Windows
The table 'Source Table' is the name of the table within the access database file
Then why are you trying to export a table named "dbTable"?
Code:
DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, [COLOR=#ff0000]TableName:="dbTable"[/COLOR], FileName:=xlWorksheetPath, hasfieldnames:=True
I was unable to export more than 65,000 records to excel
That is because you are trying to export to an "xls" file, which is an old Excel format that only allows 65536 rows.
If you are using Office 2007 or newer, try exporting to an "xlsx" file, which is
SpreadsheetType:=acSpreadsheetTypeExcel12
not
SpreadsheetType:=acSpreadsheetTypeExcel9

See: Access VBA DoCmd.TransferSpreadSheet Method
 

fingermouse

Board Regular
Joined
Dec 13, 2013
Messages
117
Thanks for responding Joe, much appreciated.

I have implemented your suggested changes and read the article on the link provided. However, I now get the following error upon execute:

"Run-time error '3883': This calculated column contains an invalid expression."

What does this mean?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,067
Office Version
365
Platform
Windows
Are you trying to export a table or query?
Do you have any calculated fields in it?
Do you have any bad data in it?
Which line of code is causing that error to return?
 

fingermouse

Board Regular
Joined
Dec 13, 2013
Messages
117
Are you trying to export a table or query? A table (named "Source Table")

Do you have any calculated fields in it? No

Do you have any bad data in it? Not that I know of - there are over a million rows however, so it could be possible.

Which line of code is causing that error to return? DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Source Table", "\\lonfs01\home\mckellarc\transfer.xlsx", True
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,067
Office Version
365
Platform
Windows
I have never seen that error before, nor can I find any reference to it in a Google Search.
What version of Access are you using?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,067
Office Version
365
Platform
Windows
Out of curiosity, is there any reason you are creating VBA code to do this and not just using the TransferSpreadsheet macro option?
It doesn't look like you are doing anything special that would require you to do it in VBA.
 
Last edited:

fingermouse

Board Regular
Joined
Dec 13, 2013
Messages
117
Out of curiosity, is there any reason you are creating VBA code to do this and not just using the TransferSpreadsheet macro option?
It doesn't look like you are doing anything special that would require you to do it in VBA.
I was unable to export more than 65,000 records to excel Access using the export wizard (I unticked the formatting box but it didn't help), and was trying to find an alternative approach. I also have 1.2 millions rows to export which doesn't help.

What is this TransferSpreadsheet macro option you mention? Would this be written within excel instead of Access?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,067
Office Version
365
Platform
Windows
No, Macros are right in Access. It allows you to run some of these things without having to write any VBA code by just filling in the needed arguments.
Its name has changed in Access 2010.
1. Go to the Create menu
2. Click on Macro
3. Click on the "Show All Actions" icon in the ribbon
4. In the "Add New Action" drop down box, select ImportExportSpreadsheet
5. Fill in the needed arguments

Note that if you have 1.2 records to export to Excel, you are still going to have issues, as the new versions of Excel only allow 1,048,576 rows.
You are going to need to break it up into two. You can do that using a Query, adding some criteria to split it.
 

Forum statistics

Threads
1,089,276
Messages
5,407,342
Members
403,135
Latest member
becca_832313

This Week's Hot Topics

Top