What is Access doing to my clipboard????

[RichStone]

I have a database in Access 2010 where in various different areas I use a simple piece of code to copy the content of a field to the clipboard so that it can be pasted in another program. This is fine until I come to paste it into Excel 2010, where for some reason the clipboard is cleared.

Firstly, here's my code (the field I am copying from is CopyToXL):

Private Sub spreadsheet_Click()
'On Error GoTo Exit_spreadsheet_Click

    CopyToXL.SetFocus
    DoCmd.RunCommand acCmdCopy
    
    'Set openExcel = CreateObject("Excel.Application")
    'openExcel.Visible = True
    '
    'On Error Resume Next
    '
    'openExcel.UserControl = True
    'ExcelPath = Forms![startup]![ExcelPath]
    'openExcel.Workbooks.Open ExcelPath

    Me.Reconciled = True
    
Exit_spreadsheet_Click:
End Sub

To test this function, I commented out the lines as shown and tried pasting into WordPad. That works fine. However, remove the comment markers and it doesn't!

The 'Reconciled' line just places a tick in a box on the main screen once, in theory, the field has been copied...

Help... please?
:banghead:

 

[JHB]

To test this function, I commented out the lines as shown and tried pasting into WordPad.
:banghead:

For testing, did you try to open Excel (not from code) and paste?
I don not have Excel 2010, only Excel 2000, and in that version the code works ok, I can paste the content from the clipboard.

 

[RichStone]

Without the commented code, it pastes anywhere I like!

The database was originally written in Access 2000 where the function worked perfectly. Before the recent upgrade to Access 2010 it was running fine in Access 2007 too.

It seems that it's only when the commented out code is active that the clipboard contents are lost, so it must be something to do with Access 2010 and that I need a new way of opening it whilst retaining the clipboard content.

 

[JHB]

By me the code works in MS-Access 2010, (of cause when the Excel sheet open you have to position the cursor in the cell you want to copy to, and then choose paste, because there is no code for that in your code!).

 

[RichStone]

This is really weird, and very annoying! I have now made the field I am copying visible so that I can copy it manually, ie highlight the contents and Ctrl-C. That works every time, and for the moment will have to be the method I use, even though it looks scruffy. It just doesn't make sense that the button which is programmed to do the same can't do it...

Re opening and pasting into the spreadsheet, the spreadsheet opens automatically, but I do click in the start cell and paste manually. That's fine as others are able to open and amend the spreadsheet too so it would be incredibly complicated for the database to know where to paste.

 

[JHB]

.. I have now made the field I am copying visible so that I can copy it manually, ie highlight the contents and Ctrl-C. ...

Clear it has to be visible, else you can't set focus on it and in your code you've the following line, (you'll get an error).

CopyToXL.SetFocus

 

[RichStone]

Sorry, I didn't make that quite clear. The field was always visible but I'd sized it so it couldn't be seen. As I said in my original problem, with the Excel open code commented out, the copy function is working fine. It's definitely something to do with opening Excel that is causing the issue.

EDIT:

After doing further investigations, I can definitely confirm that it is the opening of Excel using the code that is clearing the clipboard. I had the data I wanted to copy to Excel in a visible box on my form where I manually copied it using Ctrl-C. I then opened Wordpad and used Ctrl-V where it copied in perfectly. I then went back to my form and copied the data again using exactly the same method and opened Excel manually. I then used Ctrl-V and it copied perfectly again. The third time, I copied the data using the same method again but this time had the commented out text active, which meant Excel opened automatically. The clipboard was emptied. I could not paste the data in anything!