• Home
 · 
• Custom Controls/Forms/Menus
 · 
• To Export MSFlex Grid Data to EXCEL - Very nice Code, Working Superbly.

To Export MSFlex Grid Data to EXCEL - Very nice Code, Working Superbly.

To Export MSFlex Grid Data to EXCEL - Very nice Code, Working Superbly.

API Declarations


Dim therows As Integer
Dim thecols As Integer
Dim gridstyle As Integer
Dim xlapp As Excel.Application
Dim wbxl As Excel.Workbook
Dim wsxl As Excel.Worksheet
Dim introw As Integer 'counter
Dim intcol As Integer 'counter



Rate To Export MSFlex Grid Data to EXCEL - Very nice Code, Working Superbly.

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

Private Sub cmdExport_Click()
If Not IsObject(xlapp) Then
MsgBox "You need Microsoft Excel to use this function", _
vbExclamation, "Print to Excel"
Exit Sub
End If
On Error Resume Next

'TO Open Excel
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = True
xlapp.WindowState = xlMaximized
'xlapp.Workbooks.Add
Set wbxl = xlapp.Workbooks.Add
Set wsxl = xlapp.ActiveSheet
'Fill spreadsheet

For introw = 1 To FlxList.Rows
For intcol = 1 To FlxList.Cols
wsxl.Cells(introw, intcol).Value = _
FlxList.TextMatrix(introw - 1, intcol - 1) & " "
Next
Next
'Calculate total rows
therows = FlxList.Rows
'Format the sheet
thecols = 7       'Replace with the number of columns in your Application..
 For intcol = 1 To thecols
 wsxl.Columns(intcol).AutoFit       ' Columns to Auto fit the Data
 wsxl.Range("a1", Right(wsxl.Columns(thecols).AddressLocal, _
 1) & therows).AutoFormat gridstyle          ' to select the whole Range
 'Vertical alignment Centre.
  wsxl.Range("a1", Right(wsxl.Columns(thecols).AddressLocal, _
 1) & therows).VerticalAlignment = xlCenter  ' To Make vertical alignment of Cell as center
 Next
wsxl.Range("a1", Right(wsxl.Columns(thecols).AddressLocal, _
 1) & therows).Select           ' to select all cell which has data
 put_border    ' Call this sub to change the Borde style of the cell like table

' Additional Tasks

 ' If you want to format any Coulmn as Date Field 
    wsxl.Columns(5).NumberFormat = "mm/dd/yyyy"  ' Use the Coulmn Number you need to change
if you want Align Any Coulm Data to Center Format
 wsxl.Columns(1).HorizontalAlignment = xlCenter
'If you want Change the Row height of the Data in Excel Sheet
For introw = 1 To therows
    wsxl.Rows(introw).RowHeight = 20  ' specify the height to need
 Next

'If you want to Make the Heading Bold and Center
wsxl.Range("A1:G1").Select  ' Replace with your  Range 
With Selection
   .Font.Bold = True
   .HorizontalAlignment = xlCenter
End With
'If you want to the Excel sheet data to Fit to one Page widh & Height
 wsxl.PageSetup.FitToPagesWide = 1
 wsxl.PageSetup.FitToPagesTall = 1
End Sub  ' Code Finishes here

' This is to change  Border style
Sub put_border()
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub