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