Need to open two workbooks with known path but changing name, get data and then close and delete workbooks

[jconkl02]

Okay, this one has a lot of moving parts.

I have a macro enabled WB (Daily Ticket Report.xlsm) that has two blank worksheets. I export two reports from our ticket system and then manually select all/paste the data in one of those blank WS. The reports export to my Downloads folder (C:\Users\me\Downloads\) as a .xls file that looks like this report1564083050348.xls. The numbers always change, but "report" is always at the beginning of the file name.

I want a macro that will look in Downloads and open each report .xls WB, look in the header row and depending on what is in A1, copy all of the data into one of the blank WS in the "Daily Ticket Report.xlsm". Then I want it to close AND DELETE the file from Downloads.

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.

Then I want both .xls files deleted.

Thanks for the help
Jason

 

[JLGWhiz]

This assumes that workbook "Daily Ticket Report.xlsm" will host the code. This code is untested. You can put a break point in the code at the Kill statement to run the first one or two files and see if the code is doing what you want. If it is, then you can remove the break point and let it run through all of the files. You cannot recover the files once deleted. To insert the breakpoint, after you have pasted the code into module1 of the vbEditor, click in the left margin of the code pane beside the line with the Kill statement. Then when the code runs, it will stop on that line before executing and you can review what the code did before continuing.

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

 

[jconkl02]

I'm getting an error at the kill command after it has got data from the first report .xls file.
Run-time error '-214722108 (800401a8)':Automation error

 

[JLGWhiz]

I'm getting an error at the kill command after it has got data from the first report .xls file.
Run-time error '-214722108 (800401a8)':Automation error

Try changing from

Kill wb.FullName

To

Kill "C:\Users\me\Downloads\" & wb.Name

If the error persists, then I suggest you delete that line, let the macro do the copying for the workbooks and then manually delete the files using the File Explorer to access the file names. You can do that pretty quickly by selecting the file names holding down the Ctrl key and then right click and click 'Delete'.

 

[jconkl02]

The code still stops at the Kill command.

I realize that it is simple to delete the files manually. But what I am trying to accomplish is getting the reports pasted into the bigger report with minimum human intervention. This report at times will be put together by people who can't tell the reports apart, so I'm trying to take the guess work out of it. I can live without the files being deleted, but it seemed like a good way to make it easier for excel to find different reports and not keep hitting the same one.

Try changing from

Kill wb.FullName

To

Kill "C:\Users\me\Downloads\" & wb.Name

If the error persists, then I suggest you delete that line, let the macro do the copying for the workbooks and then manually delete the files using the File Explorer to access the file names. You can do that pretty quickly by selecting the file names holding down the Ctrl key and then right click and click 'Delete'.

 

[JLGWhiz]

I don't know what is causing the command to not execute properly. The syntax is:
Kill (Full path and file name)

If wb is declared as Workbook and set to the open workbook (which it does in Post #2 ) then wb.FullName should produce the full path and file name. Then the path used to open the workbook concatenated to the wb.Name should also produce the full path and file name. so without being able to work with the actual file, I can't offer much more on how to do it.

 

[jconkl02]

JLGWhiz,

I can't figure it out either. I have submitted another post about what I'm trying to do. I'm wondering if Saving the "report" files with one of two known generic names (ie. Data1.xlsx and Data2.xlsx) and then deleting ALL .xls workbooks that begin with the word "report" is a workaround. I mentioned you in the new post and I really appreciate you trying to help me. You were the only one that tried even though 150 people viewed my post. Thank you very much.

 

[JLGWhiz]

JLGWhiz,

I can't figure it out either. I have submitted another post about what I'm trying to do. I'm wondering if Saving the "report" files with one of two known generic names (ie. Data1.xlsx and Data2.xlsx) and then deleting ALL .xls workbooks that begin with the word "report" is a workaround. I mentioned you in the new post and I really appreciate you trying to help me. You were the only one that tried even though 150 people viewed my post. Thank you very much.

Glad to help, sorry I couldn't do more.
regards, JLG