Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81

    Unanswered: Kill is not working

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2004
    Posts
    37

    Is the file open?

    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

  4. #4
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Physically its not open but in Task Manager it shows. EXCEL.exe...

    and

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




    any clue???

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you opened the spreadsheet in VBA? For example your commented out code above:
    Code:
    'Set myWorkbook = myXL.Workbooks.Open(fName)
    If you don't close the stream then it's in use!
    Code:
    myWorkbook.Close
    Set myWorkbook = Nothing
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    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

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    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?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •