i will show you how to send data from MSFLexGrid to Excel using VB6. First we need to add a flexgrid control from components select Microsoft FlexGrid Control 6.0 or click browse and select Windows\system32\MSFLXGRD.OCX. Then we add excel reference form Project=>References add Microsoft Excel 11.0 Object Library.
so lets begin with adding items in our flexgrid
Private Sub Command2_Click() Dim i As Integer 'Counter for the rows of the FlexGrid' i = 1 With MSFlexGrid1 .FixedRows = 1 'fixed row for headers of the columns' .FixedCols = 0 .Rows = i .Cols = 3 'set 3 columns' 'Making Headers for the columns' .TextMatrix(i - 1, 0) = "Col 1" .TextMatrix(i - 1, 1) = "Col 2" .TextMatrix(i - 1, 2) = "Col 3" 'make 9 rows and fill them with some values' For i = 2 To 10 .Rows = i .TextMatrix(i - 1, 0) = "Val " & i .TextMatrix(i - 1, 1) = i .TextMatrix(i - 1, 2) = (i * 10) Next i End With End Sub
so by now we have made 3 columns set the first row as headers and fill 9 rows with some value. so now we have to send the table to excel
Private Sub Command1_Click() Dim XcLApp As Object 'used for excel application' Dim XcLWB As Object 'used for excel work book' Dim XcLWS As Object 'used for excel work sheet' Dim i As Integer ' counter for the rows of the flexgrid' Set XcLApp = CreateObject("Excel.Application") 'creating new excel application' Set XcLWB = XcLApp.Workbooks.Add 'opening new excel work book' Set XcLWS = XcLWB.Worksheets.Add 'opening new excel worksheet' 'taking data from flexgrid and sendting it to excel' With MSFlexGrid1 For i = 1 To .Rows - 1 XcLWS.Range(Addres_Excel(i, 1)).Value = .TextMatrix(i, 0) 'add the text from col1' XcLWS.Range(Addres_Excel(i, 2)).Value = .TextMatrix(i, 1) 'add the text from col2' XcLWS.Range(Addres_Excel(i, 3)).Value = .TextMatrix(i, 2) 'add the text from col3' Next i End With XcLApp.Visible = True End Sub
so what we do is we open new excel app. create a workbook and worksheet. then using the fore cycle we get the data from msflexgrid control and send it to worksheets addresses. from 1 to 3 for columns and i rows. the trick is the function Addres_Excel(). what he do is sending info like row 1 and col 1 and transfer it to 'A1'. For example if we send 1 for column 1 will be divided by 26 and the mod will show what letter is. 1 mod 26 will return 0. 0 + the ascii symbol for "A" will be "A".
so here is the function
Public Function Addres_Excel(ByVal lng_row As Long, ByVal lng_col As Long) As String 'this function is used to send the columns from grid to excel' 'make column header to look like the letters used in excel' 'for example for col 1 the first column we will send "1" and will return "A"' Dim modval As Long 'used to get the reminder' Dim strval As String 'get the transferd letter' modval = (lng_col - 1) Mod 26 'using mode we get the reminder. 26 is for the letters in engl.' strval = Chr$(Asc("A") + modval) 'using the reminder we get the letter' modval = ((lng_col - 1) \ 26) - 1 'check to see if it is not addres like "AA"' If modval >= 0 Then strval = Chr$(Asc("A") + modval) & strval 'if we have more then we add the letter' Addres_Excel = strval & lng_row 'return the value to the function' End Function
so here it is.
Probably works with all kind of grids. with little changes!!