Adding series from multiple workbooks

0
votes

The following code is used to add a chart at the workbook that is opened. The chart data series values are taken from Worksheets(1) of every workbook inside "InputPathName" directory using a loop. Then I apply a template graph saved at "TemplatePath". Two problems arise:

  1. Series Name will not have the value of cell B7. I tried 2 ways but did not work (marked as: ATTEMPT 1 & 2)

  2. Some of the workbooks of directory "InputPathName" have more than one worksheet. If any worksheet other than worksheets(1) is active, I have a Run-time error '1004' Method 'Range' of object '_Worksheet' failed (highlighted line: Set xRange =...). If I add ws.Activate after Set ws=.... (as commented out), the graph will be a mess and won't show the correct results. Note: If the active sheet in every workbook is worksheets(1), then the code works well.

DEVELOPING: How can I loop inside every worksheet of the workbooks without knowing the number of the worksheets?

Sub InputFromOtherWorksheets()
Dim ch As Chart
Dim ws As Worksheet
Dim wb As Workbook

Dim xRange As Range
Dim yRange As Range
Dim TemplatePath As String
Dim TemplateName As String
Dim InputPathName As String
Dim InputFileName As String

TemplatePath = "C:\Charts"
TemplateName = "templ4"
InputPathName = "C:\New folder\"

Set ch = Charts.Add2

InputFileName = Dir(InputPathName)

Do While InputFileName <> ""

    Set wb = Workbooks.Open(InputPathName & InputFileName)
    Set ws = wb.Worksheets(1)   
    'ws.Activate

    Set xRange = ws.Range("B18", Range("B18").End(xlDown))
    Set yRange = ws.Range("C18", Range("C18").End(xlDown))

    With ch.SeriesCollection.NewSeries
        .Name = ws.Range("B7") 'ATTEMPT 1
        .XValues = xRange
        .Values = yRange
    End With

    ch.SeriesCollection(1).Name = ws.Range("B7") 'ATTEMPT 2

    wb.Close SaveChanges:=False
        InputFileName = Dir()  
Loop

ch.ApplyChartTemplate TemplatePath & "\" & TemplateName
ch.ChartTitle.Text = "Specimen 1"

End Sub
2
vbaexcel

2 Answers

0
votes

Try the code below:

With ch.SeriesCollection.NewSeries
    .Name = "=" & ws.Range("B7").Address(False, False, xlA1, xlExternal)

    ' rest of your code

End With
0
votes

To loop through every sheet of a workbook, you have to use Worksheets collection. Let's say we have set Workbook object in wb variable:

Dim ws As Worksheet
' some code
For Each ws in wb.Worksheets
    ' do some operations on ws object
Next