Saving two files with changing names to a different location and then delete from Downloads folder

jconkl02

Board Regular
Joined
May 25, 2016
Messages
55
I'm trying to attack a problem from a different angle. I posted last week about wanting to open two .xls files in my Downloads folder that begin with "report" but have a ever changing numerical sequence after. For example, report1564504574214 or report1564504538987. Then I depending on what was in cell A1 of each WB, all of the contents of Sheet(1) would be copied and pasted into one of two worksheets in the WB that was executing the macro called "Daily Ticket Report.xlsm".

If the .xls file has "Program" in A1 then all of the data will be copied and pasted into "LastComment" WS in the Daily Ticket Report.xlsm WB.
If the .xls file has "Number" in A1 then all of the data will be copied and pasted into "Tickets" WS in the Daily Ticket Report.xlsm WB.

The problem is that I want to delete the report*.xls file after it's contents have been copied. The user will start with only two report*.xls files in their Downloads folder and I want them to be deleted so next time they pull the reports they again will start with just two report.xls filed.

JLGWhiz wrote a brilliant bid of code that should work, but when it goes to delete the open report*.xls file Excel spits out a runtime error:
Run-time error '-214722108 (800401a8)':Automation error

Code:
Sub t()Dim wb As Workbook, fPath As String, fName As String, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("LastComment")
Set sh2 = Sheets("Tickets")
fPath = "C:\Users\me\Downloads\"
fName = Dir(fPath & "Report*.xls")
    Do While fName <> ""
        Set wb = Workbooks.Open(fPath & fName)
        If wb.Sheets(1).Range("A1") = "Program" Then
            If sh1.Range("A1") = "" Then
                wb.Sheets(1).UsedRange.Copy sh1.Range("A1")
            Else
                wb.Sheets(1).UsedRange.Copy sh1.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        ElseIf wb.Sheets(1).Range("A1") = "Number" Then
            If sh2.Range("A1") = "" Then
                wb.Sheets(1).UsedRange.Copy sh2.Range("A1")
            Else
                wb.Sheets(1).UsedRange.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        End If
        wb.Close False
        Kill wb.FullName
        fName = Dir
        Set wb = Nothing
    Loop

End Sub

So now I'm wondering if it would be easier to have Excel loop through the Downloads folder and just SaveAs the report*.xls files to something very generic like Data1.xlsx and Data2.xlsx in "C:\Users\me\Documents\Ticket Report". Then I would just be opening files that I do know the name of already, and I could just look at A1 of each file and determine what I wanted to do with the contents. Then when all is said and done, go back to Downloads and delete ALL .xls files that begin with the word "report".

Any ideas on how to accomplish either version of my diabolical plan?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
That error occurs because every property of the 'wb' workbook, including FullName, is undefined after the wb.Close line. Here is the solution:
Code:
    Dim wbFile As String
    wbFile = wb.FullName
    wb.Close False
    Kill wbFile
 
Upvote 0
Solution
Where does your solution go? Directly after the original wb.Close line?


That error occurs because every property of the 'wb' workbook, including FullName, is undefined after the wb.Close line. Here is the solution:
Code:
    Dim wbFile As String
    wbFile = wb.FullName
    wb.Close False
    Kill wbFile
 
Upvote 0
My code replaces your Close and Kill lines. It stores the full name of the 'wb' workbook before closing it, so that the Kill knows which file to delete.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top