Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Pass values in each column

  Asked By: Jasmine    Date: Mar 12    Category: MS Office    Views: 1103
  

Can any one please tell me how to i get the values from query in each column
but not in first row. As first row i use it to create the header of the file:

rngCell.Row = 1
Do While Not RS.EOF
xlWS.Range("A" & rngCell.Row).Value = RS!CFG_ACCOUNT
xlWS.Range("B" & rngCell.Row).Value = RS!REASON
xlWS.Range("C" & rngCell.Row).Value = RS!BANKRUPTCYCHAPTER
xlWS.Range("D" & rngCell.Row).Value = RS!CASENUM
xlWS.Range("E" & rngCell.Row).Value = RS!BKTFILEDATE
xlWS.Range("A" & rngCell.Row).Value = RS!DATEOFDEATH
xlWS.Range("A" & rngCell.Row).Value = RS!Comments
xlWS.Range("A" & rngCell.Row).Value = RS!ORIGINALACCOUNT
RS.movenext
Loop
---------------------------
ORIGINAL CODE
Private Sub QueryProcess()
Dim DBConn
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim rngColumn As Excel.Range
Dim rngCell As Excel.Range
Dim strSource As String
Dim mysheet As String

'ProgressBar2.Value = 1
'ProgressBar2.Value = 10
'ProgressBar2.Value = 20
'ProgressBar2.Value = 30

Dim RS
'This is Adaptive Server Enterprise Connection
Set DBConn = CreateObject("ADODB.Connection")
DBConn.Open "ODBC; Driver=Adaptive Server Anywhere 6.0; DSN=Debtmaster; "
uid=dm; pwd=,,PeAches..;"
Dim sSQL As String
sSQL = "select clt_ref_no as CFG_ACCOUNT, " & _
"(case " & _
" when r.status_code in ('400','404','407') then 'OVBK' " & _
" when r.status_code in ('210','420','430','470','630') then 'OVUN' "
& _
" when r.status_code in ('435') then 'OVRC' " & _
" when r.status_code in ('450') then 'OVDC' " & _
" when r.status_code in ('487') then 'OVFA' " & _
" when r.status_code = '491' then 'OCAC' " & _
" when r.status_code = '510' then 'OSIF' " & _
" when r.status_code='520' then 'OPIF' " & _
" else 'CHECK STATUS' " & _
" END) as REASON, " & _
"(SELECT LONGSTR FROM DBTRUDF U WHERE R.DEBTOR_ID=U.DEBTOR_ID AND
LABEL='BANKRUPTCYCHAPTER') AS BANKRUPTCYCHAPTER, " & _
"(SELECT LONGSTR FROM DBTRUDF U WHERE R.DEBTOR_ID=U.DEBTOR_ID AND
LABEL='CASENUMBER') AS CASENUM, " & _
"CONVERT(CHAR(10),(SELECT DATE1 FROM DBTRUDF U WHERE R.DEBTOR_ID=U.DEBTOR_ID
AND LABEL='FILEDATE'),101) AS BKTFILEDATE, " & _
"CONVERT(CHAR(10),(SELECT DATE1 FROM DBTRUDF U WHERE R.DEBTOR_ID=U.DEBTOR_ID
AND LABEL='DECEASEDDATE'),101) AS DATEOFDEATH, " & _
" STATUS_DESCR AS COMMENTS, " & _
" substring(DEBT_DESCR,(LOCATE(DEBT_DESCR,':',1)+1), 30) AS
ORIGINALACCOUNT " & _
"FROM DEBT_VIEW S JOIN DBTR R ON S.DEBT_ID=R.DEBTOR_ID " & _
"WHERE CLT_ID LIKE 'IDTC%' AND R.STATUS_CODE>='400' AND
R.STATUS_DATE>'2007-02-25' ; "
Set RS = DBConn.Execute(sSQL)
strSource = StripPath(x) & InputBox("Please Enter a Name for your file") &
".XLS"
Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open(strSource)
mysheet = xlApp.Worksheets(1).Name
Set xlWS = xlWb.Worksheets(mysheet)
xlWS.Range("A1").Value = "CFG_ACCOUNT"
xlWS.Range("B1").Value = "STATUS"
xlWS.Range("C1").Value = "BKT CAHPTER"
xlWS.Range("D1").Value = "BKT CASE#"
xlWS.Range("E1").Value = "BKT FILE DATE"
xlWS.Range("F1").Value = "DATE OF DEATH"
xlWS.Range("G1").Value = "COMMENTS"
xlWS.Range("H1").Value = "ORIGINAL ACCOUNT"
rngCell.Row = 1
Do While Not RS.EOF
xlWS.Range("A" & rngCell.Row).Value = RS!CFG_ACCOUNT
xlWS.Range("B" & rngCell.Row).Value = RS!REASON
xlWS.Range("C" & rngCell.Row).Value = RS!BANKRUPTCYCHAPTER
xlWS.Range("D" & rngCell.Row).Value = RS!CASENUM
xlWS.Range("E" & rngCell.Row).Value = RS!BKTFILEDATE
xlWS.Range("A" & rngCell.Row).Value = RS!DATEOFDEATH
xlWS.Range("A" & rngCell.Row).Value = RS!Comments
xlWS.Range("A" & rngCell.Row).Value = RS!ORIGINALACCOUNT
RS.movenext
Loop
xlWb.Close SaveChanges:=True
Set xlWb = Nothing
Set xlWS = Nothing
xlApp.Quit
Set xlApp = Nothing
Unload Me
ShellExecute 0, vbNullString, Chr(34) & strSource & Chr(34), vbNullString,
strSource, vbMaximizedFocus
End Sub

Private Sub Form_Load()
QueryProcess
End Sub

Public Function StripPath(strFullPath As String) As String
Dim intLoc As Integer
Dim strreturn As String
intLoc = InStrRev(strFullPath, "\")
If intLoc > 0 Then
strreturn = Left(strFullPath, intLoc)
Else
strreturn = "Bad File Path"
End If
StripPath = strreturn
End Function

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Landra Schmidt     Answered On: Mar 12

Make

rngCell.Row = 1
rngCell.Row = 2

 
Answer #2    Answered By: Alexander Bouchard     Answered On: Mar 12

But this increae the rows. How to i increase the columns?
Thanks.

 
Answer #3    Answered By: Erika Evans     Answered On: Mar 12

I think you need to do it like this. If this is not what you want try to
explain a bit more what you are trying to do.

rngCell.Row = 2
Do While Not RS.EOF
xlWS.Range("A" & rngCell.Row).Value = RS!CFG_ACCOUNT
xlWS.Range("B" & rngCell.Row).Value = RS!REASON
xlWS.Range("C" & rngCell.Row).Value = RS!BANKRUPTCYCHAPTER
xlWS.Range("D" & rngCell.Row).Value = RS!CASENUM
xlWS.Range("E" & rngCell.Row).Value = RS!BKTFILEDATE
xlWS.Range("F" & rngCell.Row).Value = RS!DATEOFDEATH
xlWS.Range("G" & rngCell.Row).Value = RS!Comments
xlWS.Range("H" & rngCell.Row).Value = RS!ORIGINALACCOUNT
RS.movenext
Loop

 
Answer #4    Answered By: Jermaine Powell     Answered On: Mar 12

You will also need to increment rngCell.row so put the following above LOOP

rngCell.row = rngCell.row + 1

 
Didn't find what you were looking for? Find more on Pass values in each column Or get search suggestion and latest updates.




Tagged: