For Each Excel Row to read blank in modern design

[YangYang]

Hi there,

I am working on modern design with VB, Windows
From Excel flow on For Each Excel Row
Current Row in range current sheet (i using for each excel sheet so is reading few sheets)
checked Has header
Assigning to dictionary config index 0 = index 1
problem:
my worksheet did not always starts with visible row
meaning after header my first row might be empty
and for each row there is blank row
and for index 0 there might be blank cell even index 1 has value
the output using the for each excel row activity
when first row is blank it will not read that sheet totally
and when there is blank row even the following row has value it will end reading that sheet
and when there is empty cell in index 0 it will throw error

Can i use for each excel row under In range syntax to use
CurrentSheet.ReadAsDataTable(…
how should i use this syntax? or should i use other syntax?

[Anil_G (Anil Gorthi)]

@YangYang

Try with currentsheet.Range(currenaheet.fullrangeName)

Cheers

[YangYang]

Hi Anil_G,

there is no fixed range
different sheets has different range and might have add on row
meaning to say it is not always fixed range row

[Anil_G (Anil Gorthi)]

@YangYang

FullRangeName will fetch the fullrange of the sheet…it is not a fixed value

Cheers

[YangYang]

Hi Anil_G,

it don’t work

[Anil_G (Anil Gorthi)]

@YangYang

Was it still not reading the data?

Is it possible to share a sample excel here please

Cheers

[YangYang]

Hi Anil_G,

my other sheets has first blank row after the header and it don’t read that sheet at all
and if column A is blank but column B with value, it will throw error

[Anil_G (Anil Gorthi)]

@YangYang

Can you please attach a sample excel here and point which sheet is failing

Cheers

[YangYang]

Hi Anil_G,

is a simple sheet that you can mock up
say two worksheet, one with few blank and another sheets with first row bank

[Anil_G (Anil Gorthi)]

@YangYang

try using like this…it is working as expected and reading all the data

currentSheet.DataTableValue with for each row in datatable

cheers

[YangYang]

Hi Anil_G,

thanks, it works.

on another question:
could you advise how to use ReadAsDataTable by giving an example syntax? thanks

[Anil_G (Anil Gorthi)]

@YangYang

you can use like this CurrentSheet.ReadAsDataTable(True,True,False,True,Nothing)

cheers

[YangYang]

Hi Anil_G,

Error on conversion

[Anil_G (Anil Gorthi)]

@YangYang

this you cannot give in for each excel…output is type of datatable so for ech row in dattaable should be used

cheers