Hey all,
I am working in Excel 2000. VBA
I am trying to delete a file 'fname'..through vba but whenever I click on button it gives me permission denied errror..
It has nothing to do with any permission. I am not on the server it;s jst local machine..i dont know how to delete a file.. and after deleting i will to recreate this file with new updated data...

Here is my code


If Dir(fName) <> "" Then
'Set myWorkbook = myXL.Workbooks.Open(fName)
'For Each c In myWorkbook.Sheets
'c.UsedRange.ClearContents
'Next

Kill fName

End If

Perhaps you should create the file first, but with a different filename, and then copy the file over the existing one (overwrite)?

The FileCopy method should be a good place to start looking.

Hi,

I am relatively new to this, but are you trying to delete a file that is still open?

In order to use KILL then a path and name is needed, but I am sure that the file would need to be closed, or it would give an error message.

Hope this helps

Physically its not open but in Task Manager it shows. EXCEL.exe...

and

FileCopy gives the same error message...Permission denied...




any clue???

Have you opened the spreadsheet in VBA? For example your commented out code above:
If you don't close the stream then it's in use!

No it didnt work either....

Here is my code.. I am creating an excel file from access queries(for tabs in it)....Need sum of column J....

Here is my all code..

Dim fName As String
Dim dtToday As Date
Dim striType As String

dtToday = Now()

strDt = "_" & DatePart("m", dtToday) & "-" & DatePart("d", dtToday) & "-" & DatePart("yyyy", dtToday)
fName = "C:\UBICUpgradeStatusExport" & strDt & ".xls"
'& Now() & ".xls"
Dim myXL As Excel.Application
Dim myWorkbook As Excel.Workbook
Dim myWorksheet As Worksheet
Dim myRange, myRange2, myrange3, bottomcell, currentcell As Range
Dim c As Worksheet
Dim i, count, j, result, temp As Integer
Set myXL = New Excel.Application
'Open your workbook:
Dim objExcel As Object
'myXL.Visible = True
If Dir(fName) <> "" Then
Set myWorkbook = myXL.Workbooks.Open(fName)
'For Each c In myWorkbook.Worksheets
myWorkbook.Close
Set myWorkbook = Nothing
'Next

myXL.UserControl = False
Set myXL = Nothing

'FileCopy fName, fName
Kill fName

End If



DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", fName, True, "CST"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query2", fName, True, "IC-EXTERNAL"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query3", fName, True, "IC-INTERNAL"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query4", fName, True, "UBI"
'Start Excel

i = 1
Set myXL = New Excel.Application
Set myWorkbook = myXL.Workbooks.Open(fName)
For Each c In myWorkbook.Sheets
j = 1
result = 0
'(watch out for the line break above... that should all be on one line...)
Set myWorksheet = myWorkbook.Sheets.Item(i) 'Use the name of the sheet

myWorksheet.Activate
Set myRange = myWorksheet.Range("A1:Q1")
Set myRange2 = myWorksheet.Range("A:Q")
Set myrange3 = myWorksheet.Range("J:J")
myRange.Font.Bold = True
myRange.Interior.ColorIndex = 15
myRange.EntireColumn.Columns.AutoFit
myRange2.Rows.AutoFit

Set bottomcell = myWorksheet.Cells(65536, 10).End(xlUp)
Set currentcell = myWorksheet.Range(Cells(2, 10), bottomcell)
bottomcell.Offset(1, 0).Value = WorksheetFunction.Sum(currentcell)

i = i + 1
Next
myXL.Workbooks.Close
ActiveWindow.Close
myXL.Quit
Set myXL = Nothing

I guess I'm confused. In one part youa re referring to XL files, and in another you are referring to .exe which is an executable. Am I missing something?

and surely if you need the sum of column J you can simply add a formula to the last cell in J..? Look up formula in the VBA help