Option Explicit
#If VBA7 = 0 Then
Public Enum LongPtr:[_]:End Enum
#End If
Private Const PtrNull As LongPtr = 0
Private Const GMEM_MOVEABLE = &H2
Private Const GMEM_ZEROINIT = &H40
Private Const GHND = (GMEM_MOVEABLE Or GMEM_ZEROINIT) 'Use for hwnd
Private Const NAME_MAX_LENGTH = 1024
#If VBA7 Then
Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As LongPtr) As Long
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As Long
Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As LongPtr) As LongPtr
Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
Private Declare PtrSafe Function wstrlen Lib "kernel32" Alias "lstrlenW" (ByVal lpString As LongPtr) As Long
Private Declare PtrSafe Function wstrcpy Lib "kernel32" Alias "lstrcpyW" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
Private Declare PtrSafe Function RegisterClipboardFormat Lib "user32" Alias "RegisterClipboardFormatA" (ByVal lpString As String) As LongPtr
Private Declare PtrSafe Function EnumClipboardFormats Lib "user32" (ByVal wFormat As LongPtr) As LongPtr
Private Declare PtrSafe Function GetClipboardFormatName Lib "user32" Alias "GetClipboardFormatNameA" (ByVal wFormat As LongPtr, ByVal lpString As String, ByVal nMaxCount As Long) As LongPtr
Private Declare PtrSafe Function GlobalFree Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
#Else
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function RegisterClipboardFormat Lib "user32" Alias "RegisterClipboardFormatA" (ByVal lpString As String) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
Private Declare Function GetClipBoardData Lib "user32" Alias "GetClipboardData" (ByVal wFormat As Long) As Long
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function EnumClipboardFormats Lib "user32" (ByVal wFormat As Long) As Long
Private Declare Function GetClipboardFormatName Lib "user32" Alias "GetClipboardFormatNameA" (ByVal wFormat As Long, ByVal lpString As String, ByVal nMaxCount As Long) As Long
Private Declare Function GlobalFree Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Private Declare Function wstrlen Lib "kernel32" Alias "lstrlenW" (ByVal lpString As Long) As Long
Private Declare Function wstrcpy Lib "kernel32" Alias "lstrcpyW" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
#End If
Private Sub ClearContentsByXMLPaste_test()
Debug.Print Sheet1.[A1:C20].Value(11)
' ClearContentsByXMLPaste [A1:C60], 6, 4
End Sub
Sub ClearContentsByXMLPaste(ByVal table As Range, Optional ByVal stepRows& = 1, Optional ByVal ClearRows&)
On Error Resume Next
Dim a, i&, k&, r&, lr&, lr2&, fr&, fr2&, lc&
Dim vh$, re, ms, s1$, ri&, cr&, kr&, ss$, ss2$
lr = table.Rows.CountLarge
lc = table.Columns.CountLarge
a = table.Value
stepRows = IIf(stepRows < 1, 1, stepRows)
ClearRows = IIf(ClearRows < 1, 1, ClearRows)
cr = stepRows + ClearRows
Set re = CreateObject("VBScript.RegExp")
With re: .Global = 1: .IgnoreCase = 1: .MultiLine = 1
End With
vh = table.Value(11)
re.Pattern = "<!--(.*?)-->|\r?\n\s*\B"
If re.test(vh) Then vh = re.Replace(vh, "")
re.Pattern = "<Row( ss:Index=""(\d+)"")?([^>]*>.*?</Row>)"
Set ms = re.Execute(vh)
If ms.Count = 0 Then Exit Sub
r = 0
ss = Left$(vh, ms(0).firstIndex)
ss2 = Mid$(vh, ms(ms.Count - 1).firstIndex + ms(ms.Count - 1).Length + 1)
lr2 = ms.Count - 1
For i = 0 To lr2
s1 = ms(i).submatches(0)
If s1 <> "" Then ri = CLng(ms(i).submatches(1))
r = IIf(s1 = "", r + 1, ri)
fr2 = (r - 1) \ cr
fr = ((r - 1) Mod cr) + 1
If fr > stepRows Then
If s1 <> "" Then kr = ri
Else
If kr > 0 Then
ss = ss & "<Row ss:Index=""" & kr & """" & ms(i).submatches(2)
Else
If s1 <> "" And r > stepRows Then
ss = ss & "<Row ss:Index=""" & CStr(ri) & """" & ms(i).submatches(2)
Else
ss = ss & ms(i)
End If
End If
End If
Next
DoEvents: Application.Goto table(1, 1), False
ClipboardSetXML encodeTextXML2(ss & "<Row ss:Index=""" & CStr(lr) & """></Row>" & ss2)
DoEvents: CreateObject("WScript.Shell").SendKeys "^v", False
End Sub
Private Function encodeTextXML(ByVal Text$)
Dim L, i, x, t, m
x = 1
L:
L = Len(Text)
For i = x To L
t = Mid(Text, i, 1): m = AscW(t)
If m < 0 Then m = m + 65536
Select Case m
Case Is > 127, 60, 62, 10, 13, 34, 39
Text = Replace(Text, t, "&#" & CStr(m) & ";")
x = i + Len(m) + 2
GoTo L
End Select
Next
encodeTextXML = Text
End Function
Private Function encodeTextXML2(ByVal Text$)
Dim L, i, x, t, m
x = 1
L:
L = Len(Text)
For i = x To L
t = Mid(Text, i, 1): m = AscW(t)
If m < 0 Then m = m + 65536
Select Case m
Case Is > 127
Text = Replace(Text, t, "&#" & CStr(m) & ";")
x = i + Len(m) + 2
GoTo L
End Select
Next
encodeTextXML2 = Text
End Function
Private Sub ClipboardSetXML(ByVal xml As String)
Dim lpMemory As LongPtr
Dim hMemory As LongPtr
Dim wLen As Long
Dim RetVal As Variant
Dim memoryIsLocked As Boolean
Dim memoryIsAllocated As Boolean
Dim clipBoardIsOpen As Boolean
Dim thisClipboardFormatNumber As Long
On Error GoTo ErrorHandler
' Lâìy ðôò dài, bao gôÌm caÒ môòt phâÌn bôÒ sung cho vbNullChar õÒ cuôìi.
wLen = Len(xml) + 1
' ’ Thêm môòt giá triò rôÞng vào cuôìi
xml = xml & vbNullChar
' ’ Câìp phát môòt sôì bôò nhõì
hMemory = GlobalAlloc(GHND, wLen + 1)
If hMemory = PtrNull Then
Err.Raise vbObjectError + 1001, "vbaClipboard", "Unable To allocate memory."
Else
memoryIsAllocated = True
End If
lpMemory = GlobalLock(hMemory)
If lpMemory = PtrNull Then
' Ném môòt lôÞi
Err.Raise vbObjectError + 1001, "vbaClipboard", "Unable To lock memory."
Else
memoryIsLocked = True
End If
' Sao chép chuôÞi cuÒa chúng tôi vào bôò nhõì biò khóa.
RetVal = lstrcpy(lpMemory, xml)
' ÐýÌng gýÒi bôò nhõì biò khóa clipboard.
RetVal = GlobalUnlock(hMemory)
' Nêìu phâÌn trýõìc ðýa ra lôÞi, nó seÞ ðýõòc xýÒ lyì trong TriÌnh xýÒ lyì lôÞi
memoryIsLocked = True
If OpenClipboard(0&) = PtrNull Then
Err.Raise vbObjectError + 1, "vbaClipboard", "Unable To open Clipboard. Perhaps some other application Is using it."
Else
clipBoardIsOpen = True
End If
' Ðiònh daòng ðýõòc yêu câÌu có phaÒi là môòt trong nhýÞng ðiònh daòng ðýõòc tích hõòp sãÞn trong Windows không?
Dim i As Integer
If thisClipboardFormatNumber = 0 Then
' Không. Ðãng kyì ðiònh daòng
On Error Resume Next
thisClipboardFormatNumber = CLng(RegisterClipboardFormat("XML Spreadsheet")) 'Note: Adding this to support 64Bit
If Err.Number <> 0 Then
Err.Raise vbObjectError + 1, "vbaClipboard", "Unable To register clipboard format: " & "XML Spreadsheet" & _
". Error message: " & Err.description
End If
On Error GoTo ErrorHandler
If thisClipboardFormatNumber = 0 Then Err.Raise vbObjectError + 1, "vbaClipboard", "Unable To register clipboard format: " & "XML Spreadsheet"
End If
' Làm trôìng clipboard
If EmptyClipboard() = PtrNull Then Err.Raise vbObjectError + 1, "vbaClipboard", "Unable To Empty the clipboard."
If SetClipboardData(thisClipboardFormatNumber, hMemory) = PtrNull Then Err.Raise vbObjectError + 1, "vbaClipboard", "Unable To Set the clipboard data."
CloseClipboard
GlobalFree hMemory
Exit Sub
ErrorHandler:
Dim description As String
description = Err.description
On Error Resume Next
If memoryIsLocked Then GlobalUnlock hMemory
If memoryIsAllocated Then GlobalFree hMemory
If clipBoardIsOpen Then CloseClipboard
On Error GoTo 0
Err.Raise vbObjectError + 1, "vbaClipboard", description
End Sub