Get a part of Excel with index found

[SJL]

Hi,

I want to get only a section of a excel worksheet, but the starting and ending point is different for different files as the line of items differs. I found the row index of the start and end point by looking for the word “JBP” and “JBP total”. How do i get the in between information and put it in another new worksheet?

note: there are merged rows, so the row index i got is not exactly the cell number in excel. They count merged cell eg. 11 + 12 as one

Thanks in advance!

[ClaytonM]

Hey.

Maybe something like this?

Assign: startingIndex = sourceDT.Rows.IndexOf(sourceDT.AsEnumerable.Where(Function(r) String.Join(",",r.ItemArray).ToUpper.Contains("JBP") ).ToArray(0))
Assign: endingIndex = sourceDT.Rows.IndexOf(sourceDT.AsEnumerable.Where(Function(r) String.Join(",",r.ItemArray).ToUpper.Contains("JBP TOTAL") ).ToArray(0))

Assign: newData = sourceDT.AsEnumerable.Skip(startingIndex+1).Take(endingIndex-startingIndex-1).CopyToDataTable

Note: This will not add the headers if there are column names as part of that block. But, they can be added by taking the first row off similarly with .Take(1), then with .ItemArray, perform a ForEach column and rename them with the item in the array… if that makes sense. Then, use .Skip(1) to leave off the first row. Or just don’t use “Add Headers” when you Write the Range to your worksheet.

Hope that helps.

Regards.

[SJL]

“.AsEnumerable is not a member of system.data.datatable”

I’m getting this error, how do i fix this?

Thanks!

[ClaytonM]

Try erasing “.AsEnumerable” and type it again by hand including the period. If that doesn’t fix it, ensure you have latest .NET Framework installed on your machine and also a 2018 or newer version of Studio.