EXTRA ZERO

Herman · Mar 10th, 2002, 11:13 AM

I Have a worksheet where in column a there are productnumbers like 045444 but excel doesnt; show the first zero so i had to make a format like 000000
now in vba i want to take connect to an sql server and find the description which belongs to 045444 but activecell.text gives 045444 that;s what i want to lookup as a value but this is now a text and activecell.value gives 45444 (but i need the extra zero)how can i look up 045444 as a value in my sql databse. ?

can someone help

Juan Pablo González · Mar 10th, 2002, 11:18 AM

Are you connecting using VBA ? if so, can you post your current code ?

Herman · Mar 11th, 2002, 02:59 AM

yes i connect through vba and this is my code
y should be the value with the zero in front

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7-3-2002 by Plieger
bnumber = 0

Do While ActiveCell.Value <> 99999
bnumber = bnumber + 1
x = "b" & bnumber
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=proddta;", _
Destination:=Range(x))
y = ActiveCell.Text
.CommandText = Array( _
"SELECT f4101.imdsc1" & Chr(13) & "" & Chr(10) & "FROM AS400GOFI.PRODDTA.f4101 f4101" & Chr(13) & "" & _
Chr(10) & "WHERE (f4101.imlitm = " & y & ")" _
)
.Name = "Query from proddta"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
If ActiveCell.Value = "" Or ActiveCell.Value = "niet leverbaar" Then GoTo verder1
If ActiveCell.Value <> "" Or ActiveCell.Value <> "niet leverbaar" Then GoTo verder
verder1:
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
bnumber = bnumber + 1
GoTo verder:
verder:
Loop
End Sub

Dave Hawley · Mar 11th, 2002, 03:26 AM

Hi herman

how about

y = Format(CInt(y), "00000")
If IsNumeric(y) Then MsgBox Len(y)

Herman · Mar 11th, 2002, 04:33 AM

The code generates an overflow

i don't know what the code exactly does but i have a text and want a number
does cint convert text to number ??

Dave Hawley · Mar 11th, 2002, 04:45 AM

Yes it does, you might need Clng Function if you are getting an Overflow.

Herman · Mar 11th, 2002, 11:46 PM

Thanx for your support it's working.

Greetings.
Herman

Mar 10th, 2002, 11:18 AM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	Are you connecting using VBA ? if so, can you post your current code ? __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 10th, 2002, 11:13 AM	#1
Herman New Member Join Date: Feb 2002 Posts: 28	I Have a worksheet where in column a there are productnumbers like 045444 but excel doesnt; show the first zero so i had to make a format like 000000 now in vba i want to take connect to an sql server and find the description which belongs to 045444 but activecell.text gives 045444 that;s what i want to lookup as a value but this is now a text and activecell.value gives 45444 (but i need the extra zero)how can i look up 045444 as a value in my sql databse. ? can someone help

Mar 11th, 2002, 02:59 AM	#3
Herman New Member Join Date: Feb 2002 Posts: 28	yes i connect through vba and this is my code y should be the value with the zero in front Sub Macro3() ' ' Macro3 Macro ' Macro recorded 7-3-2002 by Plieger bnumber = 0 Do While ActiveCell.Value <> 99999 bnumber = bnumber + 1 x = "b" & bnumber With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=proddta;", _ Destination:=Range(x)) y = ActiveCell.Text .CommandText = Array( _ "SELECT f4101.imdsc1" & Chr(13) & "" & Chr(10) & "FROM AS400GOFI.PRODDTA.f4101 f4101" & Chr(13) & "" & _ Chr(10) & "WHERE (f4101.imlitm = " & y & ")" _ ) .Name = "Query from proddta" .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate If ActiveCell.Value = "" Or ActiveCell.Value = "niet leverbaar" Then GoTo verder1 If ActiveCell.Value <> "" Or ActiveCell.Value <> "niet leverbaar" Then GoTo verder verder1: ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate bnumber = bnumber + 1 GoTo verder: verder: Loop End Sub

Mar 11th, 2002, 03:26 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi herman how about y = Format(CInt(y), "00000") If IsNumeric(y) Then MsgBox Len(y)

Mar 11th, 2002, 04:33 AM	#5
Herman New Member Join Date: Feb 2002 Posts: 28	The code generates an overflow i don't know what the code exactly does but i have a text and want a number does cint convert text to number ??

Mar 11th, 2002, 04:45 AM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Yes it does, you might need Clng Function if you are getting an Overflow.

Mar 11th, 2002, 11:46 PM	#7
Herman New Member Join Date: Feb 2002 Posts: 28	Thanx for your support it's working. Greetings. Herman