Solved

Runtime error 70 permission denied excel 2010

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

746 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

13 Experts available now in Live!

Get 1:1 Help Now