Update Formatting of Multiple excels Multiple Tab

Question

Hi Everyone, I want to edit the formatting across all tabs of all excel files present at a folder location . Currently all columns are stored as text type but I want to update number and Date formatting wherever possible. Issue is that data that needs to be edited starts from different row numbers in each of the tabs of an excel.
Note- schema of tabs in one excel workbook matches the other excel workbook file.

Example

Tab-1 (data starts from row 16) and the date in column C needs to be edited to date format

Tab-2 (data starts from row 11) and the numbers in column D,E,F needs to be edited to number format

Attaching excels to be as Input and expected output for working/reference purpose.

Usecase_Africa_InputFile.xlsx

Usecase_Canada_InputFile.xlsx

Usecase_Canada_OutputFile.xlsx

Usecase_Africa_OutputFile.xlsx

snigdha25 · Answer

@mceleavey Unfortunately I cannot omit out excel usage here. I am facing issue in dynamically allocating both tab and sheet name to be written at the same time. Any suggestion's here?

mceleavey · Answer

@snigdha25 ,

no problem, my first recommendation would be to stop using Excel.

The second would be to output to ranges in the output tool:

The third, if you need to maintain colours etc in the Excel sheet is to stop using Excel, but if that's not feasible, then use the reporting tools to build the output to match current format.

Alternatively, you can write the data to a hidden sheet which then populates the required cells.

M.

snigdha25 · Answer

mceleavey.It works like a charm. The issue however now I am facing is writing this in an excel file. I do not want to discard the initial rows of each excel and update the format too. Example- after editing the format of rows 16 onwards in first tab I want to write them back in the same excel from row 16 onwards without removing 15 and above rows