Export MSFlexGrid Values to Excel.

Category:

Office/VBA

Type:

Snippets

Difficulty:

Intermediate

Author:

Henry aka PiR82k Erich III

Version Compatibility:

More information:

Requires Excel on the client machine, and a reference to the Excel object model in the project.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Public Sub FlexGrid_To_Excel(TheFlexgrid As MSFlexGrid, _
  TheRows As Integer, TheCols As Integer, _
  Optional GridStyle As Integer = 1, Optional WorkSheetName _
  As String)
    
Dim objXL As New Excel.Application
Dim wbXL As New Excel.Workbook
Dim wsXL As New Excel.Worksheet
Dim intRow As Integer ' counter
Dim intCol As Integer ' counter

If Not IsObject(objXL) Then
    MsgBox "You need Microsoft Excel to use this function", _
       vbExclamation, "Print to Excel"
    Exit Sub
End If

'On Error Resume Next is necessary because
'someone may pass more rows
'or columns than the flexgrid has

'you can instead check for this,
'or rewrite the function so that
'it exports all non-fixed cells
'to Excel

On Error Resume Next

' open Excel
objXL.Visible = True
Set wbXL = objXL.Workbooks.Add
Set wsXL = objXL.ActiveSheet

' name the worksheet
With wsXL
    If Not WorkSheetName = "" Then
        .Name = WorkSheetName
    End If
End With
    
' fill worksheet
For intRow = 1 To TheRows
    For intCol = 1 To TheCols
        With TheFlexgrid
            wsXL.Cells(intRow, intCol).Value = _
               .TextMatrix(intRow - 1, intCol - 1) & " "
        End With
    Next
Next

' format the look
For intCol = 1 To TheCols
    wsXL.Columns(intCol).AutoFit
    'wsXL.Columns(intCol).AutoFormat (1)
    wsXL.Range("a1", Right(wsXL.Columns(TheCols).AddressLocal, _
         1) & TheRows).AutoFormat GridStyle
Next

End Sub