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 ?
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).
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).
