By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,057 Members | 1,346 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,057 IT Pros & Developers. It's quick & easy.

comparision between two columns

100+
P: 198
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.
Nov 26 '07 #1
Share this Question
Share on Google+
6 Replies


Expert 5K+
P: 8,434
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.)
Nov 28 '07 #2

100+
P: 198
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.)
Dec 10 '07 #3

debasisdas
Expert 5K+
P: 8,127
Yes, you can go ahead and post your code for reference of our experts.
Dec 10 '07 #4

100+
P: 198
Hello expert.
Here is my code. Please check it and provide some other method.
Expand|Select|Wrap|Line Numbers
  1. Dim xlapp As Object
  2. Dim xlwb As Object
  3. Dim xlws As Object
  4.  
  5. Dim con As ADODB.Connection
  6. Dim rs As ADODB.Recordset
  7. Dim squery As String
  8. Dim fldcount, reccount As Variant
  9. Dim irow, icol As Integer
  10. Dim recarray As Variant
  11. Dim i As Long
  12. i = 1
  13. Dim j As Integer
  14.  
  15. Set con = New ADODB.Connection
  16. Set rs = New ADODB.Recordset
  17. con.Open "Driver={SQL Server};Server=" & txtserver & ";Database=master;Uid=sa;"
  18. squery = "select * from output"
  19. rs.Open squery, con, adOpenStatic, adLockBatchOptimistic, adCmdText
  20.  
  21. Set xlapp = CreateObject("Excel.Application")
  22. Set xlwb = xlapp.Workbooks.Open("c:\147.xls")
  23. Set xlws = xlwb.Worksheets("sheet1")
  24. xlapp.Visible = True
  25. xlapp.UserControl = True
  26. fldcount = rs.Fields.Count
  27. recarray = rs.GetRows
  28. reccount = UBound(recarray, 2) + 1
  29. ' Insert into excel sheet
  30. While xlws.Cells(i, 1).Value <> ""
  31.     i = i + 1
  32. Wend
  33. If txtserver.Text = "Irish-vul" Then
  34.     j = 2
  35. ElseIf txtserver.Text = "uk-vulcan" Then
  36.     j = 3
  37. End If
  38.  
  39. xlws.Cells(i, j).Resize(reccount, fldcount).Value = TransposeDim(recarray)
  40. xlws.Cells(i, 2).Resize(reccount, fldcount).Value = rs.Fields("entry")
  41. xlapp.Selection.CurrentRegion.Columns.AutoFit
  42. xlapp.Selection.CurrentRegion.Rows.AutoFit
  43. xlwb.SaveAs "C:\147.xls"
  44. ' Close ADO objects
  45. rs.Close
  46. con.Close
  47. Set rs = Nothing
  48. Set con = Nothing
  49.  
  50. ' Release Excel references
  51. Set xlws = Nothing
  52. Set xlwb = Nothing
  53. Set xlapp = Nothing
  54. End Sub
  55.  
  56. Function TransposeDim(v As Variant) As Variant
  57. ' Custom Function to Transpose a 0-based array (v)
  58.     Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
  59.     Dim tempArray As Variant
  60.     Dim rcount As Variant
  61.  
  62.     Xupper = UBound(v, 2)
  63.     Yupper = UBound(v, 1)
  64.  
  65.     ReDim tempArray(Xupper, Yupper)
  66.     For X = 0 To Xupper
  67.         For Y = 0 To Yupper
  68.             tempArray(X, Y) = v(Y, X)
  69.         Next Y
  70.     Next X
  71.  
  72.     TransposeDim = tempArray
  73. End Function
Yes, you can go ahead and post your code for reference of our experts.
Dec 10 '07 #5

Expert 5K+
P: 8,434
No thoughts on this, anyone?
Dec 12 '07 #6

QVeen72
Expert 100+
P: 1,445
Hi,

Check this part of the Code :

Expand|Select|Wrap|Line Numbers
  1. If txtserver.Text = "Irish-vul" Then
  2.     j = 2
  3. ElseIf txtserver.Text = "uk-vulcan" Then
  4.     j = 3
  5. End If
  6.  
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
Dec 12 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.