"Run-time error '13': type mismatch"

[mca2k4]

I've currently got a form w/ a listbox where entries can be selected for export to Excel. However, the code for the command button's OnClick() returns "run-time error '13': type mismatch" and highlights this line:

 Row = .Cells(1, 27) + 1

Here is the complete code:

 Private Sub BtnAdd_click()

Dim x As Integer, y As Integer
Dim itm As Variant
Dim xlNew As Excel.Application
Dim sht

Set xlNew = GetObject(, "Excel.Application")

    If TypeName(xlNew) = "Nothing" Then
        Set xlNew = CreateObject("Excel.Application")
    End If

'Row references that cell in the worksheet. The (1, 27) is
'row 1, column 27, which is where I said to put it.
With xlNew.Sheets("Sheet1").Cells
    Row = .Cells(1, 27).Value + 1
    
'Put all your error checking here eg check all the fields
'are entered correctly

'These are just examples, change them to whatever fields you want...

.Cells(Row, 2) = ItemNumber
Data(Row, 1) = Description
 
End With

If Me!OptionYes = True Then
    Data(Row, 8) = "Yes"
Else
    Data(Row, 8) = "No"
    
End If

End Sub

Any suggestions?

 

[RuralGuy]

Is Row a public variable somewhere? Do you have Option Explicit turned on?

 

[mca2k4]

Is Row a public variable somewhere? Do you have Option Explicit turned on?

Row is declared at the beginning of the code with the line:

 Dim Row As Integer

Option Explicit is also turned on towards the beginning of the code.

 

[RuralGuy]

So maybe you didn't copy and paste *all* of the code.

I'm not really familiar with Excel automation but it seems that the With statement is wrong by Access standards.


With xlNew.Sheets("Sheet1").Cells
I'm thinking it s/b:
With xlNew.Sheets("Sheet1")
Row = .Cells(1, 27).Value + 1

Of course I could be wrong.

 

[mca2k4]

Thx for the responses RuralGuy, but it still gives me the same error. At this point, however, I'm thinking I might just want to scrap the code altogther and start from scratch.

Basically I've got a listbox where users make selections and then output the selectino data to a new Excel worksheet. The only problem is that each time they click the OutputToExcel button, a new "Book1" then "Book2" then "Book3" etc is created, creating a mess of Excel windows.

I'd just like to find a way to make outputted selections all go to a single Excel spreadsheet (such as in the open "Book1")

 

[RuralGuy]

Sorry I couldn't be of more help. You might try looking here: Utter Access - Access Automation Forum

 

[mca2k4]

Thx anyway for trying. (btw, nice avatar )

 

[vbc22]

I think the problem is that you're missing some variables to reference the Excel objects.

From looking at my own codes, here's what I think might work...

' Declare variables and objects.
Dim xl As Excel.Application, xls As Excel.Worksheet, rng As Excel.Range

' Set up variables.
Set xl = CreateObject("Excel.Application") ' Opens new instance.

xl.Workbooks.Open (enter_parameters_here)

' Set up sheet and range variables, which will be
' used to reference particular cells in Excel.
Set xls = xl.Sheets("Sheet1")
Set rng = xls.UsedRange     ' Or use a named range in place of quotes
                            ' Set rng = xls.Range("name_of_range")
    
' References row 1, column 1
Debug.Print rng(1, 1)

xl.Quit
Set xl = Nothing
Set xls = Nothing
Set rng = Nothing

EDIT:
Ok...I just re-read your post. You're exporting to Excel so what I have above is not accurate. But the point is, I think you have to use the range object, which is what you're missing.