Save an userform as an image in EXCEL

最新推荐文章于 2025-05-14 11:10:11 发布

原创最新推荐文章于 2025-05-14 11:10:11 发布 · 4.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#image #function #integer #menu #user #excel

VBA 同时被 3 个专栏收录

82 篇文章

订阅专栏

图形界面

38 篇文章

订阅专栏

其他技巧

32 篇文章

订阅专栏

本文提供两种方法将Excel用户界面截图并保存为图片文件。方法一通过API模拟键盘操作捕获屏幕并保存；方法二则是在双击用户表单时触发截图，并将其粘贴到新的工作簿中，最终保存为HTML格式。

When click a commandbutton in an Excel userform,save the entire userform as an image file in harddisk.

Method 1

Private Declare Sub Keybd_Event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Declare Function MapVirtualKey Lib "user32" Alias "MapVirtualKeyA" (ByVal wCode As Long, ByVal wMapType As Long) As Long
Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" (PicDesc As PicBmp, RefIID As Guid, ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long
Private Const VK_MENU = &H12
Private Const VK_SNAPSHOT = &H2C
Private Const KEYEVENTF_KEYUP = &H2
Private Const CF_BITMAP = 2
Private Type PicBmp
    Size As Long
    Type As Long
    hBmp As Long
    hPal As Long
    Reserved As Long
End Type

Private Type Guid
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(0 To 7) As Byte
End Type

Private Sub CommandButton1_Click()
    Dim Altscan As Double, hwnd As Long, Pic As PicBmp, IPic As IPicture, IID_IDispatch As Guid
    DoEvents
    Altscan = MapVirtualKey(VK_MENU, 0) 'Alt+PrintScrn
    Keybd_Event VK_MENU, Altscan, 0, 0       'press Alt
    Keybd_Event VK_SNAPSHOT, 0, 0, 0   'press PrintScrn
    DoEvents
    Keybd_Event VK_MENU, Altscan, KEYEVENTF_KEYUP, 0 'release it
    OpenClipboard 0 'OpenClipboard
    With IID_IDispatch
        .Data1 = &H20400
        .Data4(0) = &HC0
        .Data4(7) = &H46
    End With

    With Pic
        .Size = Len(Pic)
        .Type = 1
        .hBmp = GetClipboardData(CF_BITMAP)
    End With

    OleCreatePictureIndirect Pic, IID_IDispatch, 1, IPic
    stdole.SavePicture IPic, "c:/userform.bmp"
    CloseClipboard
    MsgBox "ok"
End Sub

Method 2

Another method is from Emily's blog:

http://cat14051.mysinablog.com/index.php?op=ViewArticle&articleId=72135

The following code would save an userform as an image when you double click on the userform. With API, this code pastes an image of the form into a worksheet of the new workbook, then save it as a HTML file. When the Excel workbook is saved as a html file, all image files will be placed in the different folder.

' UserForm

Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _

ByVal bScan As Byte, _

ByVal dwFlags As Long, _

ByVal dwExtraInfo As Long)

Private Const VK_LMENU = &HA4

Private Const VK_SNAPSHOT = &H2C

Private Const VK_CONTROL = &H11

Private Const VK_V = &H56

Private Const VK_0x79 = &H79

Private Const KEYEVENTF_EXTENDEDKEY = &H1

Private Const KEYEVENTF_KEYUP = &H2