VBA for merge files from a location and pulling data based on a criteria

When you say you don't want to copy the highlighted text, does that mean you don't want the cell value in X not copied over or you don't want that whole row, A to X, not copied over?

Dear Sir,

We need the complete rows data from column A to X except the highlighted cells values in column X so while running the script we expect to exclude the rows from column A to X based on column X highlighted values. Currently, i have provided the data for the current month so the same process will continue for upcoming months.

I have highlighted column X values manually so we expect to filter column X values and exclude the complete row based on highlighted values in column X and not by color.

Hope i have clarified now. Please feel free to revert if there is any further clarification if required. Thank you!!!

Regards,

Neilesh

In this process, the code asks to select the folder with the files you want processed. The code will process all the files in that directory as long as the name of the file has the same month in the name. Here is the code.

Please Login or Register  to view this content.

Dear Manicab expert, Provided script are merging the workbooks but challenge is still there with filters. It is not exluding the below lines from column X wherever these value appears.

1) Blended item variance due to disposition on contract; should clear at month end when earnings are ran in Infolease
2) New write-down in the current month, the blended items income/tax method was flipped to “FLOW” thru the nightly bash and the unearned blended net investment will flush to the P&L at month end when earnings are ran which will clear this recon variance
3) Prior partial writedown now has $0 net investment so writedown UDF has been fixed to “F” by EOT, unearned blended items will be accelerated by Infolease at month end when income job is run as blended income method is being bashed to change to “FLOW” on “F
4) Stat SSR file is only sent once on close day 1 for month end balances, due to the way OBIEE V4 works if Infolease does not send a Stat file daily the Stat CAAS will show as a variance on BD1 – BD5 of current month recon until BD5 when the reversing Stat file is sent (GL is correct but sub system shows as $0 because OBIEE overrides sub-system to $0 since no daily Stat GL feed is sent from IL to ledger on BD1 - BD4, month end final recon had no stat variance so this is not a "real" variance, only concerned with Stat variances after BD5 SSR recon)
5) Pending enhancement #98165 Write-down NLC Offset
6) Disposed prior write-down, finance income recovery already previously recognized need to reverse gain on term
7) Deleted OLCB Renewal
8) Contract number change assumption
9) Defect 6837 CB Renewal in Nonaccrual Offset
10) Defect 9085 OLCB Renewal Booking Nonaccrual Offset
11) Disposition Reversal, NLC manually re-built by Operations, offsetting entry needed to equipment funding GL account
12) Payment posted on writedown contract, belongs to finance income recovery that needs re-classed topside

So we need to exclude all the rows based on the above 12 values if they appears in column X. Can you please help me on the same. Thank you!!!

Regards,

Neilesh

I have highlighted rows manually to identify the excluding items but every time we will not be in a position to highlight the excluding items from each workbook.

or is it possible if we create a new tab for excluding the lines and paste these 12 excluding criterias in that tab and based on the list script filter and provide us the output. This will also do for me.

Placing these items in a tab is the best solution. I’ll update code to look for these items and exclude lines with them in them.

Here are the updates you requested. I'm also including the file so you can start using the Exclude tab and update that as you need. I am using the text in Column A of the Open Item tab to exclude duplicates.

Please Login or Register  to view this content.

Dear Expert maniacb, While running the script, I am facing the script error "Invalid procedure call or argument 5". Can you please look into the same and fix. Thank you!!!

When I run the script, I can not duplicate the error. Please let me know which line is highlighted with the error. Place an asterisk on the far left of line "On Error GoTo Trap" so it looks like "'On Error GoTo Trap" without the quotes. This will turn off the error trapping and allow you to see what line errors out.

Hi Maniacb, Sorry for the delayed reply as i was on official trip. I am facing challenges from Ln 37 col 20 till Ln 40 col 17. after Ln 40 col 17 the script jumping back to Ln 37 col 20.

Originally Posted by Neilesh Kumar

I have a folder (Location: C:\Users\Neilesh\Documents\Destop Files\Recon\Daily Working Recon\2023\04 Apr 2023) and under 04 Apr 2023 folder I have created Daily Recon Excel files for April 2023, based on each day until now as name 04.01.2023 Recon, 04.02.2023 Recon, 04.03.2023 Recon, 04.04.2023 Recon, and so on.

After getting the expected outcome as merged files except the highlighted cell values we expect to save the file automatically at C:\Users\Neilesh\Documents\Destop Files as "Merged file Apr 2023".

try

Please Login or Register  to view this content.

If the folder is located on network, try replace "test" with the below while "Function GetList()" should be kept intact.

Please Login or Register  to view this content.

Then copy the files onto your local folder.
And the function must be kept as it is.

Sure Dear expert let me try by running the script by copying the files onto local folder. Thank you

1) Just delete that line.(code in #23 edited)
2) once you created the Merged File for 04, change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

You must tell me when you run the code for what month?
The code creates for current month, so it only deal with 05 May if you run in May.

so i can not run the script for April month in May?

If you always run for the previous month then change to

Please Login or Register  to view this content.

Dear Expert Jindon, i have tried but still the outcome is with blank workbook. Please find here attached few sample recon workbooks and provided script for testing. Please create a folder at Desktop as 2023 under 2023 folder please creat 04 Apr folder and under 04 Apr folder please save these sample workbooks and try running the script which is giving blank workbook as outcome.

Regards,

Neielsh

Why are you changing code the and the files?

This is my last warning.
If you think you are able to adjust the code, do it for yourself.

Dear Expert, I haven't changed the files, and based on the system I am currently using I had to change the location path for the folder so I have changed.

Dear Expert, my intention is only to get the results based on script so when it was not giving the outcome then i tried by changing the names in the script as well changing the file names but then also it has not provided the outcome so requested to help.

Use the code in post #23 and change one line as I mentioned in post #30, so that the code always look for previous month folder.

Open attached and place the folder 2023 onto your desk top and run the code.
Merge file will be created on desk top.

Dear Expert Jindon, Script is working but the only challenge is with the spreadsheet which is getting created as completely blank so after running the script the new workbook is getting created and save on the desktop which is completely blank.

Please try running with the provided VBA script as attached.

Hopeles...
I said

Originally Posted by jindon

Use the code in post #23 and change one line as I mentioned in post #30, so that the code always look for previous month folder.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Set back to Date - Day(Date)

Thank you so much Dear Expert Jindon and apologies for bothering a lot. Once again thank you so much.