comparision between two columns

[veer]

Hello expert
I am making a program in which I want to compare the three columns of Excel sheet, ie. Operator_id , table1, table2. I want to insert those values in "table1 Column" which I accessed from table1 database and must match those codes which I have shown in Operator_id column.
Thanks.

[Killer42]

Could you please try to explain again what it is you're trying to do? I didn't get it. (And judging from the lack of responses, I may not be the only one.)

[veer]

Hello expert,
I mean to say that I have a program in which I am accessing two columns (operator _id and records) from four SQL servers and must be shown under the server name column into excel sheet but when I access the data from first server it works fine and when I access data from 2nd server it overwrites the one column of first server data. If you want to see my code then please tell me.
Thanks.

Could you please try to explain again what it is you're trying to do? I didn't get it. (And judging from the lack of responses, I may not be the only one.)

[debasisdas]

Yes, you can go ahead and post your code for reference of our experts.

[veer]

Hello expert.
Here is my code. Please check it and provide some other method.
[CODE=vb]Dim xlapp As Object
Dim xlwb As Object
Dim xlws As Object

Dim con As ADODB.Connectio n
Dim rs As ADODB.Recordset
Dim squery As String
Dim fldcount, reccount As Variant
Dim irow, icol As Integer
Dim recarray As Variant
Dim i As Long
i = 1
Dim j As Integer

Set con = New ADODB.Connectio n
Set rs = New ADODB.Recordset
con.Open "Driver={SQ L Server};Server= " & txtserver & ";Database=mast er;Uid=sa;"
squery = "select * from output"
rs.Open squery, con, adOpenStatic, adLockBatchOpti mistic, adCmdText

Set xlapp = CreateObject("E xcel.Applicatio n")
Set xlwb = xlapp.Workbooks .Open("c:\147.x ls")
Set xlws = xlwb.Worksheets ("sheet1")
xlapp.Visible = True
xlapp.UserContr ol = True
fldcount = rs.Fields.Count
recarray = rs.GetRows
reccount = UBound(recarray , 2) + 1
' Insert into excel sheet
While xlws.Cells(i, 1).Value <> ""
i = i + 1
Wend
If txtserver.Text = "Irish-vul" Then
j = 2
ElseIf txtserver.Text = "uk-vulcan" Then
j = 3
End If

xlws.Cells(i, j).Resize(recco unt, fldcount).Value = TransposeDim(re carray)
xlws.Cells(i, 2).Resize(recco unt, fldcount).Value = rs.Fields("entr y")
xlapp.Selection .CurrentRegion. Columns.AutoFit
xlapp.Selection .CurrentRegion. Rows.AutoFit
xlwb.SaveAs "C:\147.xls "
' Close ADO objects
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing

' Release Excel references
Set xlws = Nothing
Set xlwb = Nothing
Set xlapp = Nothing
End Sub

Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)
Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
Dim tempArray As Variant
Dim rcount As Variant

Xupper = UBound(v, 2)
Yupper = UBound(v, 1)

ReDim tempArray(Xuppe r, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = v(Y, X)
Next Y
Next X

TransposeDim = tempArray
End Function[/CODE]

Yes, you can go ahead and post your code for reference of our experts.

[Killer42]

No thoughts on this, anyone?

[QVeen72]

Hi,

Check this part of the Code :

[code=vb]
If txtserver.Text = "Irish-vul" Then
j = 2
ElseIf txtserver.Text = "uk-vulcan" Then
j = 3
End If
[/code]

Since you said, you have 4 Servers, You need to Check for 4 Names..
and you are checking only for 2.. You Should have another 2 If Conditions...
To Simplify use a "Select Case"

Regards
Veena