2000 Excel export not working under 2007

[rtdc]

I have an issue with a 2000 database running under 2007. It works perfectly in 2000 and uses code I have used many times and at one point worked under 2007 but all of a sudden it won’t.

It stops at

Set wb_obj = xl_obj.workbooks.Add(Application.CurrentProject.Path & "\Templates\AtBranch.xls")

The path and the template both exist and I can run the exact same database in 2000 no problems.

It is attached to the Microsoft Excel Object 9.0 library, we tried rebuilding the database using 2007 and attaching the 12 library and it wouldn’t work (created as a 2000 mdb).

It gives me a runtime 1004 error

· Make sure specified folder exists
· Make sure the file that contains the file is not read only
· Make sure the file does not contain any of the following characters
<> ? [ ] : Asset.xls or *
· Make sure the file doesn’t contain more than 218

The folder is not read only, the file name doesn’t contain any of the characters listed, the folder exists.

Any ideas?

The Full function

Public Function ItemsatBranchSpreadsheet(str_BranchName As String) As String

Dim xl_obj As New Excel.Application
Dim wb_obj As Excel.Workbook
Dim rs As Object, V As String
Dim wc As Object

Dim FilePath As String

'SET THE WORKBOOK OBJECT TO AN INSTANCE OF TEMPLATE.XLT
Set wb_obj = xl_obj.workbooks.Add(Application.CurrentProject.Path & "\Templates\AtBranch.xls")

xl_obj.DisplayAlerts = False

wb_obj.Worksheets(1).Name = str_BranchName

V = [Forms]![Items at Branch]![Branch Number]

Set rs = CurrentDb.OpenRecordset("SELECT tblAssets.[Make], tblAssets.[Model], tblItematBranch.[ItemSerialNo], tblAssets.[Type], tblAssets.[Asset Number], tblAssets.[Status],tblAssets.[User Name]" & _
"FROM tblAssets INNER JOIN tblItematBranch ON tblAssets.[SerialNo] = tblItematBranch.[ItemSerialNo]" & _
"WHERE (((tblItematBranch.BranchNo) = " & V & "))" & _
"GROUP BY tblAssets.[Make], tblAssets.[Model], tblItematBranch.[ItemSerialNo], tblAssets.[Type], tblAssets.[Asset Number],tblAssets.[Status], tblAssets.[User Name];")

'copies entire recordshet into excel worksheet
xl_obj.Worksheets(1).Cells(5, 1).Value = "Make"
xl_obj.Worksheets(1).Cells(5, 2).Value = "Model"
xl_obj.Worksheets(1).Cells(5, 3).Value = "Serial No:"
xl_obj.Worksheets(1).Cells(5, 4).Value = "Type:"
xl_obj.Worksheets(1).Cells(5, 5).Value = "Asset No:"
xl_obj.Worksheets(1).Cells(5, 6).Value = "Status:"
xl_obj.Worksheets(1).Cells(5, 7).Value = "User"
xl_obj.Worksheets(1).Cells(5, 8).Value = "Checked"
xl_obj.Worksheets(1).Cells(5, 9).Value = "Notes"

xl_obj.Worksheets(1).Cells(6, 1).CopyFromRecordset rs

'adds period
wb_obj.Worksheets(1).Range("A2").Value = "Branch No: " & V
wb_obj.Worksheets(1).Range("A3").Value = "Branch Name: " & str_BranchName

xl_obj.DisplayAlerts = True

FilePath = Application.CurrentProject.Path & "\ReportOutput\" & str_BranchName & "_AssetList_" & Format(Now(), "dd-mm-yyyy") & ".xls"
wb_obj.SaveAs (FilePath)

Set wb_obj = Nothing
xl_obj.Quit
Set xl_obj = Nothing

CreateExcelSpreadsheet = FilePath

MsgBox FilePath & " Has been created"

End Function

 

[boblarson]

'SET THE WORKBOOK OBJECT TO AN INSTANCE OF TEMPLATE.XLT
Set wb_obj = xl_obj.workbooks.Add(Application.CurrentProject.Path & "\Templates\AtBranch.xls")

Your note says "An instance of Template.XLT" but I see an .XLS at the end of the file name. Isn't it supposed to be XLT?

 

[rtdc]

It doesn’t matter if it is xlt or xls file as it is just using a blank template in this instance. I have now resolved this issue by switching to using a docmd.transferspreadsheet command, perhaps not the cleanest solution but it’s working.