Determining type of Clipboard data

[focuswiz]

I am trying to accommodate users with different preferences. I am asking them to paste their collected data into a spreadsheet we use for analysis. All of the columns represent equivalent data for each user. However, One user seems to extract their data in CSV format, one in an Actual Excel Workbook, and another seems to have Pure Text captured to their clipboard. The results of pasting from these three source types are not exactly what I expected, but I can deal with them. Each one captures between about 75 and 300 rows of data per day with 24 columns. Here is how each gets treated when pasted into the analysis sheet is described here:

A. Pure Text. When this data is pasted, they can do a [Ctrl]=[V] to paste the data without overwriting any of my spreadsheet's formatting. The date fields get stored in Excel Date format as do the times. Text and values are interpreted with Excel's normal defaults.

B. CSV File. When this data is pasted from Excel, even though the CSV data itself has no formatting, the paste will carry forward the display format from the Excel view of the CSV file, eliminating any changes in the destination. Since there are "hard" spaces between characters in the dates, these will not be interpreted as dates but as text. Times are treated property as Excel Time values because they lack any odd formatting.

C. Excel Export. Even though the format is claimed to be "Excel" every field is a text field. The dates still have a hard space in them, but even the times and other values are treated as plain text. Like the CSV file, a [Ctrl]+[V] will override any formatting in the destination sheet.​

I have tried teaching one to just paste but the others to paste special/values to avoid changing the formatting, but they sometimes forget. I have gotten past the issues with dates having hard spaces in them and times sometimes being times and sometimes being plain text.

I was thinking of creating a macro to interrogate the paste buffer and paste the data differently depending on the format, but I have not been able to figure out if the data in the paste buffer is Excel formatted or pure text.

It appears that ClipBoard.GetFormat returns a data type of particular data elements, but I am looking for the overall format of the data structure. Something that will tell me if the data contains Excel formatting or not. I apparently do not know what to Google to find this information. I was thinking of always doing a paste special/values, but it would be easier if I could convert some of the data that seems to prefer its own formatting.

At this point, I am leaning towards adding another sheet just for pasting and creating an intermediate sheet that analyzes which kind of problems are found in the pasted data and then carries over the "fixed" data to the final data sheet, but I would think there should be a way to interrogate the clipboard for this before the data is pasted.

Can someone suggest a way?

Thanks.

 

[rlv01]

At this point, I am leaning towards adding another sheet just for pasting and creating an intermediate sheet that analyzes which kind of problems are found in the pasted data and then carries over the "fixed" data to the final data sheet

That is the way I would approach it.

 

[focuswiz]

That is the way I would approach it.

Thanks.

I learned something while playing around. The VBA command worksheet.Cells.ClearContents only clears the clipboard if the clipboard contains Excel data. This implies that Excel handles the two types of clipboard data differently. Anyway, this was not that bad and will be easier to debug.

Sub PasteBufferContents()
    Dim wbThis As Workbook
    Dim wsTemp As Worksheet
    Dim wsTCSheet As Worksheet
    Set wbThis = ActiveWorkbook
    Set wsTemp = Sheets.Add
    Set wsTCSheet = wbThis.Sheets("TCSheet")
    
    Application.ScreenUpdating = False

'   Paste the data before clearing any contents
    wsTemp.Range("A1").PasteSpecial xlPasteAll
'   Now we can clear the destination sheet
    wsTCSheet.Cells.ClearContents
    
    If IsNumeric(Range("A4").Value2) Then 'This is the characteristic of data pasted directly to the clipboard
'       Clean up the formatting of data coming directly from the clipboard (in this case, nothing really)
    Else 'The data was copied from an Excel spreadsheet either formatted as a spreadsheet or a CSV file
        If IsNumeric(Range("D4").Value2) Then 'This is the characteristic of data taken from an Excel opened CSV file; the time (in D4)will be good but the date will contain hard spaces
'           Clean up the formatting of data coming from the CSV file via Excel
        Else 'This is from an Excel file exported from the application and both the date and time will be text fields
'           Clean up the formatting of data coming from Excel
        End If
    End If
    
'   Paste the cleaned up data into the destination sheet
    wsTemp.UsedRange.Copy
    With wsTCSheet
        .Select
        .Range("A1").Select
    End With
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'   Delete the Temp worksheet
    Application.DisplayAlerts = False
    wsTemp.Delete
    Application.DisplayAlerts = True
      
'   Leave us at the TCSheet
    wsTCSheet.Select
    
    Application.ScreenUpdating = True

End Sub

Thanks again.