Page 1 of 1

Sending Data From MSFlexGrid To Excel in VB6 Rate Topic: -----

#1 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 463
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Posted 01 February 2010 - 04:31 PM

hello :)
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!!

Is This A Good Question/Topic? 0
  • +

Replies To: Sending Data From MSFlexGrid To Excel in VB6

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 179
  • View blog
  • Posts: 1,599
  • Joined: 17-April 07

Posted 04 March 2010 - 07:47 AM

if you want to process the values in the cell when update to excel then the above method is the valuable one if you just transfer the data as it is in the
flexgrid use the clip function it copy the data to the clipboard and open the excel choose a cell and using the clipboard object paste the data
it will do faster than the above method
Was This Post Helpful? 0
  • +
  • -

#3 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 463
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Posted 05 March 2010 - 11:46 AM

i do it this way because i draw a table put a name of query etc. but i guess your right. i plan to do a tut. about it but no time right now :(

This post has been edited by NoBrain: 05 March 2010 - 11:48 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1