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.

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

--
Brendan Reynolds
Access MVP

"Andy" <(E-Mail Removed)> wrote in message
news:241B3448-659A-4DAB-BD1F-(E-Mail Removed)...
>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.
>
>
>
>

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

"Brendan Reynolds" wrote:

> What's the value stored in 'pubg1' at that point? Is it within the valid
> range of an integer? (-32,768 to 32,767)
>
> --
> Brendan Reynolds
> Access MVP
>
> "Andy" <(E-Mail Removed)> wrote in message
> news:241B3448-659A-4DAB-BD1F-(E-Mail Removed)...
> >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.
> >
> >
> >
> >
>
>
>

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.

--
Brendan Reynolds
Access MVP


"Andy" <(E-Mail Removed)> wrote in message
news:E9A7567B-A091-44F5-A4AE-(E-Mail Removed)...
> Yes - sorry I should have said - the Excel cell contains the value
> "345578"
> which is passed to pubg1
>
> "Brendan Reynolds" wrote:
>
>> What's the value stored in 'pubg1' at that point? Is it within the valid
>> range of an integer? (-32,768 to 32,767)
>>
>> --
>> Brendan Reynolds
>> Access MVP
>>
>> "Andy" <(E-Mail Removed)> wrote in message
>> news:241B3448-659A-4DAB-BD1F-(E-Mail Removed)...
>> >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.
>> >
>> >
>> >
>> >
>>
>>
>>

Excellent - Thanks.

"Brendan Reynolds" wrote:

> 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.
>
> --
> Brendan Reynolds
> Access MVP
>
>
> "Andy" <(E-Mail Removed)> wrote in message
> news:E9A7567B-A091-44F5-A4AE-(E-Mail Removed)...
> > Yes - sorry I should have said - the Excel cell contains the value
> > "345578"
> > which is passed to pubg1
> >
> > "Brendan Reynolds" wrote:
> >
> >> What's the value stored in 'pubg1' at that point? Is it within the valid
> >> range of an integer? (-32,768 to 32,767)
> >>
> >> --
> >> Brendan Reynolds
> >> Access MVP
> >>
> >> "Andy" <(E-Mail Removed)> wrote in message
> >> news:241B3448-659A-4DAB-BD1F-(E-Mail Removed)...
> >> >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.
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>