• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

exporting MSHFlexgrid to Excel

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

Open in new window

0
Stevenj2
Asked:
Stevenj2
  • 2
1 Solution
 
fhillyer1Commented:
i will try to change the name on the flegrid on the function

Public Function FlexGrid_To_Excel(MSFlexGrid1 As MSFlexGrid, MinRow, MaxRow, TheCols As Integer, _
  Optional GridStyle As Integer = 1, Optional WorkSheetName As String) As Boolean
- here you are specifying the name of the flexgrid as MSFlexGRid1
and you are actually giving that name to the control i guess
change the function name for the grid
Public Function FlexGrid_To_Excel(MSFG1 As MSFlexGrid, MinRow, MaxRow, TheCols As Integer, _
  Optional GridStyle As Integer = 1, Optional WorkSheetName As String) As Boolean
and replace all MsFlexgrid1 for msfg1

also note on the function that you are still referring to msflexgrid instead of mshflexgrid
should look like this
Public Function FlexGrid_To_Excel(MSFG1 As MSHFlexGrid, MinRow, MaxRow, TheCols As Integer, _
  Optional GridStyle As Integer = 1, Optional WorkSheetName As String) As Boolean
0
 
fhillyer1Commented:
also i noted that you are hardcoding the flexgrid from the form, is the name of the flexgrid on the form called MSFLexgrid1?

0
 
Stevenj2Author Commented:
yes,  thats the name of the Grid...
and it works perfectly !   I found a silly mistake I made myself by having the wrong form name in the code,  but your advice worked perfect !

thank you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now