How to use macro to generate files based on conditions?

Hi all,

Hope all are doing good.

Not sure if this is complex and challenging, but i will try to explain in full detail.

So i need help with a macro which will be placed in personal workbook so that it can be run in any file.

To explain briefly at first, there is 1 source workbook, 3 target workbooks and based on these aforementioned files, 2 destination workbooks need to be generated

The data is to be considered from source workbook, then find its relevant data in multiple target workbooks (wherever the data is present) and finally give the result in 2 destination workbooks in CSV format.

Source workbook and target workbooks will be open before macro is run. Macro will be run in source workbook. Finally 2 destination workbooks need to be created in CSV format and automatically save with a name followed by current date in a destination folder.

Source workbook =

Extract

Target Workbooks =

EMVS Alerts Log - CEE 2024
EMVS Alerts Log - DACH 2024 and
EMVS Alerts Log - Benelux 2024

Destination workbooks (CSV) =

import_alert_status_change_template (10-03-2024)

import_investigation_status_change_template (10-03-2024)


The value to be searched in file Extract is starting from row 2, column 1, first text up to semicolon i.e. "DE-92195aa3-e4e0-4a5d-91ea-60fc4c67af80"

This value need to be searched under column AE (UNIQUE_ALERT_ID) in target workbooks. Against this Alert ID, based on some column values, 2 CSV files need to be created automatically.


Also, the oldest date in file "Extract", which is after the Alert ID (between 1st and 2nd semicolon) shall be considered while searching such that the macro should start searching in target files one day before this date.

Example: If the oldest date in file Extract is 2nd March 2024, the macro should start searching from 1st March 2024 in target files under column A.

However, one issue here is that date format in file Extract is MM/DD/YYYY but in target files will be DD/MM/YYYY.


1st CSV file:

This shall always contain "Alert ID;Investigation Status" in row 1, column 1.

From row 2, it shall contain the alert IDs followed by either "Root cause on my side" or "No root cause on my side"

"Root cause on my side" or "No root cause on my side" will be populated by value under column AK in target workbooks.

If its GSK, then "Root cause on my side",

if its Non-GSK, then "No root cause on my side"

Example:

Alert ID in extract "DE-92195aa3-e4e0-4a5d-91ea-60fc4c67af80" in file "EMVS Alerts Log - DACH 2024" has "Non-GSK"under column AK. Hence the value in CSV file should be

DE-92195aa3-e4e0-4a5d-91ea-60fc4c67af80;No root cause on my side

Similarly for other Alert IDs.

Now this file should be saved as "import_investigation_status_change_template" followed by current date and saved in Downloads location of This PC.
Example: import_investigation_status_change_template (10-03-2024)


2nd CSV file:

This shall always contain "Alert ID;Status;Reason code;Custom reason" in row 1, column 1.

From row 2, it shall contain the alert IDs followed by "Under Investigation" and "RC-001" always, separated by semicolon.

Finally, it will contain the value under column AI in target workbooks against the Alert ID.

Example:

Alert ID in extract "DE-92195aa3-e4e0-4a5d-91ea-60fc4c67af80" in file "EMVS Alerts Log - DACH 2024" has "Pack active in ATTP, decommissioned elsewhere" under column AI. Hence this value in CSV file should be

DE-92195aa3-e4e0-4a5d-91ea-60fc4c67af80;Under Investigation;RC-001;Pack active in ATTP, decommissioned elsewhere

Similarly for other Alert IDs.

Now this file should be saved as "import_alert_status_change_template" followed by current date and saved in Downloads location of This PC.

Example: import_alert_status_change_template (10-03-2024)


The target workbooks contain column by name AMS Status under column AZ. Once an alert ID from file extract was found in target workbooks, then this column should be populated as Uploaded.


Finally, the rows 2 to 26 in file extract were found in the target workbooks and have value as Uploaded in target files under column AZ. So these rows should be deleted while others should remain in the file.

Also, from next time, when the macro is run, if the Alert ID in file "Extract" contains "Uploaded" in target file , then that row same shall be deleted from file Extract and others should be searched.


Note:

1. All target workbooks shall be open before running macro. If any of the workbook is not open, it should give a pop-up with the file name that is not open such as "EMVS Alerts Log - CEE 2024 is not open"

2. Target workbooks will always have same file names and sheet names. Sheets will always be password protected with "abc123"

3. In actual, there are 8 target workbooks. So i will add them in macro later on.


A query:

The target workbooks are placed on teams/sharepoint. Is it possible that if the link is entered in macro, then the macro should automatically search the data within them without the need of keeping all these files open.


Can someone please help with the macro?

Originally Posted by rizwanulhasan

the requirement is short i guess

The requirement is not short. This would require a lot of work and it looks to me like you are having a hard time finding someone willing to do it for free.

You may want to consider offering payment in our Commercial Services forum.

Open new workbook and paste the code then save it as .xlsm file.
run test

Please Login or Register  to view this content.

Done. Modified beginning part of macro as below

Please Login or Register  to view this content.

Ran macro but now nothing happens

Code in #6 has been updated.
Now it overwrites "Extract.csv", so you'd better make copy of it before you run.

As I haven't used "Team", no idea about the file path etc.

In actual, the rows that were found in target files should be deleted and the ones which were not found shall be retained. Looks like its working opposite.

Also, instead of giving path for extract file, cant it be run directly in active workbook. Because as mentioned in post # 1, the macro will be run in source file i.e. Extract.csv because this extract can be in any folder. So why not run code directly in active workbook rather than changing path each time.

Below requirement not working yet

The target workbooks contain column by name AMS Status under column AZ. Once an alert ID from file extract was found in target workbooks, then this column should be populated as Uploaded.

Also, from next time, when the macro is run, if the Alert ID in file "Extract" contains "Uploaded" in target file , then that row same shall be deleted from file Extract and others should be searched.

Originally Posted by rizwanulhasan

Hi jindon,

Thanks again for you efforts and response.

This macro works differently than previous one. Here i need to keep target workbooks open. The data is fetched and the target workbooks are closed which is good by the way. However, below issues noted.

1. Current: Once the macro is run, open dialog box pops up asking to select the file.
Required: The macro should run in active workbook.

2. The destination workbooks cannot be found in Downloads folder

Ok, now i got it why destination files are not generating. Its because instead of deleting rows in file extract, the macro is deleting the rows in target files. The Alert IDs which are present in file extract are being deleted in target files. Hence the macro generates destination files only once and not the next time.


@jindon

Can you please check this issue and the points in post # 22 and 23

Administrative Note:



We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)

Hi Alan, Thanks for calling this out. I did provide a link in other forum to the original post rather than creating a new one, however, i missed to inform here. Apologies for the same.

Why not also post the links here!

Like Jeff pointed out, this is a free forum outside of the paid commercial services. Posting over and over again for somebody to help is not going to help especially if that person is not available at this moment. Again, being this is free, be patient or please pay for some help in the commercial services.

Or instead of deleting rows, can it be possible that another CSV file be created (Pending.CSV) for the alert IDs that are present in Extract file but not found in target files