Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to delete file?

    Tried to create a macro that will delete a file. Does anyone know how to go about this using VB? Using mS Excel 97.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to delete file?

    Try Kill Pathname where pathname is the full path and filename of the file you wish to delete, example

    Kill "C:My DocumentsFile1.xls"

    Andrew

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to delete file?

    HELP: I use MS Excel 97, Access97
    Tried this your suggestion and it will not work in my case. I need to use the following network path: prodSecurityMasterTerm2001.xls

    I keep getting "RunTime Error: 70, Permission denied" when it comes to the KILL statement at the end.

    With ActiveSheet.QueryTables.Add(Connection:= _
    "FINDER;tabsproddatabaseEdgarSecurityTermination.d qy", Destination:=Range("A5"))
    .FieldNames = True
    .RefreshStyle = xlInsertDeleteCells
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .RefreshOnFileOpen = False
    .HasAutoFormat = True
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SavePassword = True
    .SaveData = True
    End With
    Rows("5:5").Select
    Selection.Delete Shift:=xlUp
    Range("A5:F38").Select
    Selection.Sort Key1:=Range("D5"), Order1:=xlDescending, Key2:=Range("A5") _
    , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom


    'Save Spreadsheet
    Application.DisplayAlerts = False
    Kill "tabsproddatabaseEdgarSecurityMasterTerm2001.x ls"
    ActiveWorkbook.SaveAs FileName:="tabsproddatabaseEdgarSecurityMasterTerm 2001.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    End Sub

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to delete file?

    Have you tried to delete the file manually, and if so did you succeed ?

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to delete file?

    Be working on something else for a while and just go back to this problem. If I manualy delete "Runterm2001.xls" it works fine. How can I get it to overwrite the file? Starting to get desperate.


    'Save Spreadsheet
    Application.DisplayAlerts = False
    'Kill "J:EdgarSecurityMasterTerm2001.xls"

    ActiveWorkbook.SaveAs FileName:="tabsproddatabaseEdgarSecurityRunTerm200 1.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    End Sub

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to rename file?

    I use this macro to collect files from a directoy...

    Sub GetFiles()
    Dim i as integer
    Dim myFile as string
    i=1
    myFile=Dir("*.xls")
    Do until myFile = ""
    Cells(i,1).Value = myFile
    i=i+1
    myFile = Dir
    Loop
    End Sub

    My question is, after I have modified the filenames collected, can I send the name back to the directory again? Is there code to rename like there is code to "Kill"
    TX
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to rename file?

    The VBA instruction to rename (and/or move) a file is
    <code>
    Name "OldName" As "NewName"
    </code>
    Example 1:
    <code>
    Name "C:ExcelInvoice.xls" As "C:ExcelInvoice2007.xls"
    </code>
    Since the path in the old and new names is the same, the file is simply renamed.

    Example 2:
    <code>
    Name "C:ExcelInvoice.xls" As "C:BackupInvoice.xls"
    </code>
    Since the path in the old and new names is different, the file is moved; it retains the same file name.

    Example 3:
    <code>
    Name "C:ExcelInvoice.xls" As "C:BackupInvoice2007.xls"
    </code>
    The file is moved to a different folder and renamed at the same time.

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to rename file?

    TX professor! <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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