My problem is that although the spreadsheet opens and I can delete the sheet ok when I try to save it I get an error message that the object isn't supported. I have tried saving it as per code I have picked up on the forums with the same result.
Please kind people out there give me some suggestions.
The code I am using and the error message is as follows:-
Expand|Select|Wrap|Line Numbers
- Dim stdoc As String
- Dim appexcel As Object
- Dim book As Excel.Workbooks
- Dim sheet As Excel.Worksheet
- Dim myfile As String
- myfile = "H:\ESF DS\Interim Delivery Statement.xls"
- Set db = CurrentDb()
- Set rs = db.OpenRecordset("UKPRN", dbOpenDynaset)
- Set rs1 = db.OpenRecordset("TotalData", dbOpenDynaset)
- Set appexcel = CreateObject("Excel.Application")
- appexcel.DisplayAlerts = False
- DoCmd.SetWarnings False
- rs.MoveFirst
- uk = rs.fields("[UKPRN Number]").Value
- Debug.Print uk
- Forms!frmentry!Combo77 = uk
- appexcel.Workbooks.Open myfile
- appexcel.Visible = True
- appexcel.Sheets("ukprnquery").Select
- appexcel.Sheets("ukprnquery").Delete
- appexcel.Workbook.SaveAs (myfile)