How to add data to just one field in Access?

[Ehsan arman]

Hi, I have a function to import records from excel into Access. I want to add a particular cell value in Excel under a particular field in the access table and do this until I reach the end of file. The cell value will be a date like: 09/20/2010. The field name in Access is "Date". Since the date appears in the 3rd row, 3rd column in Excel I tried something like this but didn't work:

Dim newVar As Variant
Set newVar = wks.Cells(3, 3)
![CCTVPipeTemporary].Date = newVar

Thanks.

[jimatqsi]

I would simply link to the Excel sheet and write a query to do that. Use File/ImportData/Link and specify Excel sheet as the type.

The Excel sheet ends up looking like a table in the database so to write an append or update query is all you need to do.

[Ehsan arman]

I actually fixed that problem, but now I have run into another problem with a For loop. When filling up the Access table from excel I want to skip two columns and not add their data. Namely, columns 28 and 29 in Excel which correspond to Fields 26 and 27 in my access table. so I have a variable iFld which keeps track of the fields and iRow and iCol which keep track of the rows and columns in the excelsheet. I have the following For Loop which fills up the table. I am trying to skip those columns (28 and 29) in the for loop. I have failed to do this successfully. I tried an if Then statement in the For loop and although it skipped the rows I did not get any data imported into the table. Here it is:

Public Function ProcessFileImport(sFile As String, sTable As String, cStartRow1 As Byte, cStartColumn1 As Byte, cStartField1 As Byte, cTab1 As Integer) As String
 
Set ExcelBook = ExcelApp.Workbooks.Open(sFile)
Set wks = ExcelApp.Worksheets(cTab1)
 
    Set dbs = CurrentDb
   sSQL = "SELECT * FROM " & sTable
   Set rst = dbs.OpenRecordset(sSQL)
 
   With rst
   .AddNew
   End With
 
   iCol = cStartColumn1
   iRow = cStartRow1
 
   Do While Not wks.Cells(iRow, 1) = ""
 
   cStartField1 = rst.Fields(0)
 
  iFld = cStartField1
      lRecords = lRecords + 1
 
 
      For iCol = cStartColumn1 To cStartColumn1 + (rst.Fields.Count - (cStartField1 + 1))
 
 
         rst.Fields(iFld) = wks.Cells(iRow, iCol)
         DoCmd.Hourglass True
         iFld = iFld + 1
 
 
      Next
 
 
 
   If iFld = 22 Then
   rst("Date").value = wks.Cells(3, 3)
   End If
 
      iRow = iRow + 1
      rst.Update
      rst.AddNew
 
 
   Loop
 
   rst.Close
   Set rst = Nothing