How to close specific Excel spreadsheet without also closing my input file - SAP auto opens file

[MikeM]

Ok, so I had this working fine, then I tried to add some info to the filename - that broke the whole thing and now I can’t get it to work again, GRR!!

The challenge:
SAP project to download a bunch of reports. Reports are listed in an excel spreadsheet - my “InputFile.xlsx”. So I type into in SAP, the value from the first column in the “Input file” is the file I want to download. Let’s call it “DownloadedFile”. In SAP, I click the “Export as Spreadsheet” icon and this allows me to type where I want the file saved and the name of the file, so I reuse the name from the spreadsheet and save the file called “DownloadedFile1” and click Save/Replace.

HERE’S THE PROBLEM
In addition to saving the file, SAP auto-opens the spreadsheet. I need to be able to close that spreadsheet before moving to the next download.

I HAD it working!!!

Here’s what I did - and this worked 99% well for over 100 files. But, as developers do, I went back after that 1%, and added a more descriptive name, and the whole thing broke and I haven’t been able to replicate what I did the first time. It makes NO sense. This SHOULD work, but for some reason, now it keeps closing my Input file too. So obviously, when the next step logs the results back to the Input File that is no longer open, it fails.

Can you help me?! Is there another way to close the file that I can try that won’t also close my InputFile.xlsx?

Mike

PS - Using StudioX 2020.10.2 (but we’re very close to being ready to upgrade to 2021.10.5 enterprise wide)

[Nithinkrishna]

Hey @MikeM

You need to just confirm that the downloaded file variable of your has the right value ?

And when you say it not works, Is it throwing some error or ?

Thanks
#nK

[MikeM]

The error occurs on the next activity when I do a write cell back to the Input file.

It errors because InputFile.xlsx is no longer open (and I need that file open so I can keep looping through each row - or do I? Can I instead just read that file into a Datatable? I’m not sure StudioX can do that, can it?)

I tried to hard-code the actual file name of the file I want to close (instead of using a dynamic selector) and it killed all Excel files still. So I don’t believe it has to do with the variable value. I added a WriteLine activity to output the variable and it DOES match the file name. But here’s the variable in the advanced editor so you can see:

DownloadedFile = CurrentRow.ByField(“Customer #”).ToString + “_” + System.Text.RegularExpressions.Regex.replace(CurrentRow.ByField(“WBS”),“/”,“”) + “.XLSX”

[MikeM]

I believe I have found a way around my problem - Here’s how I solved:

Added Use Excel file activity and a “click” the close X button. That’s it and it seems to work by closing only the one file and leaving my Input file open. Maybe not the most elegant way, but it works…