Excel automation with dynamic range

Let’s say I have a folder in my documents called “New Data” in that folder I have an excel file named “Data File”, I’m using an excel process scope and in that scope I use the read range to open file, read file, delete file from “New Data” folder and export file into new folder “Save Data” with a new name. Each time a new file is added to the “New Data” folder. The file name and sheet name change each time. How would I create a process that will read the file and perform the process no matter what? I am using the Excel Process Scope. Help would be appreciated.

Hi @JosiloM ,

  1. in folder “new data” have only file “Data file”, that’s right?
    to get file name , you need have name of folder,
    You can get array files
    Directory.GetFiles(new data)
    for each file in folder to get file name

  2. what’s index of sheet you want to read ?
    index of sheet excel form 0
    you can get sheet name by get work sheet with index

  3. What’s new name you want to change?
    I think you can change base on datetime

You can try my code
File.zip (16.4 KB)

Hope it help,


Use for each file in folder on New Data folder

CurrentFile.FullName will give fullpath of which ever file is inside it , which can be used directly in use excel file/excel application scope


Great, thanks! Is there a way to make a dynamic sheet range as well? I would like it to read any sheet, because the name may very.


If there is only one sheet then in modern you can use Excel.CurrentSheet.Range("YourRange")

In your range you can give only the start cell like A1 or so which ever will be the starting cell and it will read full


What would I put for the range so it will read what ever the sheet name is?


You can put Excel.CurrentSheet

If multiple sheets are then then use for each sheet in Excel



I get an error that says Excel is not declared.


read range should be placed inside use excel file and provide the excel path as currentFile.FullName…

then read range would work,…as of now you did not do that step only


The sheet range is still giving me issues. I will attach my sequence. Looking to make the read range for the sheet dynamic.
Dynamic Range.xaml (12.5 KB)

I could really use help!

Hi @mariahjosilo
Use it like this. As you are already filtering the xlsx in the for each file. You can use CurrentFile.ToString as excle file & Excel.Sheet(“Sheet1”) as range. It will take the whole sheet as range

Also use Excel Application scope & write range when you are saving the datatable in other excel.

Hope This Helps :slight_smile:o

Awesome, everything works except for the Current file in Data Uploads is not being deleted. What should I put in the delete file property? Right now I have CurrentFile.name.

Use CurrentFile.FullName. This will Work for You :slight_smile:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.