A
Alan
I keep getting this message saying "cannot delete spreadsheet cells" but i
don't know why??
can anyone help me please??
my code below
Sub exportspreadsheet()
On Error GoTo HandleError
Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook
Dim db As DAO.Database
Set db = CurrentDb
conPath = GetPath(db.Name)
'delete the spreadsheet
Kill conPath & "temp_MonthlySalesReport"
' create a workbook from the template
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(conPath &
"temp_MonthlySalesReport.xlt")
'objXLApp.Visible = True
objXLBook.SaveAs (conPath & "temp_MonthlySalesReport.xlsm")
objXLBook.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"temp_MonthlySalesReport",
"F:\Accounts\Projects\Analysis\Billlings\DSICMM\Access\temp_MonthlySalesReport", True, "temp_MonthlySalesReport"
MsgBox "Done!" & vbCrLf & vbCrLf & "Look in the directory" & vbCrLf & vbCrLf
& "where the application sits for ""temp_MonthlySalesReport.xls"""
ProcDone:
On Error Resume Next
' Let's clean up our act
Set qdf = Nothing
Set db = Nothing
Set rs = Nothing
Set objResultsSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing
ExitHere:
Exit Sub
HandleError:
Select Case Err.Number
Case 3265
Resume Next
Case 1004
Set objXLBook = objXLApp.Workbooks.Open(conPath &
"temp_MonthlySalesReport")
Resume Next
Case 53
Resume Next
Case 75
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number
End Select
Resume ProcDone
End Sub
don't know why??
can anyone help me please??
my code below
Sub exportspreadsheet()
On Error GoTo HandleError
Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook
Dim db As DAO.Database
Set db = CurrentDb
conPath = GetPath(db.Name)
'delete the spreadsheet
Kill conPath & "temp_MonthlySalesReport"
' create a workbook from the template
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(conPath &
"temp_MonthlySalesReport.xlt")
'objXLApp.Visible = True
objXLBook.SaveAs (conPath & "temp_MonthlySalesReport.xlsm")
objXLBook.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"temp_MonthlySalesReport",
"F:\Accounts\Projects\Analysis\Billlings\DSICMM\Access\temp_MonthlySalesReport", True, "temp_MonthlySalesReport"
MsgBox "Done!" & vbCrLf & vbCrLf & "Look in the directory" & vbCrLf & vbCrLf
& "where the application sits for ""temp_MonthlySalesReport.xls"""
ProcDone:
On Error Resume Next
' Let's clean up our act
Set qdf = Nothing
Set db = Nothing
Set rs = Nothing
Set objResultsSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing
ExitHere:
Exit Sub
HandleError:
Select Case Err.Number
Case 3265
Resume Next
Case 1004
Set objXLBook = objXLApp.Workbooks.Open(conPath &
"temp_MonthlySalesReport")
Resume Next
Case 53
Resume Next
Case 75
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number
End Select
Resume ProcDone
End Sub