?
Solved

Excel Output

Posted on 2003-02-28
5
Medium Priority
?
140 Views
Last Modified: 2010-05-18
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
Comment
Question by:dankennedy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 2

Author Comment

by:dankennedy
ID: 8042546
I'm using Visual Basic 6.
0
 
LVL 14

Accepted Solution

by:
puranik_p earned 1000 total points
ID: 8042570
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
 
LVL 14

Expert Comment

by:puranik_p
ID: 8042593
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
 
LVL 2

Author Comment

by:dankennedy
ID: 8042672
Thanks for your help.
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 8042812
:-O)
glad it helped.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question