Ignore Excel rows while importing to Access in VB

[LB79]

Hello,

I have a worksheet in Excel which is basically a lot of links and formula’s.
Depending on parameters, the sheet may be 10 rows long or 500. If a row is unused its shows blank, although there is a formula in it.
What I want is to import the data to Access using VB, but the blank rows are being imported and causing problems.
Is there a way for access to select only the rows with data in and ignore the “blank” rows that contain formulas?

Thanks

 

[gemma-the-husky]

generally, the easiest way is

import the lot

delete rows where a particular column is blank (null)

 

[LB79]

Thanks.

This is what ive been doing, but its creates a seperate error table with all the blank rows in. Was hoping for a way to avoid that as it will end up making the DB size huge unless manually deleted.

 

[chergh]

Sort the data in excel and then loop through your data until the cell value is blank and the formula is not then delete everything below it.

 

[ghudson]

Import the Excel file as-is into a temp table in Access. Then append the records that meet your requirements into the table where you want to store the records.

    'Purge all records in the tblTEMP table
    CurrentDb().Execute "DELETE * FROM [tblTEMP]"
 
    DoCmd.SetWarnings False
         DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTEMP", "X:\ImportExcel.xls", False
    DoCmd.SetWarnings True
     
    'Purge all records in the tblToStoreRecords table
    CurrentDb().Execute "DELETE * FROM [tblToStoreRecords]"
     
    DoCmd.SetWarnings False
         DoCmd.OpenQuery "qryAppendRecordsYouWantToStore"
    DoCmd.SetWarnings True

Or, set one or more of the fields in your table that should always have a value to a Primary Key to prevent the importing of blank records into the table.