vb6 Msflexgrid can sending to Excel Cell
This time we will create a small program how vb6 Msflexgrid can send data to a program in Microsoft Offrice MsExcel. The first step we have to do is. Create a new project, then a Msflexgrid control and CommandButton control from the component select Microsoft FlexGrid Control 6.0. as well as to add a CommandButton control to do the same steps. Then we add a reference to Microsoft Excel Object Reference uyaitu 11.0.
The trick is, on the Project main menu, the submenu at the bottom there is a sub menu named references, the menu function to add other references required by the program that we are making. And what we need now is a reference from MsExcel. Do a browse, then select Microsoft Excel 11.0 Object.
Up here we have save the project means that these references, and then we can use.
Add the following program code:
Private Sub Command2_Click()
Dim i As Integer
i = 1
With MSFlexGrid1
.FixedRows = 1
.FixedCols = 0
.Rows = i
.Cols = 3
.TextMatrix(i - 1, 0) = "Col 1"
.TextMatrix(i - 1, 1) = "Col 2"
.TextMatrix(i - 1, 2) = "Col 3"
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 now we have made 3 columns and first row as header. so now we have to send the data.
type the following command at a command button control:
Private Sub Command1_Click()
Dim XcLApp As Object
Dim XcLWB As Object
Dim XcLWS As Object
Dim i As Integer
Set XcLApp = CreateObject("Excel.Application")
Set XcLWB = XcLApp.Workbooks.Add 'opening new excel work book'
Set XcLWS = XcLWB.Worksheets.Add
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 now we have made 3 columns and first row as header. so now we have to send the data.
Type the following command at a command button control:
So what we do is we have created a new excel application. create workbooks and worksheets. then use the data from the control msflexgrid and sends it to the address of the worksheet. from 1 to 3 for my columns and rows. the trick is Addres_Excel function (). what he did was to send info such as 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 you what the letter. 1 mod 26 will return 0. 0 + ascii symbol for "A" will be "A".
so here is a function
Public Function Addres_Excel(ByVal lng_row As Long, ByVal lng_col As Long) As String
Dim modval As Long 'used to get the reminder'
Dim strval As String 'get the transferd letter'
modval = (lng_col - 1) Mod 26
strval = Chr$(Asc("A") + modval)
modval = ((lng_col - 1) \ 26) - 1
If modval >= 0 Then strval = Chr$(Asc("A") + modval) & strval 'if we have more then we add the letter'
Addres_Excel = strval & lng_row
End Function
Up here we have finished creating the application program sends data to a program from vb6 Msflexgrid MsExcel of Microsoft Office.
The trick is, on the Project main menu, the submenu at the bottom there is a sub menu named references, the menu function to add other references required by the program that we are making. And what we need now is a reference from MsExcel. Do a browse, then select Microsoft Excel 11.0 Object.
Up here we have save the project means that these references, and then we can use.
Add the following program code:
Private Sub Command2_Click()
Dim i As Integer
i = 1
With MSFlexGrid1
.FixedRows = 1
.FixedCols = 0
.Rows = i
.Cols = 3
.TextMatrix(i - 1, 0) = "Col 1"
.TextMatrix(i - 1, 1) = "Col 2"
.TextMatrix(i - 1, 2) = "Col 3"
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 now we have made 3 columns and first row as header. so now we have to send the data.
type the following command at a command button control:
Private Sub Command1_Click()
Dim XcLApp As Object
Dim XcLWB As Object
Dim XcLWS As Object
Dim i As Integer
Set XcLApp = CreateObject("Excel.Application")
Set XcLWB = XcLApp.Workbooks.Add 'opening new excel work book'
Set XcLWS = XcLWB.Worksheets.Add
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
vb6 Msflexgrid WITH EXCEL
So now we have made 3 columns and first row as header. so now we have to send the data.
Type the following command at a command button control:
So what we do is we have created a new excel application. create workbooks and worksheets. then use the data from the control msflexgrid and sends it to the address of the worksheet. from 1 to 3 for my columns and rows. the trick is Addres_Excel function (). what he did was to send info such as 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 you what the letter. 1 mod 26 will return 0. 0 + ascii symbol for "A" will be "A".
so here is a function
Public Function Addres_Excel(ByVal lng_row As Long, ByVal lng_col As Long) As String
Dim modval As Long 'used to get the reminder'
Dim strval As String 'get the transferd letter'
modval = (lng_col - 1) Mod 26
strval = Chr$(Asc("A") + modval)
modval = ((lng_col - 1) \ 26) - 1
If modval >= 0 Then strval = Chr$(Asc("A") + modval) & strval 'if we have more then we add the letter'
Addres_Excel = strval & lng_row
End Function
Up here we have finished creating the application program sends data to a program from vb6 Msflexgrid MsExcel of Microsoft Office.
