Copying from datagrid

[colin]

Hi All,

Can anyone tell me how I could copy the contents of a datagrid to the clipboard. I need a way of transferring the data from the grid to an Excel worksheet.

Many Thanks,

Colin

[dbrogan]

to copy the text onto the clipboard, use:

Clipboard.SetText Screen.ActiveForm.ActiveControl.SelText

to paste it, use:

Screen.ActiveForm.ActiveControl.SelText = Clipboard.GetText()

hope that helps

David

[Edneeis]

Why not just have Excel open the same recordset?

[colin]

Thanks to you both,

I have decided to use both solutions in my imlementation.

Colin

[Jannie]

Hi Colin

I have the same question about copying from a datagrid to excel.

I tried the code from the replies you receive, but I must be doing something wrong as I cannot get it to work. I only get it to copy one cell and not all the recordsets in the datagrid.

Have you managed to copy it properly. If you have, would you kindly forward me the code you used to copy this.

Highly appreciated.

Thanks
Jannie

[beachbum]

Hi
You can simply built a comma delimited file and sent to the clipboard... here is some code that u can adjust.... ps one thing to note is that string concatenation is slow and so is a good idea to build a number of intermediary strings and add together... ie rather than building a 100 row string.. build 10 x 10.. it is much faster
Regards
Stuart

Sub CopyGrid()
    Dim llngRows As Long
    Dim llngCols As Long
    Dim lstrClipCopy As String
    
    lstrClipCopy = ""
    With Grid1
          For llngRows = 0 To .rows
                For llngCols = 0 To .Cols
                      lstrClipCopy = lstrClipCopy & Trim$(.TextMatrix(llngRows, llngCols))
                      If llngCols <> .Cols Then lstrClipCopy = lstrClipCopy & vbTab
                Next
          Next
     End With
 
    'Send to clipboard
    Clipboard.Clear
    Clipboard.SetText lstrClipCopy, 1
    
    'Clear memory
    lstrClipCopy = ""
End Sub

[Jannie]

Originally posted by beachbum
Hi
You can simply built a comma delimited file and sent to the clipboard... here is some code that u can adjust.... ps one thing to note is that string concatenation is slow and so is a good idea to build a number of intermediary strings and add together... ie rather than building a 100 row string.. build 10 x 10.. it is much faster
Regards
Stuart

Sub CopyGrid()
    Dim llngRows As Long
    Dim llngCols As Long
    Dim lstrClipCopy As String
    
    lstrClipCopy = ""
    With Grid1
          For llngRows = 0 To .rows
                For llngCols = 0 To .Cols
                      lstrClipCopy = lstrClipCopy & Trim$(.TextMatrix(llngRows, llngCols))
                      If llngCols <> .Cols Then lstrClipCopy = lstrClipCopy & vbTab
                Next
          Next
     End With
 
    'Send to clipboard
    Clipboard.Clear
    Clipboard.SetText lstrClipCopy, 1
    
    'Clear memory
    lstrClipCopy = ""
End Sub

Hi

Thanks for your reply.

I copied this to the project and I get some errors.I am still doing something wrong.
I have a command button, a datagrid and a adodc on my form using the Nwind database and the customers table.
Here is my code:

Dim xl As Application
Sub CopyGrid()
Dim llngRows As Long
Dim llngCols As Long
Dim lstrClipCopy As String

lstrClipCopy = ""
With DataGrid1
For llngRows = 0 To .Row
For llngCols = 0 To .Col
lstrClipCopy = lstrClipCopy & Trim$(llngRows, llngCols)
If llngCols <> .Col Then lstrClipCopy = lstrClipCopy & vbTab
Next
Next
End With

'Send to clipboard
Clipboard.Clear
Clipboard.SetText lstrClipCopy, 1

xl.Range("A1") = Clipboard.GetText
'Clear memory
lstrClipCopy = ""
End Sub

Private Sub GetExcel()

Set xl = New Application
xl.Visible = True
xl.Workbooks.Add

End Sub

Private Sub Command1_Click()
GetExcel
CopyGrid
End Sub

Please help!!

Thanks
Jannie

[beachbum]

Hi
Sorry i had cut and pasted this code and pulled bits out here and there to make simple.. pulled out too much!! haha

Anyway the rows and cols in a grid are zero based and so u need to subtract 1 from the upper limit

For llngRows = 0 To .Rows - 1
            For llngCols = 0 To .Cols - 1

I notice in ur code that u left the 'S' off of Rows and Cols in ur loops. This is important. In regard to the Excel stuff, i cant help cos i have never done any VBA stuff or things with Office objects

Let me know how u go
Stuart

[beachbum]

Just noticed that u also left the 'S' off of .Cols in this line

If llngCols <> .Cols Then lstrClipCopy = lstrClipCopy & vbTab

Let me know what errors u get if u get any
If errors are to do with Excel try this. Run the code with all Excel stuff commented out. Then open Excel and simply Paste the clipboard. See if that works
Regards
Stuart

[Jannie]

Originally posted by beachbum
Just noticed that u also left the 'S' off of .Cols in this line

If llngCols <> .Cols Then lstrClipCopy = lstrClipCopy & vbTab

Let me know what errors u get if u get any
If errors are to do with Excel try this. Run the code with all Excel stuff commented out. Then open Excel and simply Paste the clipboard. See if that works
Regards
Stuart

Hi Stuart

It gives me a error of "Compile error - Wrong number of arguments or invalid property assignment" and highlights the "Trim$".

I will attach the file for you.

Thanks for your help

Jannie

[beachbum]

Hi Jannie
I have this awful feeling that you are retyping what i give you... lol save urself the trouble and just Copy and Paste the code from here to VB. It works just fine. So, ur prob is just another typing error in that line.
Regards
Stuart

'You typed
lstrClipCopy = lstrClipCopy & Trim$(llngRows, llngCols)
 
'Should have been, as per original post
lstrClipCopy = lstrClipCopy & Trim$(.TextMatrix(llngRows, llngCols))

[Jannie]

Originally posted by beachbum
Hi Jannie
I have this awful feeling that you are retyping what i give you... lol save urself the trouble and just Copy and Paste the code from here to VB. It works just fine. So, ur prob is just another typing error in that line.
Regards
Stuart

'You typed
lstrClipCopy = lstrClipCopy & Trim$(llngRows, llngCols)
 
'Should have been, as per original post
lstrClipCopy = lstrClipCopy & Trim$(.TextMatrix(llngRows, llngCols))

Hi Stuart

I have pasted your replies but I get errors.

If I use ".textmatrix" it gives me a error saying Method or data member not found and even just".text gives me an error.Also using (s)in cols and rows gives me an error.

I must be doing something stupid.
Did you manage to run it with the attachment I sent, because if it works with you I am missing the point somewhere.

Thanks for all your replies and trying to help me fixing this thing I do wrong.


Thanks
Jannie

[beachbum]

Hi Jannie
It is prob just the type of control ... which grid are u using? The newer lighter grids are Flexgrid and Hierarchical Flex grid. The code will work with Flexgrid where it was copied from. I havent looked at properties for other grids so not sure there.

I cant use ur file attachment as u have only sent the Binary file .FRX... u need to send the .FRM file (same named file)

Regards
Stuart

PS Flexgrid and HFlex can be added by:
1) Project menu
2) Components
3) Check Microsoft Flexgrid Control
OR Microsoft Hierarchical Flexgrid Control

[Jannie]

Originally posted by beachbum
Hi Jannie
It is prob just the type of control ... which grid are u using? The newer lighter grids are Flexgrid and Hierarchical Flex grid. The code will work with Flexgrid where it was copied from. I havent looked at properties for other grids so not sure there.

I cant use ur file attachment as u have only sent the Binary file .FRX... u need to send the .FRM file (same named file)

Regards
Stuart

PS Flexgrid and HFlex can be added by:
1) Project menu
2) Components
3) Check Microsoft Flexgrid Control
OR Microsoft Hierarchical Flexgrid Control

Hi Stuart

Yes you were right. It works with the flexigrid, but I am using the HeadClick event of the datagrid. I have read some help files and came up with some code. I now get the datagrid to copy info to excel, but I am battling to copy all the cells of the datagrid. Could you please maybe check my code to see where I am going wrong and how I can fix it.

I am using a datagrid,command button and an adodc control with the Nwind database and the employees table.

My code is as follows:

Private Sub GetExcel()

Set xl = New Application
xl.Visible = True
xl.Workbooks.Add

End Sub

Private Sub Command1_Click()
GetExcel

Dim ISelBook As Variant
Dim sCellVal As Variant
Dim x As Variant
Dim Icols As Variant
Dim mRows

For Icols = 0 To DataGrid1.VisibleCols - 1
x = Icols
Next Icols

For mRows = 0 To DataGrid1.VisibleRows - 1
DataGrid1.SelBookmarks.Add DataGrid1.RowBookmark(mRows)
Next mRows

For ISelBook = 0 To DataGrid1.SelBookmarks.Count - 1
sCellVal = DataGrid1.Columns(x).CellValue(DataGrid1.SelBookmarks(ISelBook))
Clipboard.SetText sCellVal, 1
xl.Range("A1 ") = Clipboard.GetText
Next ISelBook

End Sub

It now only copies the last record's last colum into excel.It seems the loops are working ,but I am not putting the loops together the right way that it copies all the records to excel.

Please help!!

[beachbum]

Hi Jannie
LOL dont sound so desperate!! OK 2 ways to go about this. I havent ever used the Datagrid control so am by no means an expert on its use!!

1) I looked at the Headclick event and if that is the only reason u are using the datagrid control then it is easy to change to a flexgrid and just check which column was clicked in the click event.

2) Don't know about ur use of bookmarks etc. Are u sure this is the right reading of it? I only looked at help files for a few mins but i am sure that there must be easier way to get cell values. I think bookmarks is only if you want to get whole rows of vals direct from database as against what is already in the grid but not sure. I assume it is just the .Text value using the .Col and .Row properties to set which cell is being read.

In regard to your loops they are a bit mixed up. You need to have your processing within the loops and to loop thru rows first and then columns... the following is pseudo code becos i dont know the datagrid but maybe someone else that uses it can help

with Datagrid1
      For llngRow = 0 to .Rows - 1
            For llngCol = 0 to .Cols -1
                  .Row = llngRow
                  .Col = llngCol
                  lstrClip = lstrClip & .Text & vbtab
            next
            lstrClip = lstrClip & vbcrlf
      next
end with

and then use the clipboard Clear, SetText stuff i gave u before. As i said this is a guess cos i dont use that control but see how u go.
Regards
Stuart

[beachbum]

Hi again Jannie
I see that datagrid doesnt have .Cols or .Rows properties and that the .Col or .Row property sets the pointer position. I just tried out a stupid sample of code on a datagrid and although it sorta worked i would think that there must be a far better way.

Private Sub Command1_Click()
    With DataGrid1
        For llngrows = 0 To 10 'How do u count rows and cols?
            For llngcols = 0 To 1 
                .Row = llngrows
                .Col = llngcols
 
                lstrtext = lstrtext & .Text & vbTab
            Next
            lstrtext = lstrtext & vbCrLf
        Next
    End With
    
    Me.Print lstrtext
End Sub

Go to Flexgrid where i can help!! haha
Regards
Stuart

[Jannie]

Originally posted by beachbum
Hi again Jannie
I see that datagrid doesnt have .Cols or .Rows properties and that the .Col or .Row property sets the pointer position. I just tried out a stupid sample of code on a datagrid and although it sorta worked i would think that there must be a far better way.

Private Sub Command1_Click()
    With DataGrid1
        For llngrows = 0 To 10 'How do u count rows and cols?
            For llngcols = 0 To 1 
                .Row = llngrows
                .Col = llngcols
 
                lstrtext = lstrtext & .Text & vbTab
            Next
            lstrtext = lstrtext & vbCrLf
        Next
    End With
    
    Me.Print lstrtext
End Sub

Go to Flexgrid where i can help!! haha
Regards
Stuart

Hi Stuart

Thanks a mil. This code did the job. I have recently completed my MCSD ,but this type of practical experience and assistance you cannot get in the classroom. You have helped me a great deal and I cannot express the joy I felt when this code ran perfectly. Just shows how much I still need to learn.

Thanks once again.

Jannie

[Jannie]

Originally posted by Jannie



Hi Stuart

Thanks a mil. This code did the job. I have recently completed my MCSD ,but this type of practical experience and assistance you cannot get in the classroom. You have helped me a great deal and I cannot express the joy I felt when this code ran perfectly. Just shows how much I still need to learn.

Thanks once again.

Jannie

Hi Stuart

I wonder if you could help me with a little bit more.

Your code worked and copied the info from the datagrid to excel, but could you maybe help me with the code that also includes the columnheaders of the datagrid. The current code do not copy the headers to the clipboard.

I will appreciate your help

Thanks
Jannie

[beachbum]

Hi Jannie
You keep picking on me about this datagrid control!! lol I have never ever ever used it... I just looked at help file for 2 secs and saw the columns property. So, i added this loop to the original one. This should work but as i said i have no idea about this control.
Regards
Stuart

Private Sub Command1_Click()
    With DataGrid1
        [b]For llngcols = 0 To 1
            lstrtext = lstrtext & .Columns(llngcols).Caption & vbTab
        Next
        lstrtext = lstrtext & vbCrLf[/b]
        
        For llngrows = 0 To 10 'How do u count rows and cols?
            For llngcols = 0 To 1
                .Row = llngrows
                .Col = llngcols
 
                lstrtext = lstrtext & .Text & vbTab
            Next
            lstrtext = lstrtext & vbCrLf
        Next
    End With
    
    Me.Print lstrtext
End Sub

[Jannie]

Originally posted by beachbum

Private Sub Command1_Click()
    With DataGrid1
        [b]For llngcols = 0 To 1
            lstrtext = lstrtext & .Columns(llngcols).Caption & vbTab
        Next
        lstrtext = lstrtext & vbCrLf<b>Hi Jannie
You keep picking on me about this datagrid control!! <img src="images/smilies/tongue.gif" border="0" alt="" title="Stick Out Tongue" class="inlineimg" /> lol I have never ever ever used it... I just looked at help file for 2 secs and saw the columns property. So, i added this loop to the original one. This should work but as i said i have no idea about this control. <img src="images/smilies/wink.gif" border="0" alt="" title="Wink" class="inlineimg" /> 
Regards
Stuart
</b>
        
        For llngrows = 0 To 10 'How do u count rows and cols?
            For llngcols = 0 To 1
                .Row = llngrows
                .Col = llngcols
 
                lstrtext = lstrtext & .Text & vbTab
            Next
            lstrtext = lstrtext & vbCrLf
        Next
    End With
    
    Me.Print lstrtext
End Sub

[/B]

Thanks again!!!

Jannie

[Jannie]

Hi Stuart

Me again.

Compliments of the season.

Would you know why I get a error when the datagrid shows 20 records because of the size on the form,but actually there is 35 records and as soon as the loop gets to 20 records the loop does not count properly and then it gives me "Run Time error 6148 , Invalid row number"

This happens as soon as run the loop to copy the datagrid to excel.

Please help

Thanks
Jannie

Originally posted by beachbum

Private Sub Command1_Click()
    With DataGrid1
        [b]For llngcols = 0 To 1
            lstrtext = lstrtext & .Columns(llngcols).Caption & vbTab
        Next
        lstrtext = lstrtext & vbCrLf<b>Hi Jannie
You keep picking on me about this datagrid control!! <img src="images/smilies/tongue.gif" border="0" alt="" title="Stick Out Tongue" class="inlineimg" /> lol I have never ever ever used it... I just looked at help file for 2 secs and saw the columns property. So, i added this loop to the original one. This should work but as i said i have no idea about this control. <img src="images/smilies/wink.gif" border="0" alt="" title="Wink" class="inlineimg" /> 
Regards
Stuart
</b>
        
        For llngrows = 0 To 10 'How do u count rows and cols?
            For llngcols = 0 To 1
                .Row = llngrows
                .Col = llngcols
 
                lstrtext = lstrtext & .Text & vbTab
            Next
            lstrtext = lstrtext & vbCrLf
        Next
    End With
    
    Me.Print lstrtext
End Sub

[/B]

[Jannie]

Originally posted by Jannie
Hi Stuart

Me again.

Compliments of the season.

Would you know why I get a error when the datagrid shows 20 records because of the size on the form,but actually there is 35 records and as soon as the loop gets to 20 records the loop does not count properly and then it gives me "Run Time error 6148 , Invalid row number"

This happens as soon as run the loop to copy the datagrid to excel.

Please help

Thanks
Jannie







Hi Stuart

Please help me with this error I am getting.
Like I say as soon as there is more records than is shown on the form I get this error.

Please help.
Thanks
Jannie