Transferspreadsheet bring empty data from excel to access

[sunil vedula]

hi all,
when i use transfer spreadsheet command in the access macro to import excel
spreadsheet it imports blanks rows too. For example if there are 10 records
still it loads 2000 rows odd. Why does this happen? is there no way to stop
those empty cells from being imported. As a temporary fix i ask them to
delete all the rows using ctrl- and then paste the new data. I am looking for
an better solution.

 

[Piet Linden]

hi all,
when i use transfer spreadsheet command in the access macro to import excel
spreadsheet it imports blanks rows too. For example if there are 10 records
still it loads 2000 rows odd. Why does this happen? is there no way to stop
those empty cells from being imported. As a temporary fix i ask them to
delete all the rows using ctrl- and then paste the new data. I am lookingfor
an better solution.

see ken snell's page on importing data from Excel.
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

 

[Piet Linden]

hi all,
when i use transfer spreadsheet command in the access macro to import excel
spreadsheet it imports blanks rows too. For example if there are 10 records
still it loads 2000 rows odd. Why does this happen? is there no way to stop
those empty cells from being imported. As a temporary fix i ask them to
delete all the rows using ctrl- and then paste the new data. I am lookingfor
an better solution.

see ken snell's page on importing data from Excel.
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

 

[Ken Snell MVP]

Thanks for the link, Piet, but I don't have info there yet for this issue.
But it's a good topic to include on my web page, so I will do that soon.

sunil,

The issue is that the UsedRange property in the EXCEL spreadsheet is larger
than the range occupied by the data. This occurs when someone deleted data
from the cells in rows, but didn't actually delete the rows themselves. So
EXCEL still sees those empty rows as "dirty", and thus they are imported
into ACCESS.

What you need to do is go into the EXCEL spreadsheet, delete those empty
rows (the entire rows), save the file, then close the workbook file. Now the
UsedRange property should be reset, and the import will work fine.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

hi all,
when i use transfer spreadsheet command in the access macro to import
excel
spreadsheet it imports blanks rows too. For example if there are 10
records
still it loads 2000 rows odd. Why does this happen? is there no way to
stop
those empty cells from being imported. As a temporary fix i ask them to
delete all the rows using ctrl- and then paste the new data. I am looking
for
an better solution.

see ken snell's page on importing data from Excel.
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

 

[Ken Snell MVP]

Thanks for the link, Piet, but I don't have info there yet for this issue.
But it's a good topic to include on my web page, so I will do that soon.

sunil,

The issue is that the UsedRange property in the EXCEL spreadsheet is larger
than the range occupied by the data. This occurs when someone deleted data
from the cells in rows, but didn't actually delete the rows themselves. So
EXCEL still sees those empty rows as "dirty", and thus they are imported
into ACCESS.

What you need to do is go into the EXCEL spreadsheet, delete those empty
rows (the entire rows), save the file, then close the workbook file. Now the
UsedRange property should be reset, and the import will work fine.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

hi all,
when i use transfer spreadsheet command in the access macro to import
excel
spreadsheet it imports blanks rows too. For example if there are 10
records
still it loads 2000 rows odd. Why does this happen? is there no way to
stop
those empty cells from being imported. As a temporary fix i ask them to
delete all the rows using ctrl- and then paste the new data. I am looking
for
an better solution.

see ken snell's page on importing data from Excel.
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm