How to limit the number of imported rows

[diverdon99]

Hi All

The code used to import the excel sheets is below, my problem is that
the table DBReccData may have from 1 to 33 rows of data. How do I
determine the point where no data in the row exists and stop
importing?

Thanks in advance
Don

Extract ............................................
For z = 1 To sheetcnt
Set sheet = xlapp.ActiveWorkbook.Sheets(z)

If (sheet.Name = "DBSiteData") Then
DoCmd.TransferSpreadsheet transfertype:=acImport, _
tablename:="Maj_Import", _
Filename:="C:\Documents and Settings\Don\Desktop\Nevil
Current\OA_DataEntryv2.xls", Hasfieldnames:=False, _
Range:="DBSiteData!a2:ap2",
SpreadsheetType:=acSpreadsheetTypeExcel9
End If


If (sheet.Name = "DBReccData") Then
DoCmd.TransferSpreadsheet transfertype:=acImport, _
tablename:="Min_Import", _
Filename:="C:\Documents and Settings\Don\Desktop\Nevil
Current\OA_DataEntryv2.xls", Hasfieldnames:=False, _
Range:="DBReccData!a2:aa33",
SpreadsheetType:=acSpreadsheetTypeExcel9
End If
Next z

 

[Joe Fallon]

You can't.
Not while using TransferSpreadsheet.
It is all or nothing on a rectangular range of data.

You could automate Excel and do things at a finer grain level but at the
cost of a lot of complexity and slowness.

 

[diverdon99]

Thanks joe,

I need a workaround then, how does this sound for an idea?
Import into a temporary table then in Acess check for blank rows and
delete before running an update query to pass in to Main table.
If this is feasible, I would apppreciate some help with the VBA code
to delete blank rows.

Don

 

[John Nurick]

Hi Don,

Supplementing what Joe said, here are a couple of approaches that may
help:

1) In Excel, define a named range on the DBReccData sheet including
however many rows you want to import. Then specify that range in the
TransferSpreadsheet statement.

2) When editing the table in Excel, if you have to remove data don't
just select it and hit the Delete key (which does the same as Edit|Clear
Values). Instead, select the entire row(s) and use Edit|Delete. Doing it
this way removes the deleted rows from the UsedRange of the worksheet -
which is the area that Access tries to import. If Access still tries to
import too many rows, go back to Excel and select all the blank rows
below the data and use Edit|Delete on them.

 

[diverdon99]

John,

I apprecite the advice re Excel but as this will be an ongoing import
of up to 300 sheets a day then I need an automated Acces answer. The
users are not sophisticated and if they are asked to delete rows we
may end up up with no data!

Surely the approach would be to import into temp table and manipulate
data from there before placing in the main table/. If you have any
ideas of how to do this it would be great help

Don

 

[tina]

if you're just trying to eliminate the blank rows at the "bottom" of
spreadsheet data, which Access "sees" and imports, then try this:

choose a field (column) in the import sheets that is never blank. in the
Access table's design view, set a validation rule for that field, as

Is Not Null

this is quicker and easier than importing to a Temp table. if you can't find
a common "not blank" field in your spreadsheet data, then a Temp table is
probably the easiest way to go.
you can create a Delete query with criteria Is Null on *every* field in the
Temp table, to pull only the empty rows. use DoCmd.RunQuery in your VBA
code, or run the SQL statement directly with DoCmd.RunSQL. if you want to
block the system warning about deleting records, use DoCmd.SetWarnings False
before the delete action. make sure you remember to follow the action with
DoCmd.SetWarnings True.

hth

 

[Joe Fallon]

Something like this should do the trick.
The idea is to just look for Null PK values.

DELETE tbl1.*
FROM tbl1
WHERE (((tbl1.PK) Is Null));

 

[diverdon99]

Thanks to all for your help with this problem, its now importing as I
hoped.
One other problem has cropped up, although not too important I would
appreciate a steer on it if you have any ideas.
The 2 tables that are being appended to are related one to many, if I
update the Main table first then no problem as in the Sub Table it
requires a record to be present in main. The quirky bit is that if I
delete (via form) in the main table it seems to work including
deleteing the sub table related records but if I check the main table
then it is still there!
Is this something to do with the form not updating the underlying
table until much later. The reason this has cropped up is that my
append failed on 1 occasion and that was because the record existed
and the Primary key did its job in stopping a new record with the same
number being added. I should add that the PK is the EnquiryNumber in
the main table and the first field (not indexed) in the imported
(appended) table.
Is there a vb command to force an update immediatly a record is
deleted?

Thanks again

Don

 

[diverdon99]

Thanks Tina, a couple of good follow up points, I think Ill go with
creating a delete queries in the way you suggest.

 

[Joe Fallon]

No.
You are misdiagnosing the problem.
When a table is updated it is instantaneous.
Even through a form.

You may have something wrong with your form.

Go slowly and examine each step of your process and test that the values you
predict should be present (or deleted) at each step are really there (or
not). Then you should find your problem.