Import Selected Values From Excel via DAO (1 Viewer)

[Jamesss]

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.

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

 

[pbaldy]

You're not testing for blank, you're testing for a single space. Try this:

If Nz(cValue, "") <> "" Or cValue <> ("Aircraft") Then

I also suspect you want And rather than Or. There is no concievable value that would fail the test with Or.

 

[Jamesss]

That did the trick pbaldy. Indeed your suspicions were correct an AND statement would be correct. On further thought an Or staement would be true for all cells. THANKS!

 

[Jamesss]

I also wanted to populate a column in the Access table with a string variable "cTaskCode" defined by:

    cGroupValue = oSheet.Range("D" & nRow).Value 
    If InStr(1, cGroupValue, "Task Code :") = 1 Then
        
        ' Extracts Task number as string for characters following colon ": "
        cTaskCode = Right(cGroupValue, Len(cGroupValue) - InStr(cGroupValue, ": "))

    End If

The problem is that when I try to insert the string variable cTaskCode using the following code. It cannot recognise cTaskCode and I get a runtime error.

    If cValue <> "" And cValue <> ("Initialized") Then
        rs.AddNew
        rs("TaskCode") = cTaskCode
         .
         .
         .
    End If

How can I get Access to insert this variable for a multiple entries into the Access table.

Thanks
James

 

[pbaldy]

If that's all within the same function, I don't see why it wouldn't work. What's the full code now?

 

[Jamesss]

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
    cGroupValue = oSheet.Range("D" & nRow).Value 
    ''' Column where "Task Code" resides

    If InStr(1, cGroupValue, "Task Code :") = 1 Then
        '''Extracts Task Code as a string for values following colon ": "
        cTaskCode = Right(cGroupValue, Len(cGroupValue) - InStr(cGroupValue, ": "))
    End If
    
    cValue = oSheet.Range("G" & nRow).Value ''' "Initialized" data field is never empty
    ''' "Initialized" is the field heading in the MXi file
    
    If cValue <> "" And cValue <> ("Initialized") 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
Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Set rs = Nothing
 
End Function

I was thinking that perhaps Access doesn't allow you to record variables and only objects?

 

[pbaldy]

No, you can use variables. Have you set a breakpoint and seen exactly what happens with the code? What line does the error occur on?

 

[Jamesss]

The error is isolated to the line:

rs("TaskCode") = cTaskCode

The error reads:
"Run Time Error '3265':
Item not found in collection."

 

[pbaldy]

That implies that "TaskCode" is not a field in the recordset data.

 

[boblarson]

The error is isolated to the line:

rs("TaskCode") = cTaskCode

The error reads:
"Run Time Error '3265':
Item not found in collection."

Yeah, as Paul has mentioned it probably is misspelled and is supposed to be something like:

rs("Task Code") with the space

or

rs("Task_Code") if you have an underscore (whatever the actual field name is - so double check the name of the field).

 

[Jamesss]

You guys are right... I had a closer look at the field headings and indeed "Task Code" NOT "TaskCode".

Incredible how stoopid I can be.

Thanks Paul and Bob!

 

[boblarson]

Incredible how stoopid I can be.

Nope, not stupid at all. How do you think WE knew about it? I can't speak for Paul, but I've done the exact same thing more than once.