[RESOLVED] excel to flexgrid

[New2This]

I have posted a similar question a while back. I have since found some code in the forum that looks like what I need to import data from a worksheet into flexgrid. I think it was posted by Si-the-geek.

I have tested it in a userform (userform has a flexgrid and a command button). The worksheet I want to access sits within this excel workbook. The code I am using is;

        
Private Sub ExcelToFlexgrid()
   
        
        Clipboard.Clear
        With Application.Workbooks("Book1.xls").Worksheets("Sheet1")

            .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



Private Sub Command1_Click()
Call ExcelToFlexgrid
End Sub

I have 2 problems, when I run the code it comes up with the following error; runtime error 424 object required and highlights Clipboard.Clear

I have referenced the microsoft excel object library but it doesn't make a difference.

Secondly, how do I get it to import all data rather than a specified range (the number of rows will vary)

Many thanks in advance

[si_the_geek]

As this is within Excel itself, you should have posted in the Office Development forum - I have moved the thread there.

I think it was posted by Si-the-geek.

It's possible... the code definitely looks familiar, but the comments don't look like mine.

I have 2 problems, when I run the code it comes up with the following error; runtime error 424 object required and highlights Clipboard.Clear

The Clipboard object is not supported in Excel (or other Office applications as far as I know).

You will need to use a different method instead... perhaps by reading the range into an array, then putting that into the grid.

Secondly, how do I get it to import all data rather than a specified range (the number of rows will vary)

Instead of .Range("???"). , use .UsedRange. , which is a special range object containing the entire used area on the sheet.

[New2This]

Si- thanks

can you possibly show me how to read a range into an array? I have read about array, but don't quite understand what it does yet. I shall try to find info about the UsedRange.

[si_the_geek]

Si- thanks

can you possibly show me how to read a range into an array? I have read about array, but don't quite understand what it does yet.

As you don't know arrays yet, it probably isn't the best idea to use them here (unless you particularly want to learn them now)

For info about what an array is (and general usage), see the FAQ article What are arrays and how do I use them?

For an example of reading from a range directly to an array, see the Excel Tutorial link in my signature - it is in post #6 (Reading and writing data)


A simpler method would be to read a cell (or row) at a time, and put the value directly into the grid using .TextMatrix (or .AddItem)

I shall try to find info about the UsedRange.

It's the same as a normal Range(), but it automatically picks the cells for you (eg: if you have text in A6 and C1, it would use "A1:C6").

For example, in your code above you could change this:

.Range("A1:F7").Copy

to this:

.UsedRange.Copy

..not that a .Copy is actually useful to you here tho!

[New2This]

Thanks Si,

have had a quick look at the link to arrays (explained nice and simple which is good) and will read your tuturial next. 2 questions, will I no longer need to use ADO if I am using arrays? and how do you get an array into the flexgrid?

[si_the_geek]

will I no longer need to use ADO if I am using arrays?

I don't see how ADO is related to this...

If you were previously using ADO to read from the Excel file, then no you wont need it - but as you are coding within Excel itself, I don't see why you were using it before.

and how do you get an array into the flexgrid?

I was hoping you weren't going to ask that yet!

The 'easy' methods would to use a loop along with .TextMatrix or .AddItem , but the quicker method would be to do some manipulation of the array, and then use .Clip like in your first post.

I haven't worked it out yet, so I'll have a think about it as I get time later - remind me if I don't post back in the next 8 hours or so.

[New2This]

Si,

I don't see how ADO is related to this...

If you were previously using ADO to read from the Excel file, then no you wont need it - but as you are coding within Excel itself, I don't see why you were using it before.

sorry for confusion, I have been searching the forum and working through all sorts of different tutorials and kept coming across this 'ADO' which I thought I would have to use to connect to my excel spreadsheet, I did try to do it, but got even more confused -discovered I am not experienced enough for that yet).

I have just been working through both the arrays and your excel automation as suggested. It is starting to make sense, but when I try to apply it in my form I get errors which I am trying to work through.

I was hoping you weren't going to ask that yet!

The 'easy' methods would to use a loop along with .TextMatrix or .AddItem , but the quicker method would be to do some manipulation of the array, and then use .Clip like in your first post.

I haven't worked it out yet, so I'll have a think about it as I get time later - remind me if I don't post back in the next 8 hours or so.

sorry! I have used the TextMatrix to add textbox items to the flexgrid, but I wouldn't know how to do it with loops, so if you can show me, I would be very grateful - I am keen to learn!

[New2This]

Si,

were you able to think of a way I could achieve this? I can't seem to get off first base with it. I followed your tutorials but cant get the code to work. Any guidance would be most appreciate, particularly getting it into the flexgrid

[si_the_geek]

Sorry about the delay!

I have just been working through both the arrays and your excel automation as suggested. It is starting to make sense, but when I try to apply it in my form I get errors which I am trying to work through.

Good stuff.. it would have been better if you had posted your code and the error details (what the message was, and where it occurred), so we could have helped correct it, and explain what you had done wrong.

In case you don't know already, in our Classic VB FAQs is an article I wrote called "What does this error mean, and how do I fix it?" (via the first "fixing common errors" link in my signature), which explains the reasons and the usual corrections for several errors.

I have used the TextMatrix to add textbox items to the flexgrid, but I wouldn't know how to do it with loops, so if you can show me, I would be very grateful - I am keen to learn!

I looked at my tutorial again, and can see that the example given (with a MsgBox) wasn't actually too far away from what is needed.. however there is a 'minor' issue that would have caused you problems.

Here's a working example:

'Read everything into the array
Dim vArray As Variant
  vArray = oXLSheet.UsedRange.Value

'Set the number of rows/cols of the grid to match the data (and remove 'headers')
  With MSFlexGrid1
    .Rows = UBound(vArray, 1) + 0  'normally you would use +1 for these, but this array is
    .Cols = UBound(vArray, 2) + 0  '1-based [first element is vArray(1,1), usually vArray(0,0)]
    .FixedRows = 0
    .FixedCols = 0
'turn of drawing, so less flickering, and faster
    .Redraw = False
   

'use the TextMatrix property to put each item into the grid
Dim lngCol As Long, lngRow As Long
    For lngRow = 1 To UBound(vArray, 1)
      For lngCol = 1 To UBound(vArray, 2)
        '(we need the -1 here, as the array is (oddly) 1-based, but the grid is 0-based
        .TextMatrix(lngRow - 1, lngCol - 1) = vArray(lngRow, lngCol)
      Next lngCol
    Next lngRow

            
'turn drawing back on
    .Redraw = True
  End With

I had hoped to also use a version with .Clip (as that would be faster), but it seems that it won't save much overall, as we can't use Join on the array (it doesn't like multi-dimensional arrays), so would need to use string manipulation instead - which is slow.

[New2This]

Wow! it works and its fast!

I changed one tiny bit to point to the worksheet I need which was

vArray = Worksheets("MyData").UsedRange.Value

Many many thanks Si, you have no idea how long I have been trying to get this to work.

I shall now read the 'common errors' as it may help me resolve some other issues in my application. I will get into the habit of reading the bits in signatures as didn't realise until you pointed me to them that there was such useful stuff in there.

[New2This]

Thanks Si