多工作簿添加图表系列问题求助：系列名称异常与多工作表处理

Hey there, let's tackle these two issues you're facing with your chart automation code. I've put together targeted fixes and explanations below:

问题1：无法获取单元格B7的值作为系列名称 #

The most likely culprit here is that your code isn't explicitly referencing the source workbook when trying to grab cell B7—so it's probably pulling from the active workbook instead of the one you're looping through. Let's fix both your attempts:

修正ATTEMPT 1（明确指定工作簿和工作表） #

If your original code looked something like this (missing workbook reference):

' 错误示例：ATTEMPT 1
Series.Name = Range("B7").Value

Change it to explicitly target the workbook you're currently processing:

' 修正后：ATTEMPT 1
Dim wb As Workbook
Set wb = Workbooks.Open(InputPathName & fileName) ' 对应你循环打开工作簿的代码
Series.Name = wb.Worksheets(1).Range("B7").Value

修正ATTEMPT 2（正确的公式引用语法） #

If you tried using a formula reference but got the syntax wrong (like forgetting to wrap the address properly), adjust it like this:

' 错误示例：ATTEMPT 2
Series.Name = "=B7"
' 修正后：ATTEMPT 2
Series.Name = "='" & wb.Name & "'!B7"

This ensures the series name links directly to cell B7 in the first worksheet of the source workbook, even if that workbook isn't active.

问题2：含多工作表的工作簿处理逻辑异常 #

Since you mentioned issues when workbooks have more than one worksheet, let's harden the code to only interact with the first worksheet and avoid accidental references to other sheets. Here's a refined loop structure:

Dim inputFolder As String
inputFolder = InputPathName ' 你的输入目录路径
Dim fileName As String
fileName = Dir(inputFolder & "*.xlsx") ' 按需调整文件格式

Do While fileName <> ""
    Dim sourceWb As Workbook
    Set sourceWb = Workbooks.Open(inputFolder & fileName)
    
    ' 强制锁定第一个工作表，完全忽略其他工作表
    Dim sourceWs As Worksheet
    Set sourceWs = sourceWb.Worksheets(1)
    
    ' 提取数据系列值（替换为你的实际数据范围）
    Dim dataRange As Range
    Set dataRange = sourceWs.Range("C2:C15") ' 示例数据范围
    
    ' 在目标工作簿添加图表并应用模板
    Dim targetChart As ChartObject
    Set targetChart = ThisWorkbook.Worksheets("目标工作表名").ChartObjects.Add(Left:=100, Width:=300, Top:=100, Height:=200)
    targetChart.Chart.ApplyChartTemplate (TemplatePath) ' 你的图表模板路径
    
    ' 设置系列数据和名称（结合问题1的修正）
    With targetChart.Chart.SeriesCollection(1)
        .Values = dataRange
        .Name = sourceWs.Range("B7").Value ' 或者用公式引用的方式
    End With
    
    sourceWb.Close SaveChanges:=False ' 关闭源工作簿，不保存修改
    fileName = Dir() ' 遍历下一个文件
Loop

Key improvements here:

• We explicitly declare sourceWs as sourceWb.Worksheets(1) so all data extraction is tied strictly to the first sheet.
• We avoid using ActiveSheet or unqualified range references, which are common sources of bugs when dealing with multiple workbooks/sheets.

内容的提问来源于stack exchange，提问作者M.J