• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

Excel Output

I'd like to output data from Visual Basic to an Excel Document. I don't want to create a CSV or anything like that, I'd like to create an XLS file.

I'm going to need to do things like change the formatting of the text I output, maybe put some color in. I think I have to use an Excel Object, but I'm not sure how to use it.

Any code showing me how to create a new Excel document and output data to it would be great. And if you can show me how to change font, color, and create freeze panes, I'll throw in some more points.

Thanks!
0
dankennedy
Asked:
dankennedy
  • 3
  • 2
1 Solution
 
dankennedyAuthor Commented:
I'm using Visual Basic 6.
0
 
puranik_pCommented:
Private Function GoToExcel()
   
    Dim oExcel
    Dim oWB
    Dim oWS
    Dim RowCount As Integer
    Dim ColCount As Integer
    Dim objRsMain As ADODB.Recordset
    Dim objRsSub As ADODB.Recordset
    Dim oField As ADODB.Field
    Dim FieldValue
   
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True
   
    Set oWB = oExcel.Workbooks.Add()
   
    Set oWS = oWB.Worksheets.Add()
   
    oWS.Columns(1).ColumnWidth = 10
    oWS.Columns(2).ColumnWidth = 25
   
    RowCount = 1
   
    Set objRsMain = GetRecordset(CLng(TxtBatch.Text))
   
    While Not objRsMain.EOF
       
        Set objRsSub = GetRowDetails(objRsMain("FLD_TBL_NAME"), objRsMain("FLD_TBL_FLD_NAME"), objRsMain("FLD_TBL_FLD_ID"))
       
        If Not objRsSub Is Nothing Then
       
            If Not objRsSub.EOF Then
           
                RowCount = RowCount + 1
                ColCount = 1
               
                oWS.Cells(RowCount, ColCount) = objRsMain("FLD_TBL_NAME")
                oWS.Cells(RowCount, ColCount).Font.Bold = True
               
                For Each oField In objRsSub.Fields
                    FieldValue = oField.Name
                    ColCount = ColCount + 1
                    oWS.Cells(RowCount, ColCount) = FieldValue
                    oWS.Cells(RowCount, ColCount).Interior.Color = RGB(160, 160, 164)
                Next
               
                RowCount = RowCount + 1
                ColCount = 1
                For Each oField In objRsSub.Fields
                    FieldValue = oField.Value
                    ColCount = ColCount + 1
                    oWS.Cells(RowCount, ColCount) = FieldValue
                    oWS.Cells(RowCount, ColCount).Font.Bold = True
                Next
           
            End If
        End If
        DoEvents
        objRsMain.MoveNext
    Wend

    If ColCount <= 26 Then
        oWS.Range("A1", Chr(ColCount + 64) & RowCount).Columns.AutoFit
    End If
       
End Function

0
 
puranik_pCommented:
I'm using two recordsets (given by two functions) to populate data.

Set objRsMain = GetRecordset(CLng(TxtBatch.Text))

Set objRsSub = GetRowDetails(objRsMain("FLD_TBL_NAME"), objRsMain("FLD_TBL_FLD_NAME"), objRsMain("FLD_TBL_FLD_ID"))

pls. make modifications as per you requirements.
hope to get more points as you said. ;-)
0
 
dankennedyAuthor Commented:
Thanks for your help.
0
 
puranik_pCommented:
:-O)
glad it helped.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now