Importing from Excel

[strangeryet]

I have code that imports an Excel spreadsheet into an Access table. The import works, but includes blank lines from the Excel sheet in the import. How can I control this and only import lines with data? Below is the do command I am using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblExlCshd", "c:\My Documents\Bank\cshd" & strMnth & ".xls", False, "C:E"

Thanks

 

[PHV]

You may import the sheet into a temporary table and then run a selective append query from the temp to the real table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jimlee]

if you open excel before the import you could always do the work there. Put this into the Workbook_Open module.

Private Sub Workbook_Open()

'delete the blank lines
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

Not sure if you can run this (maybe from access) without actually opening excel to trigger it. Maybe someone else could help out with that?


jimlad

 

[CautionMP]

Another possible solution (I hate temp tables) is to create a link table to your Excel spreadsheet, then create a append query that filters out the blank rows.

I see that you are using a dynamic filename in the above code. You can still do this, you will first need to unlink the old spreadsheet (DoCmd.DeleteObject), then link the new spreadsheet (DoCmd.TransferSpreadsheet) before calling the append query.

When you call DoCmd.TransferSpreadsheet change the argument acImport to acLink, all the other arguments should be the same.

Hope this helps,
B.