Excel VBA code breaks after saving document. (ActiveCell.Address & ActiveCell.Name)-CodePudding

I have the following script to copy range of the active cell to clipboard and it works fine untill I save the document (without closing it) after which it breaks:

Sub getCellSheetAndAddress()
Dim Sheet As String
Dim Address As String
Dim myData As DataObject

Sheet = ActiveSheet.Name
Address = ActiveCell.Address

Set myData = New DataObject
myData.SetText (Sheet   "!"   Address)
myData.PutInClipboard
Set myData = Nothing

End Sub

I can use this macro to get cell range and then paste it but when I save the excel and run the macro again what is being pasted into cell are two blank squares rather than the cell location.

What is the reason behind it and how to solve it?

Many thanks

CodePudding user response：

You can use functions like this:

Function SetClipBoardText(ByVal Text As Variant) As Boolean
    CreateObject("htmlfile").ParentWindow.ClipboardData.SetData "Text", Text
End Function

Function GetClipBoardText() As String
    On Error Resume Next
    GetClipBoardText = CreateObject("htmlfile").ParentWindow.ClipboardData.getData("Text")
End Function

or look at the SetClipBoardData and GetClipBoardData API functions.