cancel
Showing results for 
Search instead for 
Did you mean: 

Get Worksheet as Collection Offset error - not reading headers

Dawid.Pekala
Level 4
hi there! Does anyone else get this weird error: We are reading a table from Excel a collection - using Excel VBO/Get Worksheet as Collection Offset. The table is located in A5. Now. we experience weird error, where - if we had ANY data (header) above line 5 it will work fine, otherwise it will read the table, taking everything from 1st row (A6-N6) as headers. Which is then useless as we are using dynamic table (no columns are prescribed when the collection is initialised).. Did anyone experience the same problem with this object/functionality? How did you fix it? One of proposed fixes (rather crude) is to populate A1 with any value (current date for example) at the top of the VBO code, but i was hoping someone would have corrected the faulty VB code by now.. Regards
2 REPLIES 2

AndreyKudinov
Level 10
Check if A1 is blank, then set its value to anything you like, then run your Excel VBO/Get Worksheet as Collection Offset action. It does stupid stuff like: Dim cell as Object = ws.Range(Start_Cell) Dim r as Object = ws.UsedRange.Offset(cell.row-1,cell.column-1) r.Select() r.Copy() If your Start_Cell is A5, UsedRange is something like A5:Z100, after offset, actual range you are readding is A9:Z104

Dawid.Pekala
Level 4
Thanks aikudino. Exactly what we were thinking.. Having come from a field where coding excel macros were daily bread I find the VBOs to be rather... basic..  Regards