Search code examples
excelvbams-access

Is there a way to remove embedded excel functions when importing the data into database using vba recordsets?

I am importing excel files one by one into an MS Access database. I only want certain rows read, those which contain data and not ones that contain empty cells with embedded meta-data. For example, some cells inside my excel file appear empty but when I highlight them they have meta-data like: =IF(K22<>"","hello-world",!Q45,"")

This meta-data becomes a problem when I iterate the rows through recordsets, it tries to read that meta-data but cannot convert it.

Currently my code iterates a temporary table that is based off the real excel file, then I import that into a MS access database. I have a vba on the back-end that iterates the file through a recordset and then runs some validations, problem is it is also reading in this meta-data and it doesn't know how to convert it. When everything runs it produces a table called table_name$_ImportErrors, with a bunch of "Type Conversion Failures".

When I deleted all the empty rows that contained meta-data from the original excel the program runs fine. But in the final version I cannot manually do this.

Is there any way to delete rows that contain that meta-data but not actual data in my script?

Here is a code snippet of my attempted solution:

'code that imports the excel file
'then create a temp_table based on main excel
Set db = CurrentDb
Set rs = db.OpenRecordset("temp_table")

while not rs.EOF
    if IsNull(rs.fields("a_null_field_in_same_row_as_meta_data").value) = False Then
    'run data validations here
    else:
        rs.movenext
    end if
wend

'add contents of temp_table into main table inside ms access

Here I am trying to skip the rows that contain meta-data on the same rows that contain completely empty cells. The rows whose first cell is empty are also rows that contain meta-data later down the row.

excel file looks like this:

field1, field2, field3, field4
------------------------------
sdfsdfs,sdfgsdg,sdgsdg,sdgsdgs
gfsdsfs,gdsgfsg,sdfsgsg,sdfsdf
       ,       ,=IF(..),=IF(..)
       ,       ,=IF(..),=IF(..)

Thank you

Solution

  • Since DoCmd.TransferSpreadsheet gives you an unwanted *ImportErrors table, consider a different method to import the spreadsheet data.

    You can use an Access query with an appropriate connection string to fetch the spreadsheet data.

    Here is an example from my system. It includes a WHERE clause to exclude rows whose first column contains Null.

    SELECT xl.*
FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Temp\Sample.xlsx].[Sheet1$] AS xl
WHERE xl.Field1 Is Not Null;

    If you have trouble constructing the right connection string for your situation, you can temporarily create an Access link to the spreadsheet and then examine the link's properties.

    ? CurrentDb.TableDefs("Sheet1").Connect
Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Temp\Sample.xlsx

? CurrentDb.TableDefs("Sheet1").SourceTableName
Sheet1$

    Or if you want to keep the spreadsheet link permanently, your Access query could be even simpler.

    SELECT xl.*
FROM [link name here] AS xl
WHERE xl.Field1 Is Not Null;

    Either way, once you have a working query, you can use it as the source for OpenRecordset, adapt it for a SELECT ... INTO query to store its data into a new table, or adapt it as an INSERT query to store its data into an existing table.