Hi everyone,

My macro will run perfect the first time I run it. If I run it again,
without closing the excel file that was created by my first macro, I get an
error message. If I close the excel file and then run the macro, it again
works fine. Why is this and how can I fix it?

Details:
The error message I get is "Method SetSourceData of...failed". The macro
pulls data from a ms project file and places it into excel. I then use this
data to create charts. The error occurs when I create my first chart.

Code:

Dim CWeek As Range, CBCWP As Range, CBCWS As Range......(etc.)

Dim co2SD As Variant, co3SD As Variant, co4SD As Variant, co5SD As Variant

Dim co2 As ChartObject, co3 As ChartObject, co4 As ChartObject, co5 As
ChartObject


'Set up the Chart Ranges based on the dates selected to run the report
' This data is on the 3rd chart

Set CWeek = ActiveSheet.Range(Cells((20 + MonthVar), 1), Cells((21 +
MonthVar + WeekVar), 1))
Set CBCWP = ActiveSheet.Range(Cells((20 + MonthVar), 3), Cells((21 +
MonthVar + WeekVar), 3))
Set CBCWS = ActiveSheet.Range(Cells((20 + MonthVar), 2), Cells((21 +
MonthVar + WeekVar), 2))

'Set Unions for each chart range for source data

Set co2SD = Union(CWeek, CCPI, CCPIUCL, CCPIAvg, CCPILCL, CCPIUSL,
CCPITarget, CCPILSL)
Set co3SD = Union(CWeek, CSPI, CSPIUCL, CSPIAvg, CSPILCL, CSPIUSL,
CSPITarget, CSPILSL)........

' Go to 4th worksheet and display charts

xlBook.Worksheets(4).Select
xlBook.Worksheets(4).Name = "Report Graphs"

' Create 1st Chart showing the Cost Performance Index

Set co2 = xlBook.Worksheets(4).ChartObjects.Add(5, 100, 700, 380)
co2.Chart.ChartType = xlLineMarkers

' ERROR OCCURS HE
co2.Chart.SetSourceData Source:=co2SD, PlotBy:=xlColumns

co2.Chart.Location Whe=xlLocationAsObject, Name:="Report Graphs"
With co2.Chart
.HasTitle = True
.ChartTitle.Characters.Text = "CPI Graph"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Index"
End With

I'm not an MVP-level expert by any means, but since no one else has replied
yet, I'll give you what I would do if I were hunting down something like
this.

Given the nature of the error, it seems like your data source, "co2SD", is
empty of data, or at least doesn't have what the Method is looking for.
Since it works the first time but not the second, my thought is that
something is changing in at least one of the ranges you Union, and renders
that object unusable. Also, you don't show the creation of all the ranges
in your Union statement - either you have cut them out of your post to save
space, or they are created in another procedure that is not shown here. You
also don't show what you do with them when you're done with them.

(BTW, if the result of Union is a Range object, and you Union Ranges, why
Dim everything as Variant rather than as Range?)

Here's how I would try to track it. Before the Union statement, set
Debug.Print statements for something about each of those ranges - maybe
something like
Debug.Print Range("CWeek")Rows.Count
Debug.Print Range("CWeek").Columns.Count
etc. Then do the same for co2SD after the Union. Step through it either
using F8, or F5 with break points, and watch BOTH the Immediate and the
Locals windows. The Locals window will tell you if the variable is getting
set at all, and give you useful information about it. The Immediate window
will show your Debug.Print results to tell you if your range is useful to
your Union statement.

Then step through it again and see what is different. I would suspect one
of the ranges in the Union is not getting set properly the second time
because your macro affects it and the method used to create it is not
rerunning without restarting Excel.

HTH
Ed

"Please Help" <Please wrote in message
...
Hi everyone,

My macro will run perfect the first time I run it. If I run it again,
without closing the excel file that was created by my first macro, I get
an
error message. If I close the excel file and then run the macro, it again
works fine. Why is this and how can I fix it?

Details:
The error message I get is "Method SetSourceData of...failed". The macro
pulls data from a ms project file and places it into excel. I then use
this
data to create charts. The error occurs when I create my first chart.

Code:

Dim CWeek As Range, CBCWP As Range, CBCWS As Range......(etc.)

Dim co2SD As Variant, co3SD As Variant, co4SD As Variant, co5SD As Variant

Dim co2 As ChartObject, co3 As ChartObject, co4 As ChartObject, co5 As
ChartObject


'Set up the Chart Ranges based on the dates selected to run the report
' This data is on the 3rd chart

Set CWeek = ActiveSheet.Range(Cells((20 + MonthVar), 1), Cells((21
+
MonthVar + WeekVar), 1))
Set CBCWP = ActiveSheet.Range(Cells((20 + MonthVar), 3), Cells((21
+
MonthVar + WeekVar), 3))
Set CBCWS = ActiveSheet.Range(Cells((20 + MonthVar), 2), Cells((21
+
MonthVar + WeekVar), 2))

'Set Unions for each chart range for source data

Set co2SD = Union(CWeek, CCPI, CCPIUCL, CCPIAvg, CCPILCL, CCPIUSL,
CCPITarget, CCPILSL)
Set co3SD = Union(CWeek, CSPI, CSPIUCL, CSPIAvg, CSPILCL, CSPIUSL,
CSPITarget, CSPILSL)........

' Go to 4th worksheet and display charts

xlBook.Worksheets(4).Select
xlBook.Worksheets(4).Name = "Report Graphs"

' Create 1st Chart showing the Cost Performance Index

Set co2 = xlBook.Worksheets(4).ChartObjects.Add(5, 100, 700, 380)
co2.Chart.ChartType = xlLineMarkers

' ERROR OCCURS HE
co2.Chart.SetSourceData Source:=co2SD, PlotBy:=xlColumns

co2.Chart.Location Whe=xlLocationAsObject, Name:="Report Graphs"
With co2.Chart
.HasTitle = True
.ChartTitle.Characters.Text = "CPI Graph"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Index"
End With

Ignore the remark that I changed the variables from global to local, the
macro crashed before I had saved the change. Also, the code is too big to
show the whole thing but i've attached the first part of it:



Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlR As Excel.Range

Sub CAM_Macro()

Dim ReportStartDate As Date
Dim ReportEndDate As Date
Dim NumberOfWeeks As Variant
Dim NumberOfMonths As Variant

Check_If_Project_Open

Check_If_Task_Selected

Get_Tasks

Call Get_Report_Dates(ReportStartDate, ReportEndDate)

Call Get_TimePhased_Values(NumberOfWeeks, NumberOfMonths, ReportStartDate,
ReportEndDate)

Open_Excel

Call Display_Selected_Data(ByVal ReportEndDate)

Call Display_Current_Period_Data(ByVal ReportStartDate, ByVal ReportEndDate,
ByVal NumberOfMonths)

Call Display_Summary_Setup(ByVal ReportStartDate, ByVal ReportEndDate)

Call Display_Monthly_Summary(ByVal NumberOfMonths, ByVal ReportStartDate,
ByVal ReportEndDate)

Call Display_Weekly_Summary(ByVal NumberOfWeeks, ByVal ReportStartDate,
ByVal ReportEndDate)

Call Display_Charts(ByVal NumberOfMonths, ByVal NumberOfWeeks, ByVal
ReportEndDate)

Call Display_Overbudget_Tasks(ByVal ReportEndDate)

Call Display_PastDue_Tasks(ByVal ReportEndDate)

End_Report

Set xlApp = Nothing
Set xlBook = Nothing
Set xlR = Nothing

End Sub
Private Sub Check_If_Project_Open()

'Procedure checks if a project file is open

On Error GoTo NoFileOpen

Dim strName As String

'If there is no active project, this will throw an error and go to the
error handler.

strName = ActiveProject.Name

Exit Sub

NoFileOpen:

MsgBox "There is no project open! Open a project and rerun the macro.",
vbCritical + R_TO_L, Title:=Application.Name

End 'End the macro

End Sub

Private Sub Check_If_Task_Selected()

'Procedure checks if a project file is open

On Error GoTo NoTaskSelected

If ActiveSelection.Tasks.Count = 1 Then

Exit Sub

ElseIf ActiveSelection.Tasks.Count 1 Then

MsgBox "You have selected more than 1 task. Select a single task and
run the macro again." _
, vbCritical + R_TO_L, Title:=Application.Name

End ' End the macro
End If

NoTaskSelected:

MsgBox "You Have Not Selected A Task!", vbCritical + R_TO_L,
Title:=Application.Name

End 'End the macro

End Sub

Private Sub Get_Tasks()
Dim N As Integer
Dim BeginningRowNumber As Integer
Dim EndRowNumber As Integer
Dim MasterTask As Task
Dim PlacementTask As Task
Dim TName As String
Dim Counter As Integer

Set MasterTask = ActiveSelection.Tasks.Item(1)
BeginningRowNumber = MasterTask.ID
Set PlacementTask = MasterTask

Do While PlacementTask.OutlineChildren.Count 0

N = PlacementTask.OutlineChildren.Count

Set PlacementTask = PlacementTask.OutlineChildren.Item(N)
TName = PlacementTask.Name

Loop

EndRowNumber = PlacementTask.ID
Counter = 1

Do While ActiveSelection.Tasks.Item(1).ID < EndRowNumber

SelectCellDown 1
Counter = Counter + 1

If ActiveSelection = 0 Then

Exit Do

End If

Loop

If ActiveSelection = 0 Then
SelectCellUp
Counter = Counter - 1
End If

If ActiveSelection.Tasks.Item(1).ID EndRowNumber Then
SelectCellUp
Counter = Counter - 1
End If
If Counter = 1 Then
Exit Sub
End If

SelectCellUp (Counter - 1), True


End Sub

Private Sub Get_Report_Dates(StartDate As Date, AsOfDate As Date)

Dim TaskStartDate As Date
Dim TaskEndDate As Date

On Error GoTo DateError

TaskStartDate = ActiveSelection.Tasks.Item(1).Start
TaskEndDate = ActiveSelection.Tasks.Item(1).Finish

OptionsCalculation Automatic:=True

StartDate = InputBox("Enter the reporting Start Date. The date should be in
the following format mm/dd/yy.", _
"Report Start Date", Format$(TaskStartDate, "Short Date"))

If Round(StartDate) < Round(TaskStartDate) Then
MsgBox "Enter a valid date greater than the Project Start Date",
vbCritical
End
End If


AsOfDate = InputBox("Enter the reporting Stop Date.", _
"Report Stop Date", Format$(TaskEndDate, "Short Date")) & " 11:59 PM"

If Round(AsOfDate) < Round(StartDate) Then
MsgBox "The reporting Stop Date must be after the reporting Start
Date.", vbCritical
End
End If

Exit Sub

DateError:

MsgBox "Invalid Date!", vbCritical
End ' End the macro

End Sub

Private Sub Get_TimePhased_Values(WeekVar As Variant, MonthVar As Variant,
StartDate As Date, AsOfDate As Date)

MonthVar = Round((((Year(AsOfDate) - Year(StartDate)) * 12) +
(Month(AsOfDate) - Month(StartDate))), 0)

WeekVar = Round((((Year(AsOfDate) - Year(StartDate)) * 52) +
(Format(AsOfDate, "ww") - Format(StartDate, "ww"))), 0)

End Sub

Private Sub Open_Excel()

If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application") 'Start new instance
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If
xlApp.Visible = True
Else
Set xlR = Nothing
Set xlApp = Nothing
Set xlBook = Nothing
Set xlApp = CreateObject("Excel.Application") ' Start New Instance
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If
xlApp.Visible = True
End If
Application.ActivateMicrosoftApp pjMicrosoftExcel

End Sub

Private Sub Display_Selected_Data(ByVal AsOfDate As Date)

Dim Proj As Project
Dim T As Task
Dim ProjectName As String

Set xlBook = xlApp.Workbooks.Add
xlApp.Calculation = xlCalculationManual ' Set Manual Calculation
xlBook.Worksheets.Add Count:=4

xlBook.Worksheets(1).Name = "Project Summary" ' Name the first worksheet

'Go to first worksheet and enter all tasks
xlBook.Worksheets(1).Select


........

Ed,

A co-worker has solved my issues. I wasn't being clear when defining the
ranges (CWeek, CCPI, etc..), for each statement I had to specify the workbook
and sheet, rather than using the activesheet method. Thanks again for all of
your help! It was very cool of you.

Curtis

Curtis, I'm glad your problem is solved! (Maybe we ought to get your
co-worker here on the NG, so he can solve *my* problems, too!! <G )

I've had others stick with me and help me work through code issues - it's
how I've learned most of what I know about VBA. That, and helping others.

Take care.
Ed

"Please Help" wrote in message
...
Ed,

A co-worker has solved my issues. I wasn't being clear when defining the
ranges (CWeek, CCPI, etc..), for each statement I had to specify the
workbook
and sheet, rather than using the activesheet method. Thanks again for all
of
your help! It was very cool of you.

Curtis