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

[fingermouse]

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.

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.

 

[Joe4]

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"?

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]

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]

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]

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]

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?

 

[fingermouse]

I'm using access 2010.

 

[Joe4]

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.

 

[fingermouse]

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]

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.