Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I have a macro that extracts a worksheet called summary. It then saves this as the name of the original workbook end with summary for eg ECBR1.summary

    Once the file has been sent, I would like the file ending with summary deleted from the directory c:/z_accts.

    It would be appreciated if you could amend my macro to do this-see my code below

    Your assistance will be most appreciated

    Sub SendFiles()
    Dim lCount As Long
    Dim vFilenames As Variant
    Dim sPath As String
    Dim lFilecount As Long
    Dim sFullName As String
    sPath = "c:\windows\temp\"
    ChDrive sPath
    ChDir sPath
    vFilenames = Application.GetOpenFilename("Microsoft Excel files (*.xls),*.xls", , "Please select the file(s) to open", , True)
    If TypeName(vFilenames) = "Boolean" Then Exit Sub
    For lCount = LBound(vFilenames) To UBound(vFilenames)
    Workbooks.Open vFilenames(lCount)
    Worksheets("summary").Copy
    ActiveWorkbook.SaveAs Replace(vFilenames(lCount), ".xls", "") & ".summary.xls"
    vFilenames(lCount) = ActiveWorkbook.FullName
    ActiveWorkbook.Close False
    Next
    With Worksheets("summary")
    .UsedRange.Formula = .UsedRange.Value
    End With
    With Worksheets("summary")
    .UsedRange.Formula = .UsedRange.Value
    End With
    Mailfiles "stephen@mweb.com", vFilenames
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I understand what you want [So make sure you have BACKUPS in case I do not!] You want to delete all the newly named files. Add the red lines...

    Mailfiles "stephen@mweb.com", vFilenames
    For lCount = LBound(vFilenames) To UBound(vFilenames)
    kill vFilenames(lCount)
    next
    End Sub

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the help



    Howard

Posting Permissions

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