DoCmd.TransferSpreadsheet acImport

[GraeagleBill]

How does one load a table from an Excel spreadsheet WITHOUT sucking in hundreds of blank records. (I don't own the Excel file, so I get whatever I get) I can get around the problem with a subsequent query that excludes the blank records or quit processing when code encounters the first "essential field", but it would be nice to know if import function provides for it.

DoCmd.TransferSpreadsheet acImport, , "tblEXCEL-PRKG", strExcelName, True

[ranman256]

Attach The workbook as an external table,
then make an append query to exclude the blanks.

[GraeagleBill]

Thanks, I knew there had to be a way.

[Micron]

I figured you already had linked the sheet as a table

I can get around the problem with a subsequent query

AFAIK, you cannot link a workbook - only a worksheet?

[GraeagleBill]

The table is created anew with the TransferSpreadSheet. I then select it then as a DAO Recordset to process the records. The OP simply has to do with the way Excel extends its spreadsheets with a bunch of empty records, I was hoping there was an Access method with the DoCmd.TransferSpreadSheet that would provide for those records to be ignored.

My solution is/was more along the lines that Ranman suggested, only without the append.

strSQL = "SELECT * FROM tblExcelPrkg WHERE (Apt Is Not Null);"
Set rsExcel = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)