So I have a report that needs formatting modifications and one big modification is deleting unnecessary rows that have continued titles and no useful data in that row. This excel file is later imported into another application to be parsed, so the formatting is crucial if we want the file to go through in the process.
What i want the program to say is: For each row; if column B has any string and column H is an empty field, then delete the row. Hence deleting the row that has the continued text with no data in the bold column.
I’ve been looking around threads trying to find something similar but haven’t had an luck. Screenshot shows an example of the spreadsheet
Instead of using the column letter you need to use the column index. The first column is 0, so B will be 1. It should look like row(1).ToString.Trim="" as your condition
The reason I am using row is because that is what your For each is referencing to represent each row in the table that you are looping through. Then, you need to place the column index or the column name (if you use AddHeaders in your Read Range) to reference the item in the row. Finally, include .ToString.Trim to remove all end spaces and the row item will be an object so you need .ToString to look at it as a string.
That should be a good start if you implement those changes.
The other thing I will mention is once you have a new set of data and you use “Write Range” to ouput your new table, all the formatting is maintained in the Excel file to which you are outputting the table to. This will bring some issues, because it does not remove all the existing data in the Excel file, so you would need to either clear the file with “Clear All” or output the table to an empty file.
If you can code all the formatting so the process can work on its own and not need to rely on other sources to be correct, that would be ideal, however complicated. The more simpler solution would be to provide a template with all the formatting set in Conditional Formatting, therefore when you Write Range your table in, the formatting changes based on the data that is in the spreadsheet… either that or just have it pre-formatted but only if the formatting is static, like only specific columns are always bold or colored.
Yeah, it’s possible.
If you are wanting to use a VBA Macro that is in Excel, you would need a .XLSM or .XLSB file that is macro enabled basically. This will require another thing to maintain and rely on, so I don’t normally recommend doing it this way. But is still a viable solution if Excel VBA is in your comfort zone… you can execute it with the Execute Macro activity.
An alternate way to do this is using the Invoke VBA activity. This is actually closer to VBScript plus there are some differences in getting the code to validate and run I have found. In this case, the code would be built as part of your workflow so you wouldn’t need to rely on an external Excel source to get the macro.
Also, if you have VBScripting experience, you can simply create a .vbs file and call it using Start Process or Open Application. You would want your workflow and script to communicate together though, like for the filename and whatnot.
ashokkarale
Anil_G
Ruban_shanmugam
Lalit_Chaudhari
eyashb
sonaliaggarwal47
PWilliams
AzeemK
Juan_Hkahfi