Capturing the Paste message

[nasar]

I have some code which loads a data file using the code below. The size of the file is variable (each day) and on occassions when the size is large, I get the following message: "There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another program later?"

The problem arises when an inexperienced user gets this message and clicks on "No".

Ideally, I would like to be able to capture this programmatically and force a "Yes".

The code extract is:

Sub LoadData()
' This routine loads the data file created as the output of the SQL
' in to the sheet named RAW.
Dim fileToOpen
Dim LastRow As Integer
Dim lastcol As Integer
Dim CleanData As String
Dim CurrentDir As String
Dim i As Long

' check if the data file dbr_data.csv has been created. If not, abort
CurrentDir = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, "\"))
If Dir(CurrentDir & "dbr_data.csv") <> "" Then ' file exists

    ' clear the existing data from the RAW sheet
    With Sheets("RAW")
        .Cells(1, 1).CurrentRegion.ClearContents
    End With
    fileToOpen = CurrentDir & "dbr_data.csv" 
    Workbooks.OpenText Filename:=fileToOpen, DataType:=xlDelimited, Comma:=False, Other:=True, OtherChar:="|"
    ' a workbook with the name dbr_data with a single sheet has been created
    ' copy and paste the data from the dbr_data sheet to RAW sheet
    Windows("dbr_data.csv").ActiveSheet.Cells(1, 1).CurrentRegion.Copy
    Windows("dbr_data.csv").Close
    Windows("DBR.xls").Activate
    Sheets("RAW").Activate
    Range("A1").Activate
    ActiveSheet.Paste
Else
    MsgBox "The Data File dbr_data.csv is missing"
End If
End Sub

Any help and tips would be appreciated.

Regards
Nasar

 

[PHV]

Why not closing dbr_data.csv after the paste ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nasar]

Thanks PH. Unfortunately the warning message is still displayed.
It also has the (inexplicable) side effect of rendering the data in RAW in its original form, ie. with the delimiters still in place and the data in a column A.

 

[TonyJollans]

What happens if you set CutCopyMode to False?
Failing that, what about copying a small amount (a single cell) which should overwrite the large amount on the clipboard and suppress the message.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[