Results 1 to 40 of 40

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

  1. #1

    Thread Starter
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

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

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

    Flexgrid to Excel Example:
    VB Code:
    1. Private Sub FlexToExcel()
    2. Dim xlObject    As Excel.Application
    3. Dim xlWB        As Excel.Workbook
    4.        
    5.     Set xlObject = New Excel.Application
    6.  
    7.     'This Adds a new woorkbook, you could open the workbook from file also
    8.     Set xlWB = xlObject.Workbooks.Add
    9.                
    10.     Clipboard.Clear 'Clear the Clipboard
    11.     With MSFlexGrid1
    12.         'Select Full Contents (You could also select partial content)
    13.         .Col = 0               'From first column
    14.         .Row = 0               'From first Row (header)
    15.         .ColSel = .Cols - 1    'Select all columns
    16.         .RowSel = .Rows - 1    'Select all rows
    17.         Clipboard.SetText .Clip 'Send to Clipboard
    18.     End With
    19.            
    20.     With xlObject.ActiveWorkbook.ActiveSheet
    21.         .Range("A1").Select 'Select Cell A1 (will paste from here, to different cells)
    22.         .Paste              'Paste clipboard contents
    23.     End With
    24.    
    25.     ' This makes Excel visible
    26.     xlObject.Visible = True
    27. End Sub
    Excel To Flexgrid Example:
    VB Code:
    1. Private Sub ExcelToFlexgrid()
    2. Dim xlObject    As Excel.Application
    3. Dim xlWB        As Excel.Workbook
    4.        
    5.     Set xlObject = New Excel.Application
    6.     Set xlWB = xlObject.Workbooks.Open("C:\Book1.xls") 'Open your book here
    7.                
    8.     Clipboard.Clear
    9.     With xlObject.ActiveWorkbook.ActiveSheet
    10.         .Range("A1:F7").Copy 'Set selection to Copy
    11.     End With
    12.        
    13.     With MSFlexGrid1
    14.         .Redraw = False     'Dont draw until the end, so we avoid that flash
    15.         .Row = 0            'Paste from first cell
    16.         .Col = 0
    17.         .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
    18.         .ColSel = .Cols - 1
    19.         .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
    20.         .Col = 1            'Just to remove that blue selection from Flexgrid
    21.         .Redraw = True      'Now draw
    22.     End With
    23.        
    24.     xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
    25.    
    26.     'Close Excel
    27.     xlWB.Close
    28.     xlObject.Application.Quit
    29.     Set xlWB = Nothing
    30.     Set xlObject = Nothing
    31. 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.
    Last edited by jcis; Apr 3rd, 2006 at 06:54 AM.

  2. #2
    Member
    Join Date
    Aug 2006
    Posts
    46

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

    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......

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,727

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

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

  4. #4
    Member
    Join Date
    Aug 2006
    Posts
    46

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

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

  5. #5

    Thread Starter
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

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

    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.

  6. #6
    Member
    Join Date
    Aug 2006
    Posts
    46

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

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

  7. #7
    Member
    Join Date
    Aug 2006
    Posts
    46

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

    cooool that work very good thx alot

  8. #8
    Hyperactive Member
    Join Date
    Aug 2005
    Location
    Cochin, India
    Posts
    350

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

    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?
    DOK OCK : "The power of .net in the palm of my hand, nothing will stand in my way. Nothing."

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,727

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

    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.

  10. #10
    New Member
    Join Date
    Dec 2006
    Posts
    1

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

    The code is doing well. Thanks to jcis

  11. #11
    New Member
    Join Date
    Feb 2010
    Posts
    8

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

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

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,727

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

    Welcome to VBForums

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

  13. #13
    New Member
    Join Date
    Feb 2010
    Posts
    8

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

    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

  14. #14
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,727

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

    I presume you tried changing this line:
    Code:
            .Range("A1:F7").Copy 'Set selection to Copy
    ..to something like this:
    Code:
            .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:
    Code:
            .UsedRange.Copy 'Set selection to Copy

  15. #15
    New Member
    Join Date
    Feb 2010
    Posts
    8

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

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

  16. #16
    New Member
    Join Date
    Feb 2010
    Posts
    8

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

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

  17. #17
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,727

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

    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.

  18. #18
    New Member
    Join Date
    Feb 2010
    Posts
    8

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

    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

  19. #19
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,727

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

    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.

  20. #20
    New Member
    Join Date
    Mar 2010
    Posts
    3

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

    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!

  21. #21
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,727

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

    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:
    Code:
        With xlObject.ActiveWorkbook.ActiveSheet
    ..to this (which specifies the actual Workbook to use):
    Code:
        With xlWB.ActiveSheet
    To reduce random bugs further you should also specify the sheet within the file, which can be done like this:
    Code:
        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.

  22. #22
    New Member
    Join Date
    Mar 2010
    Posts
    3

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

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

  23. #23
    New Member
    Join Date
    Mar 2010
    Posts
    3

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

    Quote Originally Posted by bluered View Post
    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!

  24. #24

    Thread Starter
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

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

    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
    Last edited by jcis; Jun 16th, 2010 at 01:17 AM.

  25. #25
    New Member
    Join Date
    Mar 2010
    Posts
    10

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

    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?

  26. #26
    New Member
    Join Date
    Feb 2013
    Posts
    4

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

    This thread is very useful..
    but when i run this code it show some error...
    Name:  Untitleda.jpg
Views: 22567
Size:  8.3 KBName:  Untitledc.jpg
Views: 23093
Size:  49.1 KB
    thx in advance

  27. #27
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,342

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

    Do you have the FlexGrid control called "MSFlexGrid1" on the form?
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  28. #28
    New Member
    Join Date
    Feb 2013
    Posts
    4

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

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

  29. #29
    Registered User
    Join Date
    Feb 2013
    Posts
    1

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

    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...

  30. #30
    Banned
    Join Date
    Sep 2009
    Posts
    16

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

    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 ?

  31. #31
    New Member
    Join Date
    Nov 2013
    Posts
    7

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

    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?

  32. #32
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    4,699

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

    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

  33. #33
    New Member
    Join Date
    Nov 2013
    Posts
    7

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

    Quote Originally Posted by Arnoutdv View Post
    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?

  34. #34
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    4,699

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

    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?

  35. #35
    New Member
    Join Date
    Nov 2013
    Posts
    7

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

    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

  36. #36
    New Member
    Join Date
    Nov 2013
    Posts
    7

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

    what is wrong about this code

    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"
    Last edited by Siddharth Rout; Nov 13th, 2013 at 12:39 AM. Reason: Added Code Tags

  37. #37
    Registered User
    Join Date
    Nov 2013
    Posts
    2

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

    Quote Originally Posted by jcis View Post
    The advantage of this methods is that they work pretty fast, compared to send/bring data cell by cell.

    Flexgrid to Excel Example:
    VB Code:
    1. Private Sub FlexToExcel()
    2. Dim xlObject    As Excel.Application
    3. Dim xlWB        As Excel.Workbook
    4.        
    5.     Set xlObject = New Excel.Application
    6.  
    7.     'This Adds a new woorkbook, you could open the workbook from file also
    8.     Set xlWB = xlObject.Workbooks.Add
    9.                
    10.     Clipboard.Clear 'Clear the Clipboard
    11.     With MSFlexGrid1
    12.         'Select Full Contents (You could also select partial content)
    13.         .Col = 0               'From first column
    14.         .Row = 0               'From first Row (header)
    15.         .ColSel = .Cols - 1    'Select all columns
    16.         .RowSel = .Rows - 1    'Select all rows
    17.         Clipboard.SetText .Clip 'Send to Clipboard
    18.     End With
    19.            
    20.     With xlObject.ActiveWorkbook.ActiveSheet
    21.         .Range("A1").Select 'Select Cell A1 (will paste from here, to different cells)
    22.         .Paste              'Paste clipboard contents
    23.     End With
    24.    
    25.     ' This makes Excel visible
    26.     xlObject.Visible = True
    27. End Sub
    Excel To Flexgrid Example:
    VB Code:
    1. Private Sub ExcelToFlexgrid()
    2. Dim xlObject    As Excel.Application
    3. Dim xlWB        As Excel.Workbook
    4.        
    5.     Set xlObject = New Excel.Application
    6.     Set xlWB = xlObject.Workbooks.Open("C:\Book1.xls") 'Open your book here
    7.                
    8.     Clipboard.Clear
    9.     With xlObject.ActiveWorkbook.ActiveSheet
    10.         .Range("A1:F7").Copy 'Set selection to Copy
    11.     End With
    12.        
    13.     With MSFlexGrid1
    14.         .Redraw = False     'Dont draw until the end, so we avoid that flash
    15.         .Row = 0            'Paste from first cell
    16.         .Col = 0
    17.         .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
    18.         .ColSel = .Cols - 1
    19.         .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
    20.         .Col = 1            'Just to remove that blue selection from Flexgrid
    21.         .Redraw = True      'Now draw
    22.     End With
    23.        
    24.     xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
    25.    
    26.     'Close Excel
    27.     xlWB.Close
    28.     xlObject.Application.Quit
    29.     Set xlWB = Nothing
    30.     Set xlObject = Nothing
    31. 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?

  38. #38
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    4,699

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

    Have a look at this sample project on XtremeVBTalk

  39. #39
    New Member
    Join Date
    Jan 2016
    Posts
    6

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

    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?

  40. #40
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

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

    Quote Originally Posted by Arnoutdv View Post
    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:
    Code:
    http://web.archive.org/
    ,\ then in their address bar paste this:
    HTML Code:
    http://www.xtremevbtalk.com/showpost.php?p=1217133&postcount=35
    Last edited by clickman; Feb 1st, 2021 at 04:32 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width