• JCont20...

    I'm impressed with your Excel sheet...

    I've recently done a report for MDS supports...I attacked the problem differently...I create a report with a comma as a delimeter for the pieces of info. and then imported it into excel...looks nothing as good as yours...

    Having a bash at excel thing has been at the back of my mind for a while...
    I have a question for you...is the code very complicated to achieve the formating ?

    Cheers
    Neil
  • Thank you for your sharing, although i am not doing such job right now,but it is a good oppoturnity to learn.
  • Could you post your .txt file on this forum? (a typical sample should be sufficient).
  • stair_name,platform_square_metres,handrail_metres
    /stair1,10,8
    /stair2,20,16
  • Guys, I think you're chasing rabbits with this. For our reports we do the following:

    1. Dump the data out of PMDS with whatever PDMS generated data is needed to work in Excel (such as color based on PDMS criteria).

    2. Open Excel and Turn on Record Macro.

    3. Import the data (we use semicolons but whatever you want will work).

    4. Format your Data.

    5. Turn off Macro Recording.

    6. Open up Visual Basic Editor

    7. Add a Module to your Worksheet and add a Sub named something like ImportCVS:
    Public Sub ImportCSV(bCode As Boolean)

    8. Copy and modify as needed the Recorded Macro code into your new Sub.

    9. Add a new Sub in you current Workbook named as follows:
    Private Sub Workbook_Open()
       If (ThisWorkbook.Name <> "PipeMTO.xls") Then
           ImportCSV True
       End If
    End Sub


    10. Save the Workbook as a new Excel File with an appropriate name (like "PipeMTO.xls") into your network PDMS Data folder.


    In your PDMS code (either PML or PML2) create your data file
    (we name ours the same as the Project Excel File except the extention is ".txt")
    then add the following code:

       !sfile = 'N:\MASTERDATA\PDMS\Data\PipeMTO.xls'  $* Change this to point to where you store your Excel Templates
       !tfile = !rootfile & '.xls'    $* !rootfile needs to contain the full path and name of your data dump's Excel File Name
                                      $* File name can't be PipeMTO!
       !syscom  = |copy $!sfile $!tfile| $* Copies Master Template Excel File to the Project Data Excel File
       syscom |$!syscom|
       syscom |start /min /wait excel $!tfile| $* Runs Excel and opens your Project File which in turn calls the Workbook_Open Sub.
       

    During the development cycle for a spreadsheet, I usually have to tweak the code a couple of times (PDMS and Excel both), but when done
    its complete and stable. When the Pipers/Managers want project specific changes, I just copy the Global master into a project master area and twek the spreadsheet there.

    We do not do Excel Formatting/Coding anyplace other then Excel (where it belongs).
  • Thanks egcallis...

    Sounds like more work than I'm currently doing...

    I use commas as delimeters...
    Open excel...
    Import Dat...using commas as delimeters...and I have my stuff...

    Cheers
    Neil
  • With reference to above...

    What is new module ? And Sub ?

    7. Add a Module to your Worksheet and add a Sub named something like ImportCVS:
    Public Sub ImportCSV(bCode As Boolean)

    Do you do this process once ?
    Are you basically creating a template with formating as you want...then you're copying it everytime with new data in ?

    Cheers
    Neil
  • Neil,

    Items 2-10 are done in Excel  and if you have never done any VBA programming, you'll have to get out the books. We only use this approach when we want the spreadsheet data to be custom formated (special colors, formulas, multiple sheets, etc) AND the data dump into Excel will occur many times. Otherwise we just create a CVS file and import into Excel.

    And yes, the only reason we do this is when we are going to produce a bunch of spreadsheets with different data (but the same formating/logic).