Why cant i export a table to excel?

[scottian]

Im trying to export a table to excel but for some reason i get the error 'A5:K1000 already exists'. Im trying to export the table to a range of cells within an existing workbook. Ive tried to solve it, but im tearing my hair out now. Ive searched through posts, and all seem to answer with using 'transferspreadsheet', but this isnt working.
Any help with this is much appreciated
Heres what im using

Option Compare Database
Option Explicit

Function ImportExport()

Dim FullDbName As Integer
Dim What2Look4 As String
Dim ThisDBname As String
Dim ThisDBpath As String
Dim FileName As String
Dim XL_APP As Excel.Application
Dim XL_WBOOK As Excel.Workbook
Dim XL_SHEET As Excel.Worksheet
Set XL_APP = CreateObject("Excel.Application")

'gets the length of the database name i.e. C:\my drive\my database.mdb
FullDbName = InStr(1, CurrentDb.Name, ".mdb")
What2Look4 = Mid(CurrentDb.Name, FullDbName, 1)
'loop until the last backslash int the database name is found
Do Until What2Look4 = "\"
What2Look4 = Mid(CurrentDb.Name, FullDbName - 1, 1)
FullDbName = FullDbName - 1
Loop
ThisDBname = Mid(CurrentDb.Name, FullDbName + 1, FullDbName)
ThisDBpath = Left(CurrentDb.Name, FullDbName)
FileName = ""
FileName = Dir(ThisDBpath & "*.xls")
DoCmd.RunSQL "DELETE CLEANERtbl.F1 FROM CLEANERtbl;"
DoCmd.TransferSpreadsheet acImport, 8, "CLEANERtbl", ThisDBpath & FileName, False, "A:K", True
DoCmd.RunSQL "DELETE CLEANERtbl.F10, CLEANERtbl.F2 FROM CLEANERtbl WHERE (((CLEANERtbl.F10) Like 'duplicate claim*')) OR (((CLEANERtbl.F2) Is Null)) OR (((CLEANERtbl.F2)='mprn'));"
DoCmd.RunSQL "UPDATE CLEANERtbl SET CLEANERtbl.F9 = 0 WHERE (((CLEANERtbl.F9) Is Null));"
DoCmd.RunSQL "UPDATE CLEANERtbl SET CLEANERtbl.F3 = Trim([F3]), CLEANERtbl.F4 = Trim([F4]);"

With XL_APP
Set XL_WBOOK = XL_APP.Workbooks.Open(ThisDBpath & FileName)
Set XL_SHEET = .Worksheets("LE - SEP - BGAS")
XL_SHEET.Select
.Range("A5:N1000").Select
.Selection.Delete Shift:=xlUp
XL_WBOOK.Save
XL_WBOOK.Close
Set XL_SHEET = Nothing
Set XL_WBOOK = Nothing
XL_APP.Application.Quit
Set XL_APP = Nothing
End With

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "REM_DUPEqry", ThisDBpath & FileName, False, "A5:K1000"

End Function


"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[ck1999]

This is from Microsoft help concerning docmd.transferspreadsheet

Range Optional Variant A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

ck1999

 

[scottian]

Argggh,
So i cant export the info to a certain row.

Thanks for that ck1999, sometimes i cant see the wood for the trees.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[ck1999]

You could always insert your table. Then open the spreadsheet back up and insert 5 blank lines and fill in any data in this section that needs to be there. It would only take a few more programing steps but you would not know the difference after that.

ck1999

 

[JerryKlmns]

scottian

Are you aware of these?

CurrentProject.FullName
CurrentProject.Path
CurrentProject.Name

 

[scottian]

JerryKlmns,

I knew about those, but i try to use them i get 'variable not defined'. So i assumed they werent included with '97

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[JerryKlmns]

Oh yes, you are correct about A97