run-time error 3010 on DoCmd.TransferSpreadsheet

[MCSGraham]

I'm trying to output some text into an Excel file from Access and then export table data right below it. When I do this the text is written but it gives this error when trying to export the table data:

run-time error 3010
Table 'PATIENT_IND_CLAIM_LEDGER$A3:AZ99' already exists

This is my code:

Dim objExcel As EXCEL.Application
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.ADD
objExcel.Visible = False
objExcel.Range("A1") = "GROUP:"
objExcel.Range("B1") = GROUPNAME
objExcel.Sheets(1).NAME = "PATIENT_IND_CLAIM_LEDGER"
objExcel.DisplayAlerts = False
objExcel.Workbooks(1).SaveAs (strPath)
objExcel.DisplayAlerts = True
objExcel.Quit
Set objExcel = Nothing

DoCmd.SetWarnings False

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "PATIENT IND CLAIM LEDGER", strPath, True, "PATIENT_IND_CLAIM_LEDGER!A3:AZ99"

strPath is just the path/filename.

I don't understand -- the file is created and the text is written only to the first row -- why won't it then output the data dump starting at cell A3 instead of giving me this error?

thanks in advance
any help is greatly appreciated!

 

[PHV]

The 6th argument of the DoCmd.TransferSpreadsheet method is only valid for an Import (Clearly stated in the help file).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MCSGraham]

If I'm not able to use the TransferSpreadsheet method to output data starting at a specific point in an Excel sheet does anyone know how to dump an Access table into an Excel sheet starting at a specific cell?

 

[PHV]

Why not pulling the data in access with a QueryTable ?
menu Data -> External data ->

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MCSGraham]

Not sure what you're meaning? I'm trying to export the data from Access to Excel but give the data a header or two.

Do you mean to export the data using the menu options instead of code?

 

[PHV]

No, I mean import the data.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MCSGraham]

I figured it out myself after doing a few searches. Cannot use the Docmd.Transferspreadsheet command only -- have to do this and then insert extra rows for my headers. Code is like this:


'Delete the previous Excel file if it exists
Dim KillFile As String
KillFile = strPath
'Check that file exists
If Len(Dir$(KillFile)) > 0 Then
'First remove readonly attribute, if set
SetAttr KillFile, vbNormal
'Then delete the file
Kill KillFile
End If

'Export the data dump
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "PATIENT IND CLAIM LEDGER", strPath, True

'Write any headers above the data dump
Dim objExcel As EXCEL.Application
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open (strPath)
objExcel.Visible = False
objExcel.Rows(1).EntireRow.Select
objExcel.Rows(1).Insert (xlDown)
objExcel.Rows(2).EntireRow.Select
objExcel.Rows(2).Insert (xlDown)
objExcel.Range("A1") = "GROUP:"
objExcel.Range("B1") = GROUPNAME
objExcel.DisplayAlerts = False
objExcel.Workbooks(1).Save
objExcel.DisplayAlerts = True
objExcel.Quit
Set objExcel = Nothing


There's probably a better way to do this but this will work for me for the time being.