by Gulzar (3 Submissions)
Category: Custom Controls/Forms/Menus
Compatability: Visual Basic 5.0
Difficulty: Unknown Difficulty
Originally Published: Wed 17th March 2004
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
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
VBcoders
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
No comments have been posted about To Export MSFlex Grid Data to EXCEL - Very nice Code, Working Superbly.. Why not be the first to post a comment about To Export MSFlex Grid Data to EXCEL - Very nice Code, Working Superbly..
