Prev: Hi, how do I use dates in a table to transfer to calendar?
Next: shut off Auto Correct for Word, Outlook, Excel but not Access
From: Alan on 29 Apr 2010 04:57
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

From: Arvin Meyer [MVP] on 29 Apr 2010 09:25
You are running your front-end on the server, and it appears the spreadsheet
as well. If anyone is connected to the spreadsheet file, you cannot delete
it. You wouldn't know that because your error handler just tells the process
to continue anyway.

First, before you corrupt your database, split it and put the front-end on
your workstation. Then comment out the error handlers and see what the
errors are. Typically, someone opens the file and just leaves it running. If
you move the Excel file locally too, you can avoid that problem.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Alan" <Alan(a)discussions.microsoft.com> wrote in message
news:0B907E66-929C-4487-BF06-AFADA4CAAAA7(a)microsoft.com...
>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
>


 | 
Pages: 1
Prev: Hi, how do I use dates in a table to transfer to calendar?
Next: shut off Auto Correct for Word, Outlook, Excel but not Access