Hi !
How I can remove empty rows when I import from excel ?
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"merkinnät", "thya.XLS", True, ""
Br
Timo
Hi !
How I can remove empty rows when I import from excel ?
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"merkinnät", "thya.XLS", True, ""
Br
Timo
How about simply running a delete query after you import?
We do dozens of spreadsheet imports every month and run into this a lot. I wrote this code to delete the extra blank rows. All you have to do is pass the name of the table in question to the sub. The code builds a query that selects rows that contains no data, and deletes the rows.
Sub DeleteBlankRows(TableName)
Dim dbs As Database
Dim rst As DAO.Recordset
Dim fld As Field
Dim strSQL As String, strSelect As String, strWhere As String
Dim i As Integer, iFieldCount As Integer
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset(TableName)
i = 1
iFieldCount = rst.Fields.Count
For Each fld In rst.Fields
strSelect = strSelect & "[" & TableName & "].[" & fld.Name & "] AS " & CStr(i)
strWhere = strWhere & "([" & TableName & "].[" & fld.Name & "] is Null)"
If i < iFieldCount Then
i = i + 1
strSelect = strSelect & ", "
strWhere = strWhere & " AND "
Else
End If
Next fld
rst.Close
strSQL = "Delete " & strSelect & " FROM " & TableName & " WHERE " & strWhere
dbs.Execute strSQL
dbs.Close
End Sub
Another annoying thing that happens when importing spreadsheets is the blank fields that often get added on. F34, F35, F36 . . . etc. So I wrote this to remove the extra fields. It looks for fields named "F[some number]" and drops them from the table.
Sub DeleteExtraFields(TableName)
'Remove extra fields from imported table
Dim dbs As Database
Dim rst As DAO.Recordset
Dim fld As Field
Dim strReturn As String
Dim vFieldsToDrop As Variant
Dim i As Integer
Dim element As Variant
i = 0
ReDim DynArray(0)
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset(TableName)
For Each fld In rst.Fields
strReturn = fld.Name
If Left(strReturn, 1) = "F" And IsNumeric(Right(strReturn, Len(strReturn) - 1)) Then
ReDim Preserve DynArray(UBound(DynArray) + 1)
DynArray(UBound(DynArray)) = strReturn
Else
End If
Next fld
rst.Close
For Each element In DynArray
strReturn = CStr(element)
If Len(strReturn) > 0 Then
dbs.Execute "Alter Table " & TableName & " Drop Column " & strReturn & ";"
Else
End If
Next element
dbs.Close
End Sub