transferspreedsheet not importing all rows.

[gammaman]

I have an excel file with about 4800 rows. I am using transferspreedsheet to import the rows into an access table, however only 14 out of the 4800 rows are being imported b/c all of the other rows in the excel file contain some blank cells which is causing them to error and not get imported. How can I import all the rows and just have blank data inserted into the database for the rows in the excel file that have blanks. Below is my code.

Public Sub Import()


'DoCmd.SetWarnings (WarningsOff)




Set db = CurrentDb()    'Establish a Connection to the Current Database


On Error Resume Next
DoCmd.RunSQL ("DROP TABLE 041913_ARMS_Manual_Review_2")       'Drop Table if exists
DoCmd.RunSQL ("CREATE TABLE [041913_ARMS_Manual_Review_2]")   'Create the loading table


Set tb1 = db.TableDefs("041913_ARMS_Manual_Review_2")         'Create a table definition to add fields to loading table
Set fd = Application.FileDialog(msoFileDialogFilePicker)      'Open file chooser to select file to load into loading table


counter = 0    'Set column counter to zero.


With fd
.Show
For Each vrtSelected In .SelectedItems


   Set xlapp = CreateObject("Excel.application")
   Set xlWrkBk = GetObject(vrtSelected)
   Set xlsht = xlWrkBk.Worksheets(1)


   'Loop First Row in Excel File which contains header column names


   strcolumns = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH")




   For Each strColumn In strcolumns


      counter = counter + 1                         'increase the counter to determine the number of columns
      Set columnName = xlsht.Cells(1, strColumn)


            Set FieldName = tb1.CreateField(columnName, dbText, 200)     'insert text type fields into table def
            tb1.Fields.Append FieldName 
   Next strColumn


   'transfers data from excel file and imports it into access table
   DoCmd.TransferSpreadsheet acImportDelim, , TableName:="041913_ARMS_Manual_Review_2", FileName:=vrtSelected, HasFieldNames:=True
Next vrtSelected
End With

[rpeare]

Does your target table have a primary key involving any of those null fields? excel 'null' fields are, I think stored as a blank text field in other words a string with 0 length "". have you tried converting those to null values for any number fields?

[gammaman]

No, the target table does not have any keys. It simply stores the data being imported. The macro first reads the excel sheet and creates the table fields (all "text" datatype), using row A1 of the excel spreadsheet. I then simply use transferspreedsheet to read in the data from the sheet. I don't understand though, since all of my fields are "text" (all of the cells on the spreadsheet are also all "text"), it should just insert the "blank" cells into the access table.

[June7]

Try changing acImportDelim to just acImport.

[gammaman]

Still does not work. Attached is the error message.

[rpeare]

have you got a sample of the database and the file you can post?

(something 2007 or prior if you can)

[June7]

I did a test and cannot replicate issue. If you want to provide files as suggested by rpeare, follow instructions at bottom of my post.

[vincent-leeway]

I wonder if you tried out steps in this link, http://support.microsoft.com/kb/141228