Solved

Runtime error 70 permission denied excel 2010

Posted on 2011-09-06
2
1,212 Views
Last Modified: 2012-05-12
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").Cells(40, 17)
Date = Worksheets("CIPData").Cells(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").Cells(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").Cells(counter2, 19) = 12 Then GoTo 30 Else GoTo 20

30
counter2 = counter2 + 1
If Worksheets("CIPData").Cells(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").Range("A" & Y & "", Range("W1").End(xlDown)).ClearContents


Charts.Add
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Sheets("CIPData").Range("E" & X & ":" & "E" & Y), PlotBy _
        :=xlColumns
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""CIT14003"""
    ActiveChart.SeriesCollection(1).XValues = "=CIPData!R" & X & "C2:R" & Y & "C2"
    ActiveChart.SeriesCollection(2).Values = "=CIPData!R" & X & "C11:R" & Y & "C11"
    ActiveChart.SeriesCollection(2).Name = "=""TT14005"""
        ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Values = "=CIPData!R" & X & "C13:R" & Y & "C13"
    ActiveChart.SeriesCollection(3).Name = "=""FT14002"""
        ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(4).Values = "=CIPData!R" & X & "C23:R" & Y & "C23"
    ActiveChart.SeriesCollection(4).Name = "=""PT14002"""
        ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(5).Values = "=CIPData!R" & X & "C19:R" & Y & "C19"
    ActiveChart.SeriesCollection(5).Name = "=""FUNC_CH1"""
        ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(6).Values = "=CIPData!R" & X & "C7:R" & Y & "C7"
    ActiveChart.SeriesCollection(6).Name = "=""CIT14004"""
   
   ActiveChart.PlotArea.Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    Selection.Interior.ColorIndex = xlNone
    ActiveChart.Axes(xlCategory).Select
    With ActiveChart.Axes(xlCategory)
        .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.AutoScaleFont = 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.AutoScaleFont = 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.NumberFormat = "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.LegendEntries(1).LegendKey.Select
    With Selection.Border
        .ColorIndex = 10
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 3
        .Shadow = False
    End With
    ActiveChart.Legend.LegendEntries(3).LegendKey.Select
    With Selection.Border
        .ColorIndex = 3
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 3
        .Shadow = False
    End With
    ActiveChart.Legend.LegendEntries(4).LegendKey.Select
    With Selection.Border
        .ColorIndex = 48
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 3
        .Shadow = False
    End With
    ActiveChart.Legend.LegendEntries(5).LegendKey.Select
    With Selection.Border
        .ColorIndex = 5
        .Weight = xlThin
        .LineStyle = xlDash
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 3
        .Shadow = False
    End With
    ActiveChart.Legend.LegendEntries(6).LegendKey.Select
    With Selection.Border
        .ColorIndex = 8
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 3
        .Shadow = False
    End With
    ActiveChart.SeriesCollection(6).Select
    ActiveChart.SeriesCollection(6).AxisGroup = 2
    ActiveChart.SeriesCollection(5).Select
    ActiveChart.SeriesCollection(5).AxisGroup = 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.AutoScaleFont = 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.NumberFormat = "General"
    ActiveChart.ChartArea.Select
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "" & Name & " " & Date & " Program " & Qstart & ""
    End With
    ActiveChart.PageSetup.LeftFooter = Format(Worksheets("CIPData").Range("AG1").Value)
    ActiveChart.PageSetup.RightHeader = "&P of &N"
    ActiveChart.PageSetup.RightFooter = Now
    ActiveChart.PageSetup.LeftHeader = "SV-XXXX"
   
End Sub
0
Comment
Question by:shawnyb
  • 2
2 Comments
 
LVL 1

Accepted Solution

by:
shawnyb earned 0 total points
ID: 36490724
seems to be a date time error
0
 
LVL 1

Author Closing Comment

by:shawnyb
ID: 36492241
date time entry was off.  edited to mdate and fixed issue
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now