VB6 - Flexgrid to Excel / Excel to Flexgrid. Fast copy using the Clipboard.

[jcis]

The advantage of this methods is that they work pretty fast, compared to send/bring data cell by cell.

Flexgrid to Excel Example:

Private Sub FlexToExcel()
Dim xlObject    As Excel.Application
Dim xlWB        As Excel.Workbook
        
    Set xlObject = New Excel.Application 
 
    'This Adds a new woorkbook, you could open the workbook from file also
    Set xlWB = xlObject.Workbooks.Add 
                
    Clipboard.Clear 'Clear the Clipboard
    With MSFlexGrid1
        'Select Full Contents (You could also select partial content)
        .Col = 0               'From first column
        .Row = 0               'From first Row (header)
        .ColSel = .Cols - 1    'Select all columns
        .RowSel = .Rows - 1    'Select all rows
        Clipboard.SetText .Clip 'Send to Clipboard
    End With
            
    With xlObject.ActiveWorkbook.ActiveSheet
        .Range("A1").Select 'Select Cell A1 (will paste from here, to different cells)
        .Paste              'Paste clipboard contents
    End With
    
    ' This makes Excel visible
    xlObject.Visible = True
End Sub

Excel To Flexgrid Example:

Private Sub ExcelToFlexgrid()
Dim xlObject    As Excel.Application
Dim xlWB        As Excel.Workbook
        
    Set xlObject = New Excel.Application
    Set xlWB = xlObject.Workbooks.Open("C:\Book1.xls") 'Open your book here
                
    Clipboard.Clear
    With xlObject.ActiveWorkbook.ActiveSheet
        .Range("A1:F7").Copy 'Set selection to Copy
    End With
       
    With MSFlexGrid1
        .Redraw = False     'Dont draw until the end, so we avoid that flash
        .Row = 0            'Paste from first cell
        .Col = 0
        .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
        .ColSel = .Cols - 1
        .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
        .Col = 1            'Just to remove that blue selection from Flexgrid
        .Redraw = True      'Now draw
    End With
        
    xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
    
    'Close Excel
    xlWB.Close
    xlObject.Application.Quit
    Set xlWB = Nothing
    Set xlObject = Nothing
End Sub

This has been tested Using Windows XP-SP2, VB6-SP6, and Office XP (2002).
This code might need minor modifications when using a different version of Office.

[MANNO]

thx man for that usful code
but 1 other quistion what dll u r using cause it show some error...
"compile error : "
"user-defined type not defined"
thx in advance......

[si_the_geek]

You need a reference to "Microsoft Excel X.X Object library"

[MANNO]

yes I need the refrance can any one tell me what is it plz....

[jcis]

In VB6, go to Project Menu, then click on References, you'll see a list there, scroll to "Microsoft Excel X.X Object library" (X.X is your version number), Check it.

[MANNO]

ops srry I didnt get it at first...
but now its ok thx for ur support

[MANNO]

cooool that work very good thx alot

[jain_mj]

Hi,
I used ths code to read an excel file of 15,000 records into an array. I tried the same file with conventional cell by cell reading method. first one took 1:45 mins and second one took 2:20 mins. difference of 35 seconds. Is it lower than that expected by u with ur code?

[si_the_geek]

I would expect that the quickest way to put all your "records" into an array is to use the method shown in post #6 of my Excel tutorial (link in my signature), see the bit which uses a variable called vArray.

If you have problems getting it to work, please create a new thread about it in the Classic VB forum (and PM me a link) so we can discuss it there, rather than de-rail this thread.

[skahilu]

The code is doing well. Thanks to jcis

[bluered]

good day to all, can you teach me how to select many columns and rows to be transfer in flexgrid?..

[si_the_geek]

Welcome to VBForums

The code given does that already... unless you haven't explained your issue clearly.

[bluered]

yes, i tried your nice code but only one cell copied to the flexgrid..
i tried to edit your code to have a range to copy, but the compiler said an error..how should i do the copying of columns with 50-60 rows in it,i have no idea on how to formulate the codes..please help

[si_the_geek]

I presume you tried changing this line:

        .Range("A1:F7").Copy 'Set selection to Copy

..to something like this:

        .Range("A1:F60").Copy 'Set selection to Copy

That should work, but need a small amount of effort to use the relevant last row.

An alternative is to just copy the entire range that has data:

        .UsedRange.Copy 'Set selection to Copy

[bluered]

thanks a lot for the help..it works for now

[bluered]

one last thing sir, i need help for copying data from the cell of msflexgrid to my access database.. what should i do?

[si_the_geek]

That is not what this thread is about, so you should not be asking here.

Instead you should post a new thread in an apt forum (such as Database Development), giving details of your situation rather than just a single sentence.

[bluered]

Ok ive done it..but this is my very last question sir.. what should i do so that my program is user driven event? i tried to make parameters in your function so that the range will be depending on the inputted value given by the user.

i made two textboxes and every textbox.text are assigned in string variables, but when i run my program i ends with an error..

i dont know how to dwell with this part..
.Range(a : b).Copy

[si_the_geek]

Those questions do not relate directly to the first post of this thread, so do not belong here - they belong in new thread(s) instead.

[ginetic20]

i think this question is related to this topic. im using the same method, but the problem is how bout if i have 3 sheets in excel? what should i do to load the other sheet? for example have sheet1, sheet2 and sheet3. this code only view the first sheet, how am i going to load the sheet2?? pls help thx!

[si_the_geek]

Welcome to VBForums

Assuming you are using the first code snippet in post #1 above, simply to reduce random bugs (accidentally writing to a different file that is currently open in Excel) you should change this line:

    With xlObject.ActiveWorkbook.ActiveSheet

..to this (which specifies the actual Workbook to use):

    With xlWB.ActiveSheet

To reduce random bugs further you should also specify the sheet within the file, which can be done like this:

    With xlWB.Worksheets(1)

...just change 1 to the sheet to interact with (1 is the first).


Hopefully that should be enough information for you to work it out.

[ginetic20]

ohhh! isee it went well thx a lot it helps. im not familiar with this excel thing in VB. well thx bro!

[ginetic20]

Ok ive done it..but this is my very last question sir.. what should i do so that my program is user driven event? i tried to make parameters in your function so that the range will be depending on the inputted value given by the user.

i made two textboxes and every textbox.text are assigned in string variables, but when i run my program i ends with an error..

i dont know how to dwell with this part..
.Range(a : b).Copy

the question is what kind of user driven event exactly??? there are plenty of function to use in this one actually? tab sheet, the location of excel? the range. i think you should be more specific or put your codes and problem. but in a different thread. then put your link here so we can help!

[jcis]

Recently I've been asked how to perform the Excel to Flexgrid but not using a fixed range like in the previous example, he wanted to use the range that's being used in the Excel worksheet itself, making it more dinamic and changing the flexgrid's rows and columns count according to that Range, here is the code to do it this way:http://www.vbforums.com/showpost.php...90&postcount=3

[pipo123]

Hi,

i used the Flexgrid to Excel function and it work fine. but one thing is that the date format i extract is in dd/mm/yy but when copy over to excel its in mm/dd/yy format. how can i fix this?

[nurul elena]

This thread is very useful..
but when i run this code it show some error...

thx in advance

[Nightwalker83]

Do you have the FlexGrid control called "MSFlexGrid1" on the form?

[nurul elena]

thank you...i missed the number behind -- MSFlexgrid1

[Clintoy123]

Hello every one iam just a newbie to flexgrid...1st the code really works for me..sir i wonder what is the code for allowing to adjust width and column and to allow mergingof Columns in msexcel...

[fabel58]

I used the code "Excel to Flexgrid" but excel 2010 hang on in windows xp (also windows 7 and/or windows 8) task manager: why ?

[Hossam]

Hi
first of all I wanna thank you for efforts and for your genius code, but I 've a question..
I'm trying to read a range more than 138000 rows "and it will be more than that" from Excel , but it doesn't work, I think it's a clipboard issue, so If there any thing else to do it fast avoiding the clipboard?

[Arnoutdv]

You can do it in chunks. For example 10000 rows per action.

Is there a reason why you want to show so many records in the MSFlexGrid?
The MSFlexGrid also has a maximum number of cells it can show.
http://support.microsoft.com/kb/191006

[Hossam]

You can do it in chunks. For example 10000 rows per action.

Is there a reason why you want to show so many records in the MSFlexGrid?
The MSFlexGrid also has a maximum number of cells it can show.
http://support.microsoft.com/kb/191006

thanks for your quick respond
I wish i can send you the file i wanna to add it to grid but it's more than 16M.B.
and about MSFlexGrid, I'll use MSHFLexGrid
- about "10000 rows per action" how to do it?! or do you mean looping?

[Arnoutdv]

Yes I meant by looping.

Can you please explain why you want to show 138000 rows in a FlexGrid to the user?
Do you expect them something to do with this enormous list?

[Hossam]

it's a weather and climate data "temperature, rain, wind speed,....,etc" for 100 hundred years for every single hour, "that's one file" :O and if we talk about 25 files . actually it's a text file and I opening it to excel component with "Delimited" & "Space" options then to grid in-order to make some calculate on it, to output that file form hourly data to daily and monthly and yearly, can you imagine how huge the data is!!!
anyways I do the loop and it worked great, but I'm talking about wasting time, it takes so so long to add one file

[Hossam]

what is wrong about this code

Select Case LoopInt
            Case 0
                Spreadsheet1.Range("A2:M14000").Copy
                With MsGrd
                    .Rows = 140000
                    .Redraw = False     'Dont draw until the end, so we avoid that flash
                    .Row = 1          'Paste from first cell
                    .Col = 0
                    .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
                    .ColSel = .Cols - 1
                    .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
                    .Col = 1            'Just to remove that blue selection from Flexgrid
                    .Redraw = True      'Now draw
                End With

            Case 1
                Spreadsheet1.Range("A14001:M28000").Copy
                With MsGrd
                    .Rows = 140000
                    .Redraw = False     'Dont draw until the end, so we avoid that flash
                    .Row = 14001         'Paste from first cell
                    .Col = 0
                    .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
                    .ColSel = .Cols - 1
                    .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
                    .Col = 1            'Just to remove that blue selection from Flexgrid
                    .Redraw = True      'Now draw
                End With
End select

it didn't catch all records, I think the error in ".RowSel = .Rows - 1 "' and ".ColSel = .Cols - 1"

[jeromerpg11]

The advantage of this methods is that they work pretty fast, compared to send/bring data cell by cell.

Flexgrid to Excel Example:

Private Sub FlexToExcel()
Dim xlObject    As Excel.Application
Dim xlWB        As Excel.Workbook
        
    Set xlObject = New Excel.Application 
 
    'This Adds a new woorkbook, you could open the workbook from file also
    Set xlWB = xlObject.Workbooks.Add 
                
    Clipboard.Clear 'Clear the Clipboard
    With MSFlexGrid1
        'Select Full Contents (You could also select partial content)
        .Col = 0               'From first column
        .Row = 0               'From first Row (header)
        .ColSel = .Cols - 1    'Select all columns
        .RowSel = .Rows - 1    'Select all rows
        Clipboard.SetText .Clip 'Send to Clipboard
    End With
            
    With xlObject.ActiveWorkbook.ActiveSheet
        .Range("A1").Select 'Select Cell A1 (will paste from here, to different cells)
        .Paste              'Paste clipboard contents
    End With
    
    ' This makes Excel visible
    xlObject.Visible = True
End Sub

Excel To Flexgrid Example:

Private Sub ExcelToFlexgrid()
Dim xlObject    As Excel.Application
Dim xlWB        As Excel.Workbook
        
    Set xlObject = New Excel.Application
    Set xlWB = xlObject.Workbooks.Open("C:\Book1.xls") 'Open your book here
                
    Clipboard.Clear
    With xlObject.ActiveWorkbook.ActiveSheet
        .Range("A1:F7").Copy 'Set selection to Copy
    End With
       
    With MSFlexGrid1
        .Redraw = False     'Dont draw until the end, so we avoid that flash
        .Row = 0            'Paste from first cell
        .Col = 0
        .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
        .ColSel = .Cols - 1
        .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
        .Col = 1            'Just to remove that blue selection from Flexgrid
        .Redraw = True      'Now draw
    End With
        
    xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
    
    'Close Excel
    xlWB.Close
    xlObject.Application.Quit
    Set xlWB = Nothing
    Set xlObject = Nothing
End Sub

This has been tested Using Windows XP-SP2, VB6-SP6, and Office XP (2002).
This code might need minor modifications when using a different version of Office.

Hi jcis, i used your code in my application and it works perfectly! Is it possible to mimic the design of my MSFLEXGrid when i export the data to excel? Im talking about the
header design such as background color of each cell, gradient effects to alternating rows, font type and many more. Is it possible?

[Arnoutdv]

Have a look at this sample project on XtremeVBTalk

[warkev]

This is very useful to me and I thank you for it. In grabbing data from the excel file is there a way to pick random rows?

[clickman]

Have a look at this sample project on XtremeVBTalk

This is a very interesting thread. Is there an alternative place to look at the information in the xtremevbtalk link. It appears that the site is no longer active.. Is there an archive for their materials too?

UPDATE Go here:

http://web.archive.org/

,\ then in their address bar paste this:

http://www.xtremevbtalk.com/showpost.php?p=1217133&postcount=35