[RESOLVED] VB6 with MS Excel 2007...

[dtvonly]

I used to work with VB6 and I referenced MSExcel 11.0 Library Object (MSoffice 2003). Now with MSOffice 2007, I am only given one choice of referenced MSExcel 12.0 Library Object

here is the code:

' in form
Option Explicit

Public xlApp As Excel.Application
Public xlWB As Excel.Workbook
Public xlWS As Excel.Worksheet

' in routine where I want to write to MS Excel 2007
Private Sub StepCurrentCmd_Click()

Dim DisplayStr As String
Dim row As Integer
Dim col As Integer

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Add
Set xlWS = xlWB.Worksheets("sheet1")

row = 2
col = 1
xlWS.Cells(row, col).value = DisplayStr      ' DisplayStr is an updated string
col = col + 1

End Sub

The error is "Application-defined or Object-defined error"

Essentially the error is at the line:
xlWS.Cells(row, col).value = DisplayStr

I noticed that the letter v in value (above) refuses to be capitalized. In the older application where I was using 11.0 library, the v in value automatically became capitalized. Is this the issue?

thanks.

[jggtz]

The only error I see, is

Set xlWS = xlWB.Worksheets("sheet1")

Try with :

Set xlWS = xlWB.Worksheets(1)

[westconn1]

Set xlWS = xlWB.Worksheets("sheet1")

this should work. but there have been many posts about it not working in 2007, however i would expect a runtme error on that line if it is not working correctly

test if xlWS is a valid worksheet object

[dtvonly]

Hi. Still the same error:

Run-time error '1004'
Application-defined or Object-defined error

I have tried:

Set xlWS = xlWB.Worksheets(1)

The error seems to be with the excel defined-object. How do I check if it is correctly defined?

[dtvonly]

I found the problem!

xlWS.Cells(row, col).value = DisplayStr
col = col + 1
If (col = 15) Then
  col = 1     REM  This line used to be col = 0.  Excel did not like this.
  row = row + 1
End If

Resolved! thanks for all your help.