Error in script: (10062) Overflow

[Gyorgy Bea]

Hello,

I'm working on a script which should read some data from an excel file and record them in 2 array variables (named header and row) for further processes. I am getting this error on the highlighted line: (10062) Overflow. Any idea what is wrong in the code?

Thank you,
Beata


Dim App
Dim strFileName As String
Dim W

Sub Main

Dim lngRowsA As Integer
Dim lngRowsB As Integer
Dim header ()
Dim row ()
Dim i As Integer

  Set App = CreateObject("Excel.Application")
  strFileName = GetFilePath$("TableSpecs.xls","xls",,"Open the Excel Workbook", 0)
  Set Workbook = App.Workbooks.Open(strFileName)
  Set XLSheet=Workbook.Worksheets("Sheet1")


With XLSheet

    lngRowsA = .Range("A:A").Rows.Count
    For i = 0 To lngRowsA -1
      header(i) = .Activecell.Offset(i,0).value
    Next

    lngRowsB = .Range("B:B").Rows.Count
    .ActiveCell.Offset(0, 1).Activate
    For i = 0 To lngRowsB -1
      row(i) = .Activecell.Offset(i,0).value
    Next

End With


End Sub

[Peck, Jon]

First thing to try: your variables are declared as int.  That's a 16-bit signed quantity in VB/Sax.  Try declaring them as long instead.

 

---

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Gyorgy Bea
Sent: Wednesday, March 25, 2009 8:21 AM
To: [hidden email]
Subject: [SPSSX-L] Error in script: (10062) Overflow

 

Hello,

I'm working on a script which should read some data from an excel file and record them in 2 array variables (named header and row) for further processes. I am getting this error on the highlighted line: (10062) Overflow. Any idea what is wrong in the code?

Thank you,
Beata


Dim App
Dim strFileName As String
Dim W

Sub Main

Dim lngRowsA As Integer
Dim lngRowsB As Integer
Dim header ()
Dim row ()
Dim i As Integer

  Set App = CreateObject("Excel.Application")
  strFileName = GetFilePath$("TableSpecs.xls","xls",,"Open the Excel Workbook", 0)
  Set Workbook = App.Workbooks.Open(strFileName)
  Set XLSheet=Workbook.Worksheets("Sheet1")


With XLSheet

    lngRowsA = .Range("A:A").Rows.Count
    For i = 0 To lngRowsA -1
      header(i) = .Activecell.Offset(i,0).value
    Next

    lngRowsB = .Range("B:B").Rows.Count
    .ActiveCell.Offset(0, 1).Activate
    For i = 0 To lngRowsB -1
      row(i) = .Activecell.Offset(i,0).value
    Next

End With


End Sub

 

[Gyorgy Bea]

Thank you. Changing into Long solved the Overflow problem.
Now I'm facing an other one: it gives me error on this line:       header(i) = .Activecell.Offset(i,0).value
"no such property or method"

I assume the reference on the excel object is not correct, I've tried it in several ways, but I can't figure out how is the correct form.


Dim App
Dim strFileName As String
Dim Workbook
Dim XLSheet


Sub Main

Dim lngRowsA As Long
Dim lngRowsB As Long
Dim header ()
Dim row ()
Dim i As Long

  Set App = CreateObject("Excel.Application")
  strFileName = GetFilePath$("TableSpecs.xls","xls",,"Open the Excel Workbook", 0)
  Set Workbook = App.Workbooks.Open(strFileName)
  Set XLSheet=Workbook.Worksheets("Sheet1")


With XLSheet

    lngRowsA = .Range("A:A").Rows.Count
    For i = 0 To lngRowsA -1
      header(i) = .Activecell.Offset(i,0).value
    Next

    lngRowsB = .Range("B:B").Rows.Count
    .ActiveCell.Offset(0, 1).Activate
    For i = 0 To lngRowsB -1
      row(i) = .Activecell.Offset(i,0).value
    Next

End With


End Sub

---

From: "Peck, Jon" <[hidden email]>
To: Gyorgy Bea <[hidden email]>; [hidden email]
Sent: Wednesday, March 25, 2009 4:46:33 PM
Subject: RE: [SPSSX-L] Error in script: (10062) Overflow

First thing to try: your variables are declared as int.  That's a 16-bit signed quantity in VB/Sax.  Try declaring them as long instead.

 

---

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Gyorgy Bea
Sent: Wednesday, March 25, 2009 8:21 AM
To: [hidden email]
Subject: [SPSSX-L] Error in script: (10062) Overflow

 

Hello,

I'm working on a script which should read some data from an excel file and record them in 2 array variables (named header and row) for further processes. I am getting this error on the highlighted line: (10062) Overflow. Any idea what is wrong in the code?

Thank you,
Beata


Dim App
Dim strFileName As String
Dim W

Sub Main

Dim lngRowsA As Integer
Dim lngRowsB As Integer
Dim header ()
Dim row ()
Dim i As Integer

  Set App = CreateObject("Excel.Application")
  strFileName = GetFilePath$("TableSpecs.xls","xls",,"Open the Excel Workbook", 0)
  Set Workbook = App.Workbooks.Open(strFileName)
  Set XLSheet=Workbook.Worksheets("Sheet1")


With XLSheet

    lngRowsA = .Range("A:A").Rows.Count
    For i = 0 To lngRowsA -1
      header(i) = .Activecell.Offset(i,0).value
    Next

    lngRowsB = .Range("B:B").Rows.Count
    .ActiveCell.Offset(0, 1).Activate
    For i = 0 To lngRowsB -1
      row(i) = .Activecell.Offset(i,0).value
    Next

End With


End Sub