Activity: UiPath.Excel.Activities.Business.ExcelApplicationCard (from the miriad of different Excel-related activies)
Original locale: Catalan
Affected locale: Spanish
The problem arised because the Studio project is being run in machines with different locales.
Originally, the script was developed using the Catalan locale.
The script creates a new Excel file, adds a spreadsheet (or “tab”) in the file called “Full1” (which translates to “Sheet1”) and then fills its contents.
When ran in a different machine with a Spanish locale, everything is the same but we noticed there’s also an empty, first one “tab” or spreadsheet, with no contents, called “Hoja1” (which translates to “Sheet1”) prior to “Full1”.
This is a machine setting. In Excel you can set the defaults for a new workbook, for example you can change the default number of worksheets (typically 1 or 3) and the name of them I think is also set there or via the language settings on the computer.
Sorry guys; I was in a hurry and didn’t give all the proper information.
The issue is that in the Catalan locale machine I create that “Full1” spreadsheet and all the contents are written there.
Instead, when we run it on the other locale’s machine, a blank, empty “Hoja1” is created on its own, and then also the “Full1” that I explicitly created via the Excel activities. All contents are written in that sheet, and the “Hoja1” that came out of nowhere is kept blank in there.
So my assumption is that this activity is always creating one blank spreadsheet, which has the default name (according to the machine’s locale). By mere chance, the one I explicitly created in the Catalan locale has the same name as that default one, thus I don’t get an extra, empty spreadsheet belonging to the default one.
Instead, when in the Spanish locale, as the default one has a different name, I end up with two tabs.
I want to get rid of the defaultly created one, which is not trivial as it may seem (in fact I already lost 2 hours searching and writing in the forum). I can’t find a setting in the activity to control if that default spreadsheet has to be created or not (which would solve my problem inmediately). I will probably have to check explicitly if the blank tab exists and delete it. But that’s not straightforward, as it dependes on every locale, and what if it coincides with the name of the one I’m creating explicitly and that all.
This is so frustrating, because I’m creating my tabs manually. But the Excel file will end up always having thie “Sheet1” no matter what I do.
I’m trying the “Delete sheet activity” (Activities - Delete Sheet (uipath.com)) to get rid of that non-requested inserted sheet by trying to access it by index, but such a trivial task is becoming a nightmare. I don’t see any “Sheets” collection being displayed by the “intellisense” when accession the Excel file reference. There’s an “Excel.SelectedSheet”, but it complains about the sheet not being selected, and I don’t know either how to select it, plus if I don’t know its name I cannot select it, either… There’s an Excel.Sheet.Item but, again, if I don’t know the name of the inserted sheet…
The only way for me was to loop over the spreadsheets and delete the 1st of them all.
Which is a “solution” I hate, as nobody guarantees that the loop is sorted from oldest to newest tabs, neither that they are not going to change this behaviour in future versions (and, thus, breaking functionality). Oh well.
Also I tried doing this BEFORE inserting the ones I do manually, so there’s only one item to iterate amongst and this way assure there are not going to be any unwanted spreadsheets affected. But, unfortunately, when you try to delete the one and only spreadsheet in the workbook, it will throw an error about at least one is required (which is silly in my opinion as long as you are not doing anything else yet that needs a spreadsheet, like saving or adding cells).
I’d still be very happy if someone suggests a better method for straightly accessing the 1st spreadsheet.
So, when a new workbook is created there has to be at least 1 tab present, and the name of that tab is localized. Depending on different settings in Excel the number of tabs will be different and the name will be different.
Usually, in RPA, you want to have good control and consistency on your UiPath machines, ensuring they all have the same default settings in applications and regional settings.
If you don’t have that, you will of course have quite alot of headaches, as you see, irrespective of what software you use to generate this Excel.
So, how to solve it?
Well what I usually do is have a template file rather than make one from scratch, that way I am in control of it.
If I don’t have a template and indeed want to make from scratch then I will firstly create myself a worksheet with the name I want, since you must always have at least one worksheet, I then would loop over the existing worksheets collection so I can dynamically handle any number or name, and then I would delete all the ‘default’ worksheets.
Of course, I agree that, in RPA and in almost any other environment, you want to have a good control and consistency of the machines. That said, if you meant all of them have to be using the same locale, etc., so I can access the tab by an “invariable” name, I disagree with you. Not only because this is (my organization) a multi-language environment and it must support 3 language configurations, but also because hard-coding a value to rely on is not a good practice, programming-side talking. For instance, in next Excel update they could change the name of the default new tab freom “Sheet1” to “NewSheet” or whatever. You get the point, no?
Using a template is something I didn’t consider because the resulting Excel file is simple enough that I also prefer to simplify it further by avoiding an extra file areound specially when I don’t need it. Neater to just fill the cells with the values I need and that’s all.
The point of my script is to generate hundreds of Excel files, thus what you suggested of “creating a worksheet” yourself is of no use in my case, as, of course, one of the purposes is to avoid having to create hundreds of new files by hand. If you mean inside of the script, that’s what I’m already doing; also creating my own tab, but not because the program need at least one worsheet, but because I needed it and with a certain name. As I already explained, the program is nevertheless creating the tab even if you didn’t do it explicitly, and even if you did it explicitly. Deleting the “default worksheet” (only one, as there’s no more than that one created automatically) is what I’m trying to accomplish from the beginning, and I already explained how I’m doing it, but I don’t think it’s accomplished in an elegant way because of the issues I commented.
This issue is not new, you could go back decades and read VBA forums talking about Excel Macro’s having the same problems (believe me, at one time I did alot of VBA some decade ago or so), you can blame Microsoft for these ‘issues’ and I doubt it will change.
I do not see what is unelegant about the solution I propose?
Make your new worksheet, of which you know the name.
Get the collection of existing worksheet names, remove/filter the new worksheet from that collection and loop over the collection to delete the existing worksheets.
Clean, consistent, simple.
If you aren’t satisified and insist of a solution where you can create a new Excel file with the number of worksheets you desire and the names you desire, or enable it so you can have zero worksheets then the UiPath forums are not the correct place. You need to go to a Microsoft Excel forum and request Microsoft change their product as that is not a functionality they provide, so also impossible to do via any RPA software.
(believe me, at one time I did alot of VBA some decade ago or so)
We are (or we were) on the same boat; I experienced that also (and more or less by the same time… )
you can blame Microsoft for these ‘issues’ and I doubt it will change.
I had a recent experience related to the issues I had with Powershell (I flooded the forum with messages about that) and as a side effect I discovered a bug in one command that was supposed to give you exact results when searching for Microsoft Teams teams by team name. It was supposed to give you the results based on an EXACT MATCH of the string you provided, as per the documentation. After months of using that funcionality, we noticed some erratic behaviour. In the end, after heavy testing and some other evidence on the internet, we found out that it was the Microsoft command that was not giving exact match results, but partial, and not only that but in the worst way - if your string started like a group name it would give a positive, even if both ended differently. I opened a bug report, they eventually closed it as “wont fix” and neither the docs were changed to reflect the proper behaviour.
I do not see what is unelegant about the solution I propose?
Make your new worksheet, of which you know the name.
Get the collection of existing worksheet names, remove/filter the new worksheet from that collection and loop over the collection to delete the existing worksheets.
Clean, consistent, simple.
Yeah, you are right. Yesterday I got it in a different way, probably as what I was already doing. I didn’t get the “filtering” part. You are specifically comparing the names and removing the ones that you haven’t created. That’s ok. Probably I’ll implement that when our script starts throwing errors when I realize the loop doesn’t go in the order I took for granted
An Excel file has to have at least one worksheet, and you can control the default name and number of sheets in the Excel settings.
Please leave it consistent with how a human opening a new worksheet behaves, both in the names of the sheet and number (as in it does whatever is default in Excel).
The existence of Sheet 1 is a simple part of the excel - new document constructor. It’ll be the same if you create a new workbook from scratch.
If you use the classic excel activities you can delete it by directly refering to the name. (I assume the workbook must maintain at least 1 valid sheet though).
Somehow UiPath in all their wisdom shielded the interop workbook object in the modern activities and exposed a poor substitute instead. (No clue why they actively eliminate most excel features this way).
If you want to delete a sheet by name you can iterate through them of course, by using the ‘for each sheet’ activity, then compare the sheet.name value with your (un)desired sheetname and then delete it.
But as with most of the ‘modern design experience’ things got more limited and/or more complex compared to the classic methods.
Yeah, I agree with the mates. It’s not probably a bug and there’s a lot other issues that need more attention than this.
Regarding how to control the default name of the new sheet, I haven’t tried or investigated it; I don’t know if I have to set it up on Excel independently of UiPath and I haven’t found any settings in the activity for this, but anyway if I explicitly create one at first with a given name, I simply can delete whatever other exists in the workbook that doesn’t have that name.
It would be nice to access sheets by index (I asume by chronological order of creation), thought.
)
