Solved

Runtime error 70 permission denied excel 2010

Posted on 2011-09-06
2
1,220 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

773 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