+ Post New Thread
Page 3 of 3 FirstFirst 123
Results 31 to 38 of 38
MIS Systems Thread, Edit Excel Output in Technical; I believe we are in the same flock so you decide which it is ..........
  1. #31

    Join Date
    Mar 2008
    Location
    Shrewsbury
    Posts
    9
    Thank Post
    2
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    I believe we are in the same flock so you decide which it is .......

  2. #32
    Sivadam's Avatar
    Join Date
    Feb 2009
    Location
    Dronfield - Derbyshire
    Posts
    1,449
    Thank Post
    136
    Thanked 208 Times in 175 Posts
    Rep Power
    55
    I reckon that everybody knows already .............

  3. #33

    Join Date
    Nov 2009
    Location
    Southampton
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    I've followed these instructions to the letter to get Sims report data to export into an Excel Pivot Table and subsequently a graph, but something is going whacky as when I run my report I get an error message saying "error 1004 : Unable to get the PivotFields property of the Pivot Table Class". Any idea how I overcome this?

    Thanks

  4. #34

    Join Date
    May 2007
    Posts
    13
    Thank Post
    4
    Thanked 1 Time in 1 Post
    Rep Power
    0
    I had issues like this most of which were related to problems in my code, so:

    1) make sure where you refer to cells, you've selected the correct area, and you've referred in such a way that the area only ever uses 'active' cells (from row 1 to xl.end function or whatever it is so its looking at the last used row).
    2) the columns have headers/titles
    3) I had a problem when a-c had data in, pivot table was only looking at a-c but d also had bits of data in (but no header), so either delete unnecessary columns or make sure they have headers or something
    4) the pivot tables seem not to like having rows with empty data, which when exporting from SIMS can be a bit of a problem because you often have empty cells. One way to get around this is to include in your code a replacement value, so record a macro where you select your data area and use 'replace' (ctrl+h) leave 'find what' blank and change 'replace with' to some value (probably a letter, numbers might affect formulae).

    Hope that helps, post back if you solve/want further suggestions/help

  5. #35

    Join Date
    Nov 2009
    Location
    Southampton
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Sims Report - Excel Output Problem

    Thanks Simon....

    I've moved a step forward with the Excel output now showing part of my pivot table graph. However, one of the pivot table fields is missing and the error has now changed to
    "Error 1004 : Method 'Cells of Object'_Global' failed.

    Any idea what may be wrong now?


    Here's the Macro that I'm trying to run (MacroX at the bottom being the problem):

    ' Excel Macro to prepare Tab-separated file ReportData.txt for printing
    ' written by David Stott
    '
    ' Parameters are read from first line of report

    ' New parameters must be added to function GetParameters
    ' for Strings use readValue, for Booleans use readToken

    Dim ReportTitle As String 'Title eg My Report
    Dim RepeatCols As String 'FCols eg 1,2,3
    Dim HorizBars As String 'HBars eg 5
    Dim ShowPreview As Boolean 'Preview
    Dim Landscape As Boolean 'Landscape
    Dim DisplayCount As String 'DisplayCount
    Dim SplitCol As String 'SplitCol eg 7,8

    Dim RowCount As Integer
    Dim ColCount As Integer

    Function GetParameters()

    Range("1:1").Select
    Dim data As String
    Do While ActiveCell.value <> ""
    data = ActiveCell.value

    ReportTitle = readValue(data, "Title", ReportTitle)
    HorizBars = readValue(data, "HBars", HorizBars)
    ShowPreview = readToken(data, "Preview", ShowPreview)
    Landscape = readToken(data, "Landscape", Landscape)
    RepeatCols = readValue(data, "FCols", RepeatCols)
    SplitCol = readValue(data, "SplitCol", SplitCol)
    DisplayCount = readValue(data, "DisplayCount", DisplayCount)

    ActiveCell.Offset(0, 1).Select
    Loop

    RepeatCols = ConvertCol(RepeatCols)
    SplitCol = ConvertCol(SplitCol)

    End Function

    ' Main subroutine of Macro starts here ***********************
    Sub Auto_Open()

    On Error GoTo ErrorHandler

    Application.Visible = False

    ThePath = ThisWorkbook.Path
    Workbooks.Open FileName:=ThePath + "\ReportData.txt"

    ' Copy the workbook, and close the source file (having marked it as saved)
    Set Wbook = ActiveWorkbook
    ActiveSheet.Copy
    Wbook.Saved = True
    Wbook.Close

    Set ReportSheet = ActiveSheet

    ' Set Default parameters
    RepeatCols = ""
    SplitCol = ""
    ReportTitle = ""
    HorizBars = "5"
    ShowPreview = False
    Landscape = False

    ' Read parameters from first line of report
    GetParameters

    ' Delete first line now that its work is done
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp

    ' Calculate number of rows in report
    RowCount = Range("A1").SpecialCells(xlCellTypeLastCell).Row
    'if not split sheets then take the Column title into account
    If SplitCol = "" Then RowCount = RowCount - 1

    ColCount = Range("A1").SpecialCells(xlCellTypeLastCell).colum n

    'Rule the columns
    RuledColumns

    ' Delete top line now that its work is done
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp

    ' Page Settings
    With ReportSheet.PageSetup
    .LeftFooter = "Page &P of &N"
    .RightFooter = "&D &T"
    .CenterHeader = "&14 " + ReportTitle
    .PrintTitleRows = "$1:$1"
    If RepeatCols >= "A" Then .PrintTitleColumns = "$A:$" + RepeatCols
    If Landscape Then .Orientation = xlLandscape Else .Orientation = xlPortrait
    If DisplayCount <> "" Then .RightHeader = DisplayCount + " " + Str(RowCount - 2)
    End With

    ' Excel doesn't autofit address block properly so do a hack
    FixAddressColumn

    MacroX

    ' Set Automatic column widths
    Cells.Select
    Selection.HorizontalAlignment = xlLeft
    Selection.VerticalAlignment = xlTop
    Selection.Columns.AutoFit

    ' Embolden top line of report (column headings)
    Rows("1:1").Select
    Selection.Font.bold = True

    ' Add Grid Lines (Horizontal lines are added in "MakeSheets" if separate lists are requested)
    If RepeatCols >= "A" Then VerticalLine (RepeatCols)
    If SplitCol = "" Then HorizontalBars first:=1, cycle:=Val(HorizBars), last:=RowCount
    Range("A1").Select

    ' Split list up if required
    If SplitCol > "" Then MakeSheets col:=SplitCol


    ' Display Preview
    Application.Visible = True

    ' Mark the active workbook as saved
    ActiveWorkbook.Saved = True

    If ShowPreview Then ActiveWindow.SelectedSheets.PrintPreview
    'close this workbook
    ThisWorkbook.Close
    Exit Sub

    ' Error-handling routine
    ErrorHandler:
    Application.Visible = True
    MsgBox "Error " & Err.Number & " : " & Err.Description

    End Sub

    Function readValue(data, name, store)

    namelen = Len(name) + 1
    If UCase(Left(data, namelen)) = UCase(name) + "=" Then store = Right(data, Len(data) - namelen)
    readValue = store

    End Function

    Function readToken(data, name, store)

    If UCase(data) = UCase(name) Then store = True
    readToken = store

    End Function

    Sub HorizontalBars(first, cycle, last)
    x = first
    Do While x < last
    HorizontalLine (x)
    If cycle <= 0 Then x = last Else x = x + cycle
    Loop
    End Sub

    Sub VerticalLine(Idx)
    Columns(Idx + ":" + Idx).Select
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .weight = xlMedium
    End With
    End Sub

    Function RuledColumns()

    Range("1:1").Select
    Dim data As String
    Do While ActiveCell.value <> ""
    data = ActiveCell.value
    If Left(data, 1) = "*" Then

    ActiveCell.EntireColumn.Select
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .weight = xlThin
    End With
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .weight = xlThin
    End With
    End If

    ActiveCell.Offset(0, 1).Select
    Loop

    End Function
    Function FixAddressColumn()

    Range("1:1").Select
    Do While ActiveCell.value <> ""
    If Left(ActiveCell.value, 7) = "Address" Then
    ActiveCell.EntireColumn.ColumnWidth = 50
    End If
    ActiveCell.Offset(0, 1).Select
    Loop

    End Function

    Sub HorizontalLine(Idx)
    i = Trim(Str(Idx))
    Rows(i + ":" + i).Select
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .weight = xlThin
    End With

    End Sub

    Function ConvertCol(Idx)
    If Idx = "" Then ConvertCol = "" Else ConvertCol = Chr(Idx + 64)
    End Function

    Sub MakeSheets(col)

    Range(col + "1:" + col + "1").Select
    caption = ActiveCell.value
    ActiveCell.Offset(1, 0).Select
    Dim data As String
    frow = 0
    value = ""
    For r = 2 To RowCount
    data = ActiveCell.value
    If data <> value And frow <> 0 Then
    If value <> "" Then
    MakeSheet first:=frow, last:=r - 1, caption:=caption, descr:=value, col:=col
    End If
    frow = 0
    End If
    If frow = 0 Then
    frow = r
    value = data
    End If
    ActiveCell.Offset(1, 0).Select
    Next r

    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    Sheets.Select

    End Sub

    Sub MakeSheet(first, last, caption, descr, col)

    Sheets("ReportData").Copy After:=Sheets(Sheets.Count)
    SetSheetName (descr)
    Chop first:=last + 1, last:=RowCount
    Chop first:=2, last:=first - 1
    With ActiveSheet.PageSetup
    .CenterHeader = .CenterHeader + Chr(13) + caption + ": " + descr
    If DisplayCount <> "" Then .RightHeader = DisplayCount + " " + Str(last - first + 1)
    End With

    HorizontalBars first:=1, cycle:=Val(HorizBars), last:=last - first + 3

    Range(col + ":" + col).Select
    Selection.Delete Shift:=xlRight

    fstr = Trim(Str(last - first + 3))
    lstr = Trim(Str(RowCount))
    Rows(fstr + ":" + lstr).Select
    Selection.Style = "Normal"

    Columns(col + ":" + ConvertCol(ColCount)).Select
    Selection.Style = "Normal"

    Range("A1").Select
    Sheets("ReportData").Select

    End Sub

    Sub SetSheetName(value)
    For x = 1 To Len(value)
    If InStr("[]?/\'", Mid(value, x, 1)) > 0 Then value = Left(value, x - 1) + "." + Mid(value, x + 1)
    Next x
    On Error Resume Next
    ActiveSheet.name = value
    End Sub

    Sub Chop(first, last)
    If last >= first Then
    fstr = Trim(Str(first))
    lstr = Trim(Str(last))
    Rows(fstr + ":" + lstr).Delete
    End If

    End Sub

    Sub MacroX()
    '
    ' MacroX Macro
    ' Macro recorded 30/11/2009 by .
    '

    '
    Cells.Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    "ReportData!A1:F220").CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Provision type")
    .Orientation = xlColumnField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Provision type"), "Count of Provision type", _
    xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Surname")
    .Orientation = xlRowField
    .Position = 1
    End With
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3")
    ActiveChart.Location Where:=xlLocationAsNewSheet
    End Sub

  6. #36

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    244
    Thank Post
    23
    Thanked 36 Times in 27 Posts
    Rep Power
    20
    You're calling Cells(...) without specifying which cells you want to select! Not tested, but I'd bet good money that's what's causing the error - specify the range that you want, and then hopefully it'll work.

  7. #37

    Join Date
    Jun 2012
    Location
    United Kingdom
    Posts
    14
    Thank Post
    2
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    I am trying to do a report in SIMS which show the Top 20 students who have most behaviour or achievement points in a specified period of time, any ideas?

  8. #38
    Sivadam's Avatar
    Join Date
    Feb 2009
    Location
    Dronfield - Derbyshire
    Posts
    1,449
    Thank Post
    136
    Thanked 208 Times in 175 Posts
    Rep Power
    55
    See the Report that I uploaded to File Sharing on SupportNet - number 956.
    Last edited by Sivadam; 1st June 2012 at 02:24 PM.

SHARE:
+ Post New Thread
Page 3 of 3 FirstFirst 123

Similar Threads

  1. excel spreadsheet editable online
    By Uraken in forum Web Development
    Replies: 4
    Last Post: 21st November 2007, 04:23 PM
  2. pdf file editting
    By russdev in forum Windows
    Replies: 7
    Last Post: 27th September 2005, 01:45 PM
  3. Excel frustration
    By GrumbleDook in forum Windows
    Replies: 3
    Last Post: 24th August 2005, 10:01 PM
  4. Replies: 1
    Last Post: 15th August 2005, 06:44 AM
  5. ISa 2004 Standard Edition
    By mrtechsystems in forum Windows
    Replies: 2
    Last Post: 31st July 2005, 04:33 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •