Browse to Folder to complete formulas

[jomili]

Almost daily I get invoices from vendors about services they've performed for our caseworkers. The vendor sends me the CW name (somewhat; it's misspelled, or only the first name, or only the last, etc) and the employee ID (also problematic; supposed to be 11 digits, I get anywhere between 0 and 15). For each invoice there are usually 200 plus lines of expenses, sometimes MANY more.

We have 17K CWs. Our IT dept creates an Active Position Report (APR) ALMOST every day that lists each employee and all the pertinent information about each one. I use the APR from the date of the expense to look up the Dept, Program, and other coding elements so we can charge each expense to the right bucket.

Each APR is stored in the APR folder, then under the Fiscal Year, then under the month of the FY. The name of the APR is pretty static, other than that it has the date in the file name, so each APR is different.

Since there's a lot of setup to be done for each invoice, I created the macro below to help me out with it. It's a big help, but one part is big pain.

I have the path to the APR hardcoded in (see section in red). So, since very seldom do I need the same APR more than a few times, I have to constantly update my macro or alter the formulas after they're desposited.

What I'd like is to be able to browse to the folder and select the APR I need, and have the formulas adjust to use my selected APR.

You'd think with all the Covid-19 time I'd be able to work through it, but I'm staying pretty busy so haven't had the opportunity to tackle it, so hoping some kind soul will help me out on this. Any help is appreciated.

Thanks!

Please Login or Register  to view this content.

[harrisonland]

Try popping this at some point before you need the variables, then you can substitute the file paths in the vlookup formulae with them.

Please Login or Register  to view this content.

Now your your vlookup formulae can look something like this (untested, so might need tweaking):

Please Login or Register  to view this content.

Like I said, untested, but can be a starting point.

Tim

[dangelor]

Possibly...

Please Login or Register  to view this content.

[jomili]

Thanks guys. I'll play with both of these today and try to get back with you later this afternoon. Thanks for the ideas!

[jomili]

Harrisonland,
Your pseudo code worked flawlessly. It was close to the code I was cobbling together, but I was going the route of replacing "\FY" with "\[FY" and ".xlsx" with "xlsx] to get the formula right. Breaking up the strFilePathName into the two components is a much smarter way, so that's what I ran with.

Dangelor,
I gave your code a spin. I did a Debug.Print of the sFileName, which showed this:

Please Login or Register  to view this content.

, so in the formulas for DeptID and PAC the second bracket was added appropriately, but no first bracket was added. so the formula was invalid.

Thanks to both of you for helping me through this. This makes my work much easier!

[dangelor]

Thanks for letting me know. Glad you got a solution! And thanks for the rep!

[harrisonland]

Glad you got it worked out - and thanks for the rep.

Tim