Sage 100

 View Only
Back to discussions
Expand all | Collapse all

Sage Intelligence publish to file duplicating row 1-4 values down the sheet

  • 1.  Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-07-2019 18:23
    I'm on Sage 100 Std 2018 PU5 with the SI5200 patch installed to address reporting tree issues. 
    I have about 140 worksheets, one for each reporting tree unit. 
    I'm trying to save to file with static values.
    I set up the sending instructions to save to the location/file, all works fine, except...
     
    The saved file repeats the header information in rows 1-4 all down the body of the spreadsheet, overwriting data. 
    Has anyone seen this before?
    Any solutions?
     


    ------------------------------
    Clark Walliser
    Senior Consultant
    DSD Business Systems
    San Jose CA
    Clark
    ------------------------------


  • 2.  RE: Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-08-2019 18:30
    What happens when you output to another 'static' format such as PDF?

    ------------------------------
    Moira Goggin
    Chismet Consulting Corp.
    Long Beach CA
    ------------------------------



  • 3.  RE: Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-08-2019 19:03
    The problem turned out to be that I had a filter to hide zero value rows.
    When I removed the filter, it worked as expected.

    My guess is that to convert formulas to values, they used a VBA script.
    I've seen example scripts online that use a copy/paste (as values) function for all worksheets. 

    But they depend on the source and destination sheet layouts to be the same. 
    I tried using the VBA code and it errored out also. That's what leads me to this hypothesis. 

    So now I'm working on some VBA code to automatically filter out the zero rows, then another button to unfilter everything. 
    I don't like adding VBA code that needs to be supported, but the alternative is worse.

    ------------------------------
    Clark Walliser
    Senior Consultant
    DSD Business Systems
    San Jose CA
    Clark
    ------------------------------

    Original Message


  • 4.  RE: Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-08-2019 19:32
    I have that code, let me find it and post it.

    ------------------------------
    Moira Goggin
    Chismet Consulting Corp.
    Long Beach CA
    ------------------------------

    Original Message


  • 5.  RE: Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-08-2019 19:36
    Scroll down a bit in this thread and you'll see Lou Wagners post about two macros he used to hide/unhide rows.

    https://community.90minds.com/communities/community-home/digestviewer/viewthread?MessageKey=91012c96-11d2-42de-bd0b-fccf5fafc1d3&CommunityKey=4593d843-4557-43ff-ba4d-6240681ed696&tab=digestviewer#bme390c58b-2fac-4d55-901a-3c375502df75

    ------------------------------
    Moira Goggin
    Chismet Consulting Corp.
    Long Beach CA
    ------------------------------

    Original Message


  • 6.  RE: Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-08-2019 19:49

    Thanks Moira, I'll digest that one for a while.



    ------Original Message------

    Scroll down a bit in this thread and you'll see Lou Wagners post about two macros he used to hide/unhide rows.

    https://community.90minds.com/communities/community-home/digestviewer/viewthread?MessageKey=91012c96-11d2-42de-bd0b-fccf5fafc1d3&CommunityKey=4593d843-4557-43ff-ba4d-6240681ed696&tab=digestviewer#bme390c58b-2fac-4d55-901a-3c375502df75

    ------------------------------
    Moira Goggin
    Chismet Consulting Corp.
    Long Beach CA
    ------------------------------


  • 7.  RE: Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-08-2019 19:43

    This is the VBA code I have to remove filters, which worked.

    My challenge is finding code that will apply a filter to all sheets when some of the sheets are protected.

    Everything I've tried so far errors out on the protected sheets.

     

    Sub Auto_Open()

        Dim xWs As Worksheet

        For Each Wks In ThisWorkbook.Worksheets

        On Error Resume Next

        If Wks.AutoFilterMode Then

            Wks.AutoFilterMode = False

        End If

        Next Wks

    End Sub

     



    ------Original Message------

    I have that code, let me find it and post it.

    ------------------------------
    Moira Goggin
    Chismet Consulting Corp.
    Long Beach CA
    ------------------------------


  • 8.  RE: Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-08-2019 20:04
    You need to unprotect then reprotect them - is it password protected too or just cell/row level for edits?

    ------------------------------
    Moira Goggin
    Chismet Consulting Corp.
    Long Beach CA
    ------------------------------

    Original Message


  • 9.  RE: Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-08-2019 20:06
    Haven't tried it but found it here:

    https://analysistabs.com/excel-vba/protect-unprotect-worksheets/

    ------------------------------
    Moira Goggin
    Chismet Consulting Corp.
    Long Beach CA
    ------------------------------

    Original Message


  • 10.  RE: Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-08-2019 21:05
    The sage intelligence data sheets are password protected by SI.

    Clark Walliser
    DSD Business Systems



    ------Original Message------

    Haven't tried it but found it here:

    https://analysistabs.com/excel-vba/protect-unprotect-worksheets/

    ------------------------------
    Moira Goggin
    Chismet Consulting Corp.
    Long Beach CA
    ------------------------------


  • 11.  RE: Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-09-2019 15:54
    Yes, to run the report you'd have to have access/permissions but I don't recall after the report is generated that the sheets are protected.

    ------------------------------
    Moira Goggin
    Chismet Consulting Corp.
    Long Beach CA
    ------------------------------

    Original Message


  • 12.  RE: Sage Intelligence publish to file duplicating row 1-4 values down the sheet

    Posted 03-09-2019 19:56
    Sage has given me the SI password on some of the reports when I have asked.

    ------------------------------
    [Michele] [Herzog] [CPA,CITP, CGMA]
    [Overland Park] [KS]
    [816-520-1365]
    ------------------------------

    Original Message


Related Content