troubleshooting Question

exporting MSHFlexgrid to Excel

Avatar of Stevenj2
Stevenj2 asked on
Visual Basic Classic
3 Comments1 Solution331 ViewsLast Modified:
Hello experts,
The code I attached below is some code I use in another part of my application for a MSFlexgrid.
I would like to use it for the MSHFlexgrid,  and I can't seem to get the syntax correct in either where I call it,  of if I need to change something in the function.
I like to use this code because it formats the excel book for the user. and they love that
If you could help me modify this for my MSHFlex grid,  I would very much appriciate it..
Thanks...
Private Sub Command1_Click()
Call FlexGrid_To_Excel(MSFlexGrid1, 1, 5000, 20)
End Sub
*******************************
Public Function FlexGrid_To_Excel(MSFlexGrid1 As MSFlexGrid, MinRow, MaxRow, TheCols As Integer, _
  Optional GridStyle As Integer = 1, Optional WorkSheetName As String) As Boolean
     
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
Dim countofrows As Integer 'counter

If Not IsObject(objXL) Then
    MsgBox "You need to have Microsoft Excel installed to use this function", _
       vbExclamation, "Export to Excel Function"
       FlexGrid_To_Excel = False
    Exit Function
End If

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

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 1st row of worksheet with Flexgrid field names
For intRow = 1 To 1
    For intCol = 1 To TheCols + 1
        With FrmDayView.MSFlexGrid1
            wsXL.Cells(intRow, intCol).Value = _
               .TextMatrix(intRow - 1, intCol - 1) & " "
        End With
    Next
Next

'Fill worksheet with Flexgrid data
countofrows = 2     'Starts on second row worksheet and populates with flexgrid data
For intRow = MinRow To MaxRow   'number of times to loop
    For intCol = 1 To TheCols + 1
        With MSFlexGrid1
            wsXL.Cells(countofrows, intCol).Value = _
               .TextMatrix(intRow, intCol - 1) & " "
        End With
    Next
countofrows = countofrows + 1
Next

' format the look
For intCol = 1 To TheCols + 1
    wsXL.Columns(intCol).AutoFit
    'wsXL.Columns(intCol).AutoFormat (1)
    wsXL.Range("a1", Right(wsXL.Columns(TheCols + 1).AddressLocal, 1) & countofrows).AutoFormat GridStyle
Next
'MsgBox "Report Complete"
FlexGrid_To_Excel = True

End Function
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros