May 18 2022 03:07 PM
May 18 2022 03:07 PM
I am going preface this with a warning that I am clueless.
First an overview of what I am trying to accomplish.
This is in Excel 365
I have a source sheet that is generated by an application once/week. The report contains both data that has changed since last run and new data. The file name changes each time that it is run, the file is then deleted so no formulas can be stored in it. I do maintain a personal.xlsb file with vba.
Source and destination sheets are formatted the same.
Data in the source will have new rows that should be appended to the destination
Data in the source will have existing data that will have changed and should overwrite the destination sheet. Column A is the key for matching, this cell does not change.
At the moment, I am using =MATCH(A:A,report1652227282759.xlsx!$A:$A,0) in the last column on every row of the destination sheet to locate the matching (changed) rows on the source sheet. The then filter out the #N/A cells and copy the entire contents of the source to the destination.
My goal is to automate this.
1) Modify the formula so that the report name is whatever the source file is when I am running it. (report####.xls) dim ws as worksheet set ws=thisworkbook.activesheet ?
2) Copy the formula to every row on the destination each time I run the report without manually selecting all rows and using CTRL+D.
3) Filter out #N/A on the Destination (Daily cases.XLS)
4) copy the contents of the source XLS to the destination XLS.
I hope this make sense to you much smarter than me Excel folks.
I am wide open to scraping the entire approach if there is a better way.
File samples are attached.
May 18 2022 07:14 PM
In the attached, you will find two buttons (look at the range O1:S1) called "Match Cases With Report File" and "Show All Data".
If you click the button called "Match Cases With Report File", it will open a file picker dialogbox for you, please browse and select your report file and click open to continue. The code will then do the rest job and filter column M to show you the matching row# from the report file.
To see all the data again, just click on the Show All Data button.
Code for Module1:
Option Explicit Sub MatchCaseNumbersWithReportFile() Dim dwb As Workbook Dim swb As Workbook Dim dws As Worksheet Dim sws As Worksheet Dim srcRepotPath As String Dim lr As Long Dim dict As Object Dim x As Variant Dim y As Variant Dim i As Long Dim j As Long Dim arrOut() As Variant Application.ScreenUpdating = False Set dwb = ThisWorkbook Set dws = dwb.Worksheets("Cases") Set dict = CreateObject("Scripting.Dictionary") With Application.FileDialog(msoFileDialogFilePicker) .Title = "Select a Report File to Match CaseNumbers" .AllowMultiSelect = False .Filters.Clear .Filters.Add "Excel Files", "*.xls*" If .Show = -1 Then srcRepotPath = .SelectedItems(1) Else MsgBox "You didn't select any Report File!", vbCritical Exit Sub End If End With Set swb = Workbooks.Open(srcRepotPath, False) Set sws = swb.Worksheets(1) x = sws.Range("A1").CurrentRegion.Value For i = 2 To UBound(x, 1) dict.Item(x(i, 1)) = i Next i swb.Close False If dws.FilterMode Then dws.ShowAllData y = dws.Range("A1").CurrentRegion.Value ReDim arrOut(1 To UBound(y, 1) - 1, 1 To 1) For i = 2 To UBound(y, 1) j = j + 1 If dict.exists(y(i, 1)) Then arrOut(j, 1) = dict(y(i, 1)) Else arrOut(j, 1) = "" End If Next i dws.Range("M2").Resize(j, 1).Value = arrOut With dws.Range("A1:M1") .AutoFilter field:=13, Criteria1:="<>" End With Application.ScreenUpdating = True End Sub Sub ShowAllData() If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End Sub
May 19 2022 11:05 AM