I am having an issue similar to this poster’s here: Copy file activity not working correctly. I am trying to copy a header from a template file into another Excel. There are about twenty headers to choose from for each Excel file and about one hundred Excel files to update, which is why I decided to use this activity rather than a VBA.
However, sometimes the BalaReva.Excel.Sheets.CopyToFile activity fails without sending any error message. The header simply is not copied over, although the bot thinks it has been. As a temporary “solution”, I have the bot a) kill Excel right before the copy activity and b) check to see if the header has been copied over afterwards and alert me if it hasn’t so that I know to rerun that Excel file the next day. However, I would prefer to resolve this problem at its source.
Only a couple of Excel files fail to have their headers copied over each month, so it is not necessarily a huge issue, but it is noticeable enough to cause a bit of irritation from the department. This problem has plagued me for 3 months now.
Here are the steps of the process:
- The bot loops through GroupFoldersPath (“O:\DepartmentName\Group Reports”)
- For each Folder in GroupFoldersPath…
- The bot finds GroupInputFoldersPath (Folder.ToString+“\INPUTS”).
- The bot finds the current month’s input file (MonthlyInputExcel) in GroupInputFoldersPath,
- The bot reads the range of MonthlyInputExcel into a data table.
- The bot uses a switch statement to invoke the correct workflow for the current group.
- Each group’s workflow has a different VendorNumberArray. For each item in the VendorNumberArray…
- The bot filters the data table it read earlier by item/vendor number and sums up the costs.
- The bot copies the header over into the OutputFile (GroupFoldersPath+“"+item.ToString+” “+GroupName+”.xlsx") using the BalaReva activity. This is where my issue occurs.
- After copying the correct header over, the bot writes the data table into the OutputFile and adds some unique formatting to the OutputFile (ex. different text sizes and fonts). I did not bother including this in the example I attached.
- The bot repeats steps 8-10 for each item in the VendorNumber Array.
- The bot moves onto the next Folder in GroupFoldersPath and then completes steps 3-11 until the bot has looped through all Folders.
- The job ends.
Any help is appreciated. Thank you!