I have modified some code that opens Excel from Access using DAO. It iterates through each row and selects only cells that are empty or with a certain string value inside.
Problem: It still reads and imports every line from the Excel worksheet into an Access table as though my IF arguments do not exist. When I checked it appears that the code enters the IF arguments for EVERY iteration then proceeds to record every line from Excel. I want Access to ignore cells containing " " (blank) "Initialized" and import other corresponding rows.
I've checked the cell referencing in the Excel file to make sure the arguments apply the the correct column, but still the same.
Any advice would be great!
James
Problem: It still reads and imports every line from the Excel worksheet into an Access table as though my IF arguments do not exist. When I checked it appears that the code enters the IF arguments for EVERY iteration then proceeds to record every line from Excel. I want Access to ignore cells containing " " (blank) "Initialized" and import other corresponding rows.
I've checked the cell referencing in the Excel file to make sure the arguments apply the the correct column, but still the same.
Code:
Option Compare Database
Option Explicit
Dim xlfilename As String
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim nRow As Integer
Dim iRow As Integer
Dim iCol As Integer
Dim iLastRow
Dim cValue As String
Dim cGroupValue As String
Dim cTaskCode_Row As Integer
Dim cTaskCode As String
Function LoadExcelSpreadsheet(xlfilename)
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(xlfilename)
Set oSheet = oBook.Worksheets(1)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("EOTaskDefInitStatus")
iRow = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iCol = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
With ActiveSheet.UsedRange
iCol = .Cells(1, 1).Column + .Columns.Count - 1
iLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With
'iRow is the last row in the worksheet that contains data
For nRow = 1 To iRow
cValue = oSheet.Range("G" & nRow).Value
If cValue <> " " Or cValue <> ("Aircraft") Then
rs.AddNew
''' rs("TaskCode") = cTaskCode
rs("Aircraft") = oSheet.Range("A" & nRow).Value
rs("Rego") = oSheet.Range("B" & nRow).Value
rs("EngineAPU_PN") = oSheet.Range("C" & nRow).Value
rs("EngineAPU_SN") = oSheet.Range("D" & nRow).Value
rs("Component_PN") = oSheet.Range("E" & nRow).Value
rs("Component_SN") = oSheet.Range("F" & nRow).Value
rs("Initialised") = oSheet.Range("G" & nRow).Value
rs.Update
End If
Next
rs.Close
oExcel.Quit
MsgBox (nRow)
Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Set rs = Nothing
End Function
Any advice would be great!
James