+ Reply to Thread
Results 1 to 6 of 6

How to select any file in folder name (Left = "Final Test") then kill the file

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    How to select any file in folder name (Left = "Final Test") then kill the file

    Hello All,
    I'm looking for code how to open any files which Left 10 chars = "Final Test", do something then kill this file after done.

    Dim sDir As String
    Dim sFname As String
    Dim thswbk
    Dim actvbk
    Set thswbk = ThisWorkbook
    Application.ScreenUpdating = False
    
    sDir = "C:\Mycomputer\Testing"
    sFname = Dir(sDir & "\*.xls")   '''I want to open any file contains Left 10 Chars = "Final Test"
    Set thswbk = ThisWorkbook
    
    Application.DisplayAlerts = False
    Do While sFname <> ""
        
        Workbooks.Open sDir & "\" & sFname, Format:=1, Local:=True
        Set actvbk = ActiveWorkbook
        actvbk.Activate
        
    Call Macro1
    '''I also want to kill the actvbk
        sFname = Dir
    Loop
    
    thswbk.Activate
    Application.ScreenUpdating = True
    Regards,
    tt3
    Last edited by tuongtu3; 10-30-2013 at 12:41 AM. Reason: Solved

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: How to select any file in folder name (Left = "Final Test") then kill the file

    sFname = Dir(sDir & "\Final Test*.xls")
    ?

  3. #3
    Forum Contributor
    Join Date
    05-10-2012
    Location
    Paris, France
    MS-Off Ver
    2016/365
    Posts
    123

    Re: How to select any file in folder name (Left = "Final Test") then kill the file

    Hi all

    Try this optimised code
    Sub test()
      Dim sDir As String, sFname As String
      Dim thswbk As Workbook, actvbk As Workbook
      Set thswbk = ThisWorkbook
      Application.ScreenUpdating = False
    
      sDir = "C:\Mycomputer\Testing"
      sFname = Dir(sDir & "\Final Test*.xls")  ' I want to open any file contains Left 10 Chars = "Final Test"
    
      Application.DisplayAlerts = False
      Do While sFname <> ""
        Set actvbk = Workbooks.Open(sDir & "\" & sFname, Format:=1, Local:=True)
        Call Macro1
        actvbk.Close
        ' Delete file
        kill sDir & "\" & sFname
        sFname = Dir
      Loop
      thswbk.Activate
      Application.ScreenUpdating = True
    End Sub
    Last edited by BrianM45; 10-30-2013 at 12:37 AM.

  4. #4
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    Re: How to select any file in folder name (Left = "Final Test") then kill the file

    Hi Jindon & BrianM45:
    Thank you very much.

    How about to kill the file. Please help

    Call Macro1
    '''I also want to kill the actvbk ("Final Test")
    sFname = Dir

        Workbooks(sFname).Close SaveChanges:=False
        Kill sFname   '''This line sometime does work; sometime this line does not work
        sFname = Dir
    Regards,
    tt3
    Last edited by tuongtu3; 10-30-2013 at 12:32 AM.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: How to select any file in folder name (Left = "Final Test") then kill the file

        actvbk.Close
        kill sDir & "\" & sFname
        sFname = Dir

  6. #6
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    Re: How to select any file in folder name (Left = "Final Test") then kill the file

    Hi Jindon,
    That works perfect. Thank you for your time and help.

    Regards,
    tt3

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Saving file to its current location and current file name with current date and "FINAL"
    By ironwill20 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2012, 03:22 AM
  2. Replies: 2
    Last Post: 07-26-2010, 06:59 AM
  3. Transfert cell values from file "A" to file "B" skipping columns in file "B".
    By Sentrosi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2009, 11:11 PM
  4. zip workbook file by using "Send to compressed zipped folder"
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-10-2009, 04:55 PM
  5. Saving a file to a "YYYY" and "MMM" folder
    By hriggs in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-10-2008, 07:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1