Excel VBA导出选中单元格区域为JPG并按指定单元格内容命名

你可以直接使用以下修改后的代码，它会自动根据你选中的6×23区域的对应位置单元格内容拼接导出文件名：

Sub ExportRangeToJPG()
    Dim oWs As Worksheet
    Dim oRng As Range
    Dim oChrtO As ChartObject
    Dim lWidth As Long, lHeight As Long
    Dim fileNamePart1 As String, fileNamePart2 As String
    Dim savePath As String, fullFileName As String

    ' 选中区域校验
    If TypeName(Selection) <> "Range" Then
        MsgBox "请先选中要导出的单元格区域", vbExclamation
        Exit Sub
    End If
    Set oRng = Selection
    ' 校验是否为6列23行的固定尺寸区域
    If oRng.Columns.Count <> 6 Or oRng.Rows.Count <> 23 Then
        MsgBox "请选中6列×23行的待导出区域", vbExclamation
        Exit Sub
    End If

    Set oWs = ActiveSheet
    ' 默认保存在当前Excel文件所在文件夹，可自行修改路径
    savePath = ThisWorkbook.Path & Application.PathSeparator

    ' 提取拼接文件名所需的两个单元格内容
    fileNamePart1 = CleanFileName(oRng.Cells(1, 5).Value) ' 选中区域第1行第5列，对应示例的F2、N27
    fileNamePart2 = CleanFileName(oRng.Cells(23, 2).Value) ' 选中区域第23行第2列，对应示例的C24、K49

    ' 拼接完整文件名，如两个单元格内容为空则默认使用Case.jpg
    If fileNamePart1 = "" And fileNamePart2 = "" Then
        fullFileName = savePath & "Case.jpg"
    Else
        fullFileName = savePath & fileNamePart1 & fileNamePart2 & ".jpg"
    End If

    ' 原有导出逻辑
    oRng.CopyPicture xlScreen, xlPicture
    lWidth = oRng.Width
    lHeight = oRng.Height

    Set oChrtO = oWs.ChartObjects.Add(Left:=0, Top:=0, Width:=lWidth, Height:=lHeight)
    oChrtO.Activate
    With oChrtO.Chart
        .Paste
        .Export Filename:=fullFileName, Filtername:="JPG"
    End With
    oChrtO.Delete

    MsgBox "导出完成，文件保存至：" & fullFileName, vbInformation
End Sub

' 辅助函数：清理文件名中的Windows系统非法字符
Function CleanFileName(str As String) As String
    Dim invalidChars As Variant
    Dim i As Integer
    invalidChars = Array("/", "\", ":", "*", "?", """", "<", ">", "|")
    For i = LBound(invalidChars) To UBound(invalidChars)
        str = Replace(str, invalidChars(i), "_")
    Next i
    CleanFileName = Trim(str)
End Function

代码说明 #

• 新增了选中区域校验逻辑，避免选错区域导出错误内容
• 文件名直接从选中区域的相对位置提取：oRng.Cells(行号, 列号)的行号、列号是相对于你选中区域的左上角计算，完全适配你给出的两个示例的文件名规则
• 内置非法字符清理逻辑，避免单元格内容包含系统禁止的文件名字符导致导出失败
• 导出路径默认和当前Excel文件同目录，你可以修改savePath变量自定义保存位置

内容的提问来源于stack exchange，提问作者jon