Convert Excel variable to Access variable

[Guest]

I have opened an Excel spreadsheet from Access code and passed the value of a
particular cell (in text format) to an Access varaible (pubg1) in Variant
format - fine.

When I try to covert the Access variable to an Integer variable (intvar)
using the Val function the code fails with an overflow error.

Can any one make sense of this? Subset of the code :-

Dim pubg1 as Variant
Dim intvar as Integer

Set objWkb = objXL.Workbooks.Open("c:\PM.xls")
Set objSht1 = objWkb.Worksheets("PM")
i = 3

With objXL

.Visible = False

With objSht1

pubg1 = .Range(.Cells(i, 1), .Cells(i, 1)).Value ' range
format is text

End With

.Quit

End With

objXL.Quit
Set objSht1 = Nothing
Set objWkb = Nothing
Set objXL = Nothing

intvar = Val(pubg1) ' fails here with overflow error and err value 6.

Thanks.

 

[Brendan Reynolds]

What's the value stored in 'pubg1' at that point? Is it within the valid
range of an integer? (-32,768 to 32,767)

 

[Guest]

Yes - sorry I should have said - the Excel cell contains the value "345578"
which is passed to pubg1

 

[Brendan Reynolds]

Well there you go then. You can't put 345,578 in an integer, the maximum
value of an integer is 32,767. You'll need to use a long instead.

 

[Guest]

Excellent - Thanks.

Well there you go then. You can't put 345,578 in an integer, the maximum
value of an integer is 32,767. You'll need to use a long instead.