shawnyb
asked on
Runtime error 70 permission denied excel 2010
Works for xp office 2007 but not for windows 7 office 2010.
Here is the code below. Any help would be great. I have full control of the file and local admin on the machine
Sub CIP_Trend_Graphing()
' Software Platform Version: MS XP SP3 MS EXCEL 2007
' Name of the Macro: CIP_Excel_Trend_Reporter Rev 000
' Created by: Mark Minick
' Purpose: VBA Macro for graphing of CIP system batch report
' Issued by: XXXXX
' Effective Date: ddmmmyy
' DCR No: DCR-XX-XXX
'
' Keyboard Shortcut: Ctrl+g
'
Qstart = Worksheets("CIPData").Cell s(40, 17)
Date = Worksheets("CIPData").Cell s(2, 1)
If Qstart = 1 Then Name = "CS 2 A"
If Qstart = 2 Then Name = "CS 2 B"
If Qstart = 3 Then Name = "Glasswash Stn"
If Qstart = 4 Then Name = "250L Buffer Prep 2"
If Qstart = 5 Then Name = "600L Buffer Prep 2"
If Qstart = 6 Then Name = "CS 2 SIP"
If Qstart = 7 Then Name = "Glasswash Stn SIP"
If Qstart = 8 Then Name = "250L Buffer Prep 2 SIP"
If Qstart = 9 Then Name = "600L Buffer Prep 2 SIP"
If Qstart = 10 Then Name = "WFI Tank SIP"
If Qstart = 11 Then Name = "CS 1 A"
If Qstart = 12 Then Name = "CS 1 B"
If Qstart = 13 Then Name = "100L UF/DF"
If Qstart = 14 Then Name = "500L UF/DF"
If Qstart = 15 Then Name = "100L Ferm"
If Qstart = 16 Then Name = "500L Ferm"
If Qstart = 17 Then Name = "P6"
If Qstart = 18 Then Name = "P12"
If Qstart = 19 Then Name = "500L Lysis"
If Qstart = 20 Then Name = "250L Buffer Prep 1"
If Qstart = 21 Then Name = "400L Buffer Prep 1"
If Qstart = 22 Then Name = "250L Media Prep"
If Qstart = 23 Then Name = "CS 1 SIP"
If Qstart = 24 Then Name = "250L Buffer Prep 1 SIP"
If Qstart = 25 Then Name = "400L Buffer Prep 1 SIP"
If Qstart = 26 Then Name = "250L Media Prep SIP"
If Qstart = 27 Then Name = "WFI Tank Drain and Rinse"
If Qstart = 28 Then Name = "100L Lysis"
Dim counter1 As Integer
' counter1 is our starting number for the graph
For counter1 = 2 To 40
If Worksheets("CIPData").Cell s(counter1 , 17) = Qstart Then GoTo 10
Next counter1
10
Dim counter2 As Integer
' counter2 is our end point for our graph
counter2 = 1
20
counter2 = counter2 + 1
If Worksheets("CIPData").Cell s(counter2 , 19) = 12 Then GoTo 30 Else GoTo 20
30
counter2 = counter2 + 1
If Worksheets("CIPData").Cell s(counter2 , 19) = 12 Then GoTo 30 Else GoTo 40
40
counter2 = counter2 - 1
counter2 = counter2 + 10
X = counter1
Y = counter2
' Truncates unneccesary data
Worksheets("CIPData").Rang e("A" & Y & "", Range("W1").End(xlDown)).C learConten ts
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("CIPData"). Range("E" & X & ":" & "E" & Y), PlotBy _
:=xlColumns
ActiveChart.SeriesCollecti on.NewSeri es
ActiveChart.SeriesCollecti on(1).Name = "=""CIT14003"""
ActiveChart.SeriesCollecti on(1).XVal ues = "=CIPData!R" & X & "C2:R" & Y & "C2"
ActiveChart.SeriesCollecti on(2).Valu es = "=CIPData!R" & X & "C11:R" & Y & "C11"
ActiveChart.SeriesCollecti on(2).Name = "=""TT14005"""
ActiveChart.SeriesCollecti on.NewSeri es
ActiveChart.SeriesCollecti on(3).Valu es = "=CIPData!R" & X & "C13:R" & Y & "C13"
ActiveChart.SeriesCollecti on(3).Name = "=""FT14002"""
ActiveChart.SeriesCollecti on.NewSeri es
ActiveChart.SeriesCollecti on(4).Valu es = "=CIPData!R" & X & "C23:R" & Y & "C23"
ActiveChart.SeriesCollecti on(4).Name = "=""PT14002"""
ActiveChart.SeriesCollecti on.NewSeri es
ActiveChart.SeriesCollecti on(5).Valu es = "=CIPData!R" & X & "C19:R" & Y & "C19"
ActiveChart.SeriesCollecti on(5).Name = "=""FUNC_CH1"""
ActiveChart.SeriesCollecti on.NewSeri es
ActiveChart.SeriesCollecti on(6).Valu es = "=CIPData!R" & X & "C7:R" & Y & "C7"
ActiveChart.SeriesCollecti on(6).Name = "=""CIT14004"""
ActiveChart.PlotArea.Selec t
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Interior.ColorIn dex = xlNone
ActiveChart.Axes(xlCategor y).Select
With ActiveChart.Axes(xlCategor y)
.CrossesAt = 1
.TickLabelSpacing = 90
.TickMarkSpacing = 30
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
Selection.TickLabels.AutoS caleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue). Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 85
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Selection.TickLabels.AutoS caleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.TickLabels.Numbe rFormat = "General"
ActiveChart.Legend.Select
Selection.Position = xlTop
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Legend.LegendE ntries(1). LegendKey. Select
With Selection.Border
.ColorIndex = 10
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorInde x = xlNone
.MarkerForegroundColorInde x = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Legend.LegendE ntries(3). LegendKey. Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorInde x = xlNone
.MarkerForegroundColorInde x = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Legend.LegendE ntries(4). LegendKey. Select
With Selection.Border
.ColorIndex = 48
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorInde x = xlNone
.MarkerForegroundColorInde x = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Legend.LegendE ntries(5). LegendKey. Select
With Selection.Border
.ColorIndex = 5
.Weight = xlThin
.LineStyle = xlDash
End With
With Selection
.MarkerBackgroundColorInde x = xlNone
.MarkerForegroundColorInde x = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Legend.LegendE ntries(6). LegendKey. Select
With Selection.Border
.ColorIndex = 8
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorInde x = xlNone
.MarkerForegroundColorInde x = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollecti on(6).Sele ct
ActiveChart.SeriesCollecti on(6).Axis Group = 2
ActiveChart.SeriesCollecti on(5).Sele ct
ActiveChart.SeriesCollecti on(5).Axis Group = 2
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = 0
.MaximumScale = 12
.MajorUnit = 1
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Selection.TickLabels.AutoS caleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.TickLabels.Numbe rFormat = "General"
ActiveChart.ChartArea.Sele ct
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Tex t = "" & Name & " " & Date & " Program " & Qstart & ""
End With
ActiveChart.PageSetup.Left Footer = Format(Worksheets("CIPData ").Range(" AG1").Valu e)
ActiveChart.PageSetup.Righ tHeader = "&P of &N"
ActiveChart.PageSetup.Righ tFooter = Now
ActiveChart.PageSetup.Left Header = "SV-XXXX"
End Sub
Here is the code below. Any help would be great. I have full control of the file and local admin on the machine
Sub CIP_Trend_Graphing()
' Software Platform Version: MS XP SP3 MS EXCEL 2007
' Name of the Macro: CIP_Excel_Trend_Reporter Rev 000
' Created by: Mark Minick
' Purpose: VBA Macro for graphing of CIP system batch report
' Issued by: XXXXX
' Effective Date: ddmmmyy
' DCR No: DCR-XX-XXX
'
' Keyboard Shortcut: Ctrl+g
'
Qstart = Worksheets("CIPData").Cell
Date = Worksheets("CIPData").Cell
If Qstart = 1 Then Name = "CS 2 A"
If Qstart = 2 Then Name = "CS 2 B"
If Qstart = 3 Then Name = "Glasswash Stn"
If Qstart = 4 Then Name = "250L Buffer Prep 2"
If Qstart = 5 Then Name = "600L Buffer Prep 2"
If Qstart = 6 Then Name = "CS 2 SIP"
If Qstart = 7 Then Name = "Glasswash Stn SIP"
If Qstart = 8 Then Name = "250L Buffer Prep 2 SIP"
If Qstart = 9 Then Name = "600L Buffer Prep 2 SIP"
If Qstart = 10 Then Name = "WFI Tank SIP"
If Qstart = 11 Then Name = "CS 1 A"
If Qstart = 12 Then Name = "CS 1 B"
If Qstart = 13 Then Name = "100L UF/DF"
If Qstart = 14 Then Name = "500L UF/DF"
If Qstart = 15 Then Name = "100L Ferm"
If Qstart = 16 Then Name = "500L Ferm"
If Qstart = 17 Then Name = "P6"
If Qstart = 18 Then Name = "P12"
If Qstart = 19 Then Name = "500L Lysis"
If Qstart = 20 Then Name = "250L Buffer Prep 1"
If Qstart = 21 Then Name = "400L Buffer Prep 1"
If Qstart = 22 Then Name = "250L Media Prep"
If Qstart = 23 Then Name = "CS 1 SIP"
If Qstart = 24 Then Name = "250L Buffer Prep 1 SIP"
If Qstart = 25 Then Name = "400L Buffer Prep 1 SIP"
If Qstart = 26 Then Name = "250L Media Prep SIP"
If Qstart = 27 Then Name = "WFI Tank Drain and Rinse"
If Qstart = 28 Then Name = "100L Lysis"
Dim counter1 As Integer
' counter1 is our starting number for the graph
For counter1 = 2 To 40
If Worksheets("CIPData").Cell
Next counter1
10
Dim counter2 As Integer
' counter2 is our end point for our graph
counter2 = 1
20
counter2 = counter2 + 1
If Worksheets("CIPData").Cell
30
counter2 = counter2 + 1
If Worksheets("CIPData").Cell
40
counter2 = counter2 - 1
counter2 = counter2 + 10
X = counter1
Y = counter2
' Truncates unneccesary data
Worksheets("CIPData").Rang
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("CIPData").
:=xlColumns
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.PlotArea.Selec
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Interior.ColorIn
ActiveChart.Axes(xlCategor
With ActiveChart.Axes(xlCategor
.CrossesAt = 1
.TickLabelSpacing = 90
.TickMarkSpacing = 30
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
Selection.TickLabels.AutoS
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 85
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Selection.TickLabels.AutoS
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.TickLabels.Numbe
ActiveChart.Legend.Select
Selection.Position = xlTop
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Legend.LegendE
With Selection.Border
.ColorIndex = 10
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorInde
.MarkerForegroundColorInde
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Legend.LegendE
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorInde
.MarkerForegroundColorInde
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Legend.LegendE
With Selection.Border
.ColorIndex = 48
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorInde
.MarkerForegroundColorInde
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Legend.LegendE
With Selection.Border
.ColorIndex = 5
.Weight = xlThin
.LineStyle = xlDash
End With
With Selection
.MarkerBackgroundColorInde
.MarkerForegroundColorInde
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Legend.LegendE
With Selection.Border
.ColorIndex = 8
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorInde
.MarkerForegroundColorInde
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.SeriesCollecti
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = 0
.MaximumScale = 12
.MajorUnit = 1
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Selection.TickLabels.AutoS
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.TickLabels.Numbe
ActiveChart.ChartArea.Sele
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Tex
End With
ActiveChart.PageSetup.Left
ActiveChart.PageSetup.Righ
ActiveChart.PageSetup.Righ
ActiveChart.PageSetup.Left
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER