Results 1 to 4 of 4

Thread: Designing/Formatting Excel during exporting Data from vb6 application.

  1. #1

    Thread Starter
    Registered User
    Join Date
    Nov 2013
    Posts
    2

    Designing/Formatting Excel during exporting Data from vb6 application.

    Im using this code to export data from my vb6 application to excel. Is it possible to apply color formatting in the cell/columns of the worksheet where my
    data will be exported?
    Code:
    Private Sub FlexToExcel()
    Dim xlObject    As Excel.Application
    Dim xlWB        As Excel.Workbook
            
        Set xlObject = New Excel.Application 
     
        'This Adds a new woorkbook, you could open the workbook from file also
        Set xlWB = xlObject.Workbooks.Add 
                    
        Clipboard.Clear 'Clear the Clipboard
        With MSFlexGrid1
            'Select Full Contents (You could also select partial content)
            .Col = 0               'From first column
            .Row = 0               'From first Row (header)
            .ColSel = .Cols - 1    'Select all columns
            .RowSel = .Rows - 1    'Select all rows
            Clipboard.SetText .Clip 'Send to Clipboard
        End With
                
        With xlObject.ActiveWorkbook.ActiveSheet
            .Range("A1").Select 'Select Cell A1 (will paste from here, to different cells)
            .Paste              'Paste clipboard contents
        End With
        
        ' This makes Excel visible
        xlObject.Visible = True
    End Sub
    Excel To Flexgrid Example:
    VB Code:
    Private Sub ExcelToFlexgrid()
    Dim xlObject    As Excel.Application
    Dim xlWB        As Excel.Workbook
            
        Set xlObject = New Excel.Application
        Set xlWB = xlObject.Workbooks.Open("C:\Book1.xls") 'Open your book here
                    
        Clipboard.Clear
        With xlObject.ActiveWorkbook.ActiveSheet
            .Range("A1:F7").Copy 'Set selection to Copy
        End With
           
        With MSFlexGrid1
            .Redraw = False     'Dont draw until the end, so we avoid that flash
            .Row = 0            'Paste from first cell
            .Col = 0
            .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
            .ColSel = .Cols - 1
            .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
            .Col = 1            'Just to remove that blue selection from Flexgrid
            .Redraw = True      'Now draw
        End With
            
        xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
        
        'Close Excel
        xlWB.Close
        xlObject.Application.Quit
        Set xlWB = Nothing
        Set xlObject = Nothing
    End Sub
    Thanks!

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Designing/Formatting Excel during exporting Data from vb6 application.

    Moved From The CodeBank (which is for sharing code rather than posting questions )

  3. #3
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Designing/Formatting Excel during exporting Data from vb6 application.

    Absolutely!!!!
    Check this out....(I have some examples I can share in a coupla hours if needed).

    http://www.vbforums.com/showthread.p...-Cell-from-VB6

  4. #4
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Designing/Formatting Excel during exporting Data from vb6 application.

    You can use this to change the Font Color:
    Code:
    worksheet.Range("a3:g3").Font.ColorIndex = 3 'change font color to red (look up colorindices for Excel fo rthe proper number to use)
    You can change the background color of cells like this:
    Code:
    worksheet.Cells(x, y).Interior.Color = vbYellow 'where x and y are the rows and columns of the worksheet

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width