Thanks for your reply, Sam.
I've now modified my code as follows, but am getting a 'Compile error: Sub or Function not defined at
S3 = GetText("formatId3") ' No markers
Private Sub AggdBut_Click()
Dim Excel As Excel.Application, Workbook As Excel.Workbook, Worksheet As Excel.Worksheet, i As Integer
Dim ExcelOpen As Boolean
Dim DataObj As New MSForms.DataObject
' Set default text strings
Dim S1 As String
Dim S2 As String
Dim S3 As String
Dim S4 As String
S1 = "The following markers are listed" & vbCr & vbCr
S2 = "These are the records For the past eighteen months" & vbCr & vbCr
S3 = "There are no markers" & vbCr & vbCr
S4 = "There are no records" & vbCr & vbCr
With DataObj
.SetText S1, "FormatId1"
.PutInClipboard
.SetText S2, "FormatId2"
.PutInClipboard
.SetText S3, "FormatId3"
.PutInClipboard
.SetText S4, "FormatId4"
.PutInClipboard
S1 = vbNullString
S2 = vbNullString
S3 = vbNullString
S4 = vbNullString
End With
' Check if Excel is open
On Error Resume Next
Set Excel = GetObject(, "Excel.Application") 'Select Excel if open
If Err.Number <> 0 Then 'If Excel is not already open, then open it
Set Excel = CreateObject("Excel.Application")
Else
ExcelOpen = True 'An indicator so we know whether to close Excel or not when finished
End If
On Error GoTo 0
' Open workbook Triage
On Error Resume Next
Set Workbook = Excel.Workbooks.Open(ThisDocument.Path & "/Triage.xlsm")
Workbook.Application.DisplayAlerts = False
If Workbook Is Nothing Then
Beep
MsgBox "Unable To open file!"
On Error GoTo 0
GoTo CleanUp
End If
On Error GoTo 0
' First deal with the worksheet Markers
With Workbook.Worksheets("Markers")
'See if there's anything to copy
If .Cells(1, 1) = vbNullString Then
.GetFromClipboard
S3 = GetText("formatId3") ' No markers
Else: .GetFromClipboard
S1 = GetText("formatId1") ' There are markers
End If
End With
' Secondly deal with the worksheet History
With Workbook.Worksheets("History")
'See if there's anything to copy
If .Cells(1, 1) = vbNullString Then
.GetFromClipboard
S4 = GetText("formatId3") ' No history
Else: .GetFromClipboard
S2 = GetText("formatId1") ' There is history
End If
End With
' Paste everything that has been copied into TextBox3
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
TextBox3.Text = clipboard.GetText(1)
CleanUp:
If ExcelOpen = False Then Excel.Quit 'close Excel if we started it, otherwise leave open
Workbook.Application.DisplayAlerts = False
End Sub
' Clear clipboard
Sub ClearClipboard()
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
End Sub