Making an Excel Macro function across two open workbooks with changing file names.

[nnhood]

Hello,

I do IT for a health screening company. Basically a data review person will download a list of people who participated in a health screening event. This file lists all the people that attended and their personal information. This file name changes.

The second file is almost identical but is generated by our health screeners from our own system. This file name also changes.

We do a comparison between these two files and I'm trying to create a macro that will work no matter what the file names are. I'm not a programmer at all, macros are easy, but above that I just don't have that skill set.

So the two files would be open Book1.xls and Book2.xls
In both file we're working with Last Name, First Name, Unique ID columns, but there are many more columns present.

Book1.xls
Add column to right of Unique ID (say Column "J" for this example)
Concatenate Last Name, First Name, Unique ID Columns

Book2.xls
Add two columns to the right of Unique ID (say Columns "I" and "J" for this example)
In first column, Concatenate Last Name, First Name, Unique ID Columns (same as in Book1)
In second column, Vlookup on first cell to the left which is the concatenated value, then highlight Column "J" in Book1.xls, then add comma 1, then add false.

=VLOOKUP(E2,[Book2.xlsx]Sh<wbr>eet1!$J:$J<wbr>,1,FALSE)

It's just comparing the columns for accuracy.
I can make macro work, but then of course the file names change and that's the end of the story.

Just trying to make someones life easier as they do this all day long with different files.

Thanks for any help,
Matt

 

[JLGWhiz]

the obvious answer is to use variables to represent the workbooks and sheets. If the file names are randomly assigned, then the workbook variables would have to be assigned based on the order in which the workbooks are opened, using index numbers, i.e. Workbooks(1) and Workbooks(2). Or If the code will always reside in one particular workbook, regardless o f name change, you can make the same object variable equate to ThisWorkbook, e.g.. 'Set ThisWorkbook = wb1'. Then the other workbook can be assigned a different object variable for use in the code. Once you get past the variable assignment for the workbooks, the sheet variables are easy, 'Set sh1 = wb1.Sheets(1): Set sh2 = wb2.Sheets(1)'. etc.

The sheet variables will then also carry the correct workbook, so only the sheet variables need be used throughout the code to do your data manipulation. The workbook variables would, of course, have to be used for saving and closing.

If there is some part of the file names that is consistent, the the files can be opened from the GetOpenFilename dialog box using wildcards, and object variables can be assigned at the time the file is opened.

 

[nnhood]

Thanks, the files do not need to be saved again, just opened to run the quality check.

I believe three characters of each file name is the same.

So I can do this in the Excel macro creation area? Where do I insert the variables? The macros have been created in the Personal Workbook on that machine.

The one file is generated and downloaded from a web portal, the other file is opened from a shared network drive.

Thanks for your help,
Matt

 

[JLGWhiz]

Assume that the three consistent characters in the file name for the downloaded are 'abc'.

Sub t()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, c As Range, fName As Variant
fName = GetOpenFilename("Excel Files (abc*.xl*), abc*.xl*")
Set wb1 = Workbooks.Open(fName)
Set sh1 = wb1.Sheets(1) 'Edit sheet name
fName = GetOpenFilename("Excel Files (abc*.xl*), abc*.xl*")
Set wb2 = Workbooks.Open(fName)
Set sh2 = wb2.Sheets(1) 'Edit sheet name
'Your codee to do stuff here
wb1.Close True
wb2.Close.True
End Sub

What the above code would do.
1. provide the user a dialog box to choose a file to open from all Excel files with 'abc' as the first three characters file name.
2. Open the selected workbook and initialize the object variable for that file.
3. Initialize the object variable for the appropriate sheet in that file. Note the comment in the code.
4. Repeat steps 1- 3 for the second workbook.
5. The code to do the comparison and data manipulation would follow the above action, using the sh1 and sh2 variables for reference to the worksheets in each workbook. It is unnecessary to refer to the file names because they are incompassed in the sheet variables.
6. close each workbook while saving any changes. If the user does not want to save changes, change the 'True' To 'False' on the close statement.
Again, This is not code you can readily run on your files. The location of the three consistent digits was unspecified, so that would need to be edited in the GetOpenFilename statement to make sure the wildcard asterisk is in the correct place and the actual thee characters substituted for the 'abc'. Also, only a place holder is used where your executable code would go.
I hope you can use this to develop your code.
Regards, JLG

 

[Macropod]

Cross-posted at: https://www.excelguru.ca/forums/sho...s-two-open-workbooks-with-changing-file-names
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Macropod]

Also cross-posted at: https://www.experts-exchange.com/qu...-open-workbooks-with-changing-file-names.html