-
Nov 15th, 2013, 02:15 AM
#1
Thread Starter
Registered User
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!
-
Nov 15th, 2013, 06:20 AM
#2
Re: Designing/Formatting Excel during exporting Data from vb6 application.
Moved From The CodeBank (which is for sharing code rather than posting questions )
-
Nov 15th, 2013, 06:25 AM
#3
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
-
Nov 15th, 2013, 07:29 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|