Hi, for create table activity, how do I efficiently select a range of data if I only know the top left cell?
I.e. I do not know where the bottom right cell is
Hi.
Maybe I’m just a newbie, but I didn’t even know there is a Create Table activity.
I suggest you instead use the Excel Read Range activity. That is Excel Scope with a Read Range inside. You can also set the Excel scope as Read-only and not visible in the properties.
The Read Range allows you to leave the range as the default and it will create a table variable with all the data.
Regards.
Hi, I am not referring to DataTable variable, I am referring to an Excel table… for this I need to specify the range of cells e.g. “A1:H10” if the top left cell is A1 and bottom right cell is H10. Only thing is I only know the A1 cell and I do not know the H10 cell
Oh gotcha.
Do you know how many columns you want for the table?
Don’t know how many columns or how many rows in the table, this varies
Do you have data in the Excel file that you can use a Read Range on? If you get the data table of the data, then you can use its row and column count to generate the range.
How do I efficiently convert the number of columns into the last column index? e.g. AC1, BX1 etc
Also I was hoping if there was a solution like one where I just select the top left cell and it automatically detects the entire range of data…
Right, so if it’s just until Z, then you can simply use Convert.ToChar(dt1.Columns.Count+64) to change it to a letter.
However, if you want to go passed that, you will need to do some calculations with loops and modulo.
Here is one of my old solutions. Let me know if you can’t get that working:
1 Like
If you want to use the Select Range, then you would need to calculate the range from the data table. Another thing you can do though, is use keystrokes in TypeInto to perform the select all data… Ctrl+a
Ok I used Select Range and it picked up a bunch of stuff as extra data that I do not need… possibly some extra formatting, whitespace, etc… the range extended way beyond the table itself… how do I remove the extra data?
The Select Range activity will require the exact range I believe. You might have an easier time selecting the range with data by using TypeInto with Ctrl+a ( "[d(rctrl)]a[u(rctrl)]" )
I should also mention that using the Invoke VBA activity is an option if you know a little vba syntax, which works inside an Excel Scope.
Sorry, i used Read Range, specified “A1” and it picked up the extra data
The Read Range should only pick up the data. Did you use the Workbook Read Range or the Excel Read Range?
I used Excel Read Range from the App Integration menu
Can you try something? Open the Excel file and highlight all the empty rows below the data (highlight row below last row, then press Ctrl+down). With all the empty rows selected, go to the Home ribbon at top, and go to the Clear menu on the right side and choose “Clear All”.
Then, try the Read Range. I’m just wondering if there is extra hidden data that you don’t know about.
Would preferably not do this because the workflow is operated by a client and they are supposed to run this workflow against the data which they receive and I wouldn’t want to complicate things
You should probably create a copy as a sample file to develop with. I was just trying to rule out there there is actually data that you don’t know about on the file, but understand if you don’t want to mess with it. If you create a copy, then there won’t be any issues.
I’m also currently not on my machine with Studio, so I can’t really mess with things on here. If you want you can also provide some screenshots of your activities, and maybe I can help identify any other problems.
If not today, I can get back to you tomorrow again.
The Ctrl+A method works to select the range of data, how do I extract the range string from here?
For the Ctrl+a method to work, you would need to probably create the table also with keystrokes. I looked it up on Google and it says Ctrl+T or Ctrl+L.
I’m not quite sure how to get the range of a selection, unless you use Invoke VBA. But, like I said, you can create a table with keystrokes too.
Anil_G
sallavudeen_ashwin
Sachin_Jadhav