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 is the code:
Code:
Public Function ProcessFileImport(sFile As String, sTable As String, cStartRow1 As Byte, cStartColumn1 As Byte, cStartField1 As Byte, cTab1 As Integer) As String
Dim wbk As New Excel.Workbook
Dim ExcelApp As New Excel.Application
Dim ExcelBook As New Excel.Workbook
Dim wks As New Excel.Worksheet
Dim rngDefine As Excel.Range
' Access object variables
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer
Set ExcelBook = ExcelApp.Workbooks.Open(sFile)
Set wbk = 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