Problem in insertion of data in Excel

[veer]

Hello expert.
Can anyone help me by providing the method of inserting records into Excel sheet? The Excel sheet has three fields; Code, server1 and server2. If I insert the data without matching these three fields it works fine.
But I have no idea how I match the fields with the table record.
Eg. When I access the data from server1, it must be placed under the server1 column and when I access from server2 it must be under server2 field.
Please provide some help.

[kadghar]

Hello expert.
Can anyone help me by providing the method of inserting records into Excel sheet? The Excel sheet has three fields; Code, server1 and server2. If I insert the data without matching these three fields it works fine.
But I have no idea how I match the fields with the table record.
Eg. When I access the data from server1, it must be placed under the server1 column and when I access from server2 it must be under server2 field.
Please provide some help.

you can use CELLS (row, col) , define an integer, lets say J, and make it be 2 if it comes from server1 , 3 if it comes fron server2

this way you can make cells(i, J).value = YourInfo

HTH

[veer]

i am not properly understanding please describe it with some code
thanks

you can use CELLS (row, col) , define an integer, lets say J, and make it be 2 if it comes from server1 , 3 if it comes fron server2

this way you can make cells(i, J).value = YourInfo

HTH

[kadghar]

i am not properly understanding please describe it with some code
thanks

sure, lets say you have your excel application in an object : Obj1
the value you wanto to insert is in a variable: myValue
the integer J is 2 if its from server1 and its 3 if its from server2

then just write

obj1.cells(i,J) = myValue

where i is a long or an integer that indicates the row you want to put it. so, if it comes from server2, J will be 3, if you want the value tu be in cell C4 then

J=3
i=4
obj1.cells(i,J) = myValue

HTH

[veer]

Thanks its working
but my probleum is that every time i have to access two columns from every table wheather it is server one or server two, i, e Operator_id and record
the record column data must be placed under every server name but the Operator _id always shown under operator_id column
please provide some help so that operator_id must be under operator_id column
thanks

sure, lets say you have your excel application in an object : Obj1
the value you wanto to insert is in a variable: myValue
the integer J is 2 if its from server1 and its 3 if its from server2

then just write

obj1.cells(i,J) = myValue

where i is a long or an integer that indicates the row you want to put it. so, if it comes from server2, J will be 3, if you want the value tu be in cell C4 then

J=3
i=4
obj1.cells(i,J) = myValue

HTH

[kadghar]

Thanks its working
but my probleum is that every time i have to access two columns from every table wheather it is server one or server two, i, e Operator_id and record
the record column data must be placed under every server name but the Operator _id always shown under operator_id column
please provide some help so that operator_id must be under operator_id column
thanks

obj1.cells(i,J) = myValue
obj1.cells(i,1) = operator_id

[veer]

hello expert
i m not properly understanding your solution i am sending you my code please check it and tell me where i will put this code
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

obj1.cells(i,J) = myValue
obj1.cells(i,1) = operator_id