Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Runtime error 70 permission denied excel 2010

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

829 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