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

Problem in insertion of data in Excel

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


kadghar
Expert 100+
P: 1,295
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
Nov 26 '07 #2

100+
P: 198
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
Nov 27 '07 #3

kadghar
Expert 100+
P: 1,295
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
Nov 27 '07 #4

100+
P: 198
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
Nov 27 '07 #5

kadghar
Expert 100+
P: 1,295
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
Nov 27 '07 #6

100+
P: 198
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.Connection
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.Connection
Set rs = New ADODB.Recordset
con.Open "Driver={SQL Server};Server=" & txtserver & ";Database=master;Uid=sa;"
squery = "select * from output"
rs.Open squery, con, adOpenStatic, adLockBatchOptimistic, adCmdText

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Open("c:\147.xls")
Set xlws = xlwb.Worksheets("sheet1")
xlapp.Visible = True
xlapp.UserControl = 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(reccount, fldcount).Value = TransposeDim(recarray)
xlws.Cells(i, 2).Resize(reccount, fldcount).Value = rs.Fields("entry")
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(Xupper, 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
Dec 10 '07 #7

Post your reply

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