Tuesday, August 23, 2016

REM DISSEMINATES REVIT OOTB DRAG AND DROPPED FOLDER TO CLIENT-SPECIFIC NAMED FOLDERS
@echo
REM file-folder path for drag and drop
Set source=%~1
echo %source%

rem set source=C:\ProgramData\Autodesk\RVT 2015

echo
REM NAMING PREFIXES FOR MAIN FOLDERS
    REM VERSION SUFFIX
set Version=2015
    REM DESTINATION PATH
set NewPath=C:\temp\libtest
    REM CLIENT PREFIX
set Client=OOTB\ootb_

echo
set USImperial=%newpath%\%Client%I(US)-%Version%
set USMetric=%newpath%\%Client%M(US)-%Version%
set CANMetric=%newpath%\%Client%M(CAN)-%Version%

Set RoboPrefs=/e /Z /DCOPY:T /MT:8 /XO /IT /XJ /FFT /DST /XJD /XJF /R:0 /TBD /LOG+:%newpath%\RVT_%version%_SPLIT.txt
rem  Set RoboPrefs=/e /Z /DCOPY:T /MT:8 /XO /IT /XJ /FFT /DST /XJD /XJF /R:0 /TBD /LOG+:%newpath%\RVT_%version%_SPLIT.txt /TEE
REM --SET COPY  English Templates (US)
set Src=%source%\Family Templates\English_I\
set Dest=%USImperial%\000-TEMPLATES-FAMILY
robocopy "%Src%" "%Dest%" %RoboPrefs%
mkdir %USImperial%\000-TEMPLATES-PROJECT


REM --SET COPY **METRIC TEMPLATES**
set Src=%source%\Family Templates\English\
set Dest=%USMetric%\000-TEMPLATES-FAMILY
robocopy "%Src%" "%dest%" %RoboPrefs%
set Dest=%CANMetric%\000-TEMPLATES-FAMILY
robocopy "%Src%" "%dest%" %RoboPrefs%
mkdir %USMetric%\000-TEMPLATES-PROJECT


REM --SET COPY IES For Metric & Imperial
set Src=%source%\ies\
set Dest=%USImperial%\Lighting\000-IES
robocopy "%Src%" "%dest%" %RoboPrefs%
set Dest=%CANmetric%\Lighting\000-IES
robocopy "%Src%" "%dest%" %RoboPrefs%
mkdir %CANmetric%\000-TEMPLATES-PROJECT

REM --SET COPY Imperial Library<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
set Src=%source%\Libraries\US Imperial
set Dest=%USImperial%
robocopy "%Src%"  "%dest%" %RoboPrefs%

REM --SET COPY US Metric Libraries<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
set Src=%source%\Libraries\US METRIC
set Dest=%USmetric%
robocopy "%Src%"  "%dest%%RoboPrefs%

REM --SET COPY CAN METRIC LIBRARIES
set Src=%source%\Libraries\Canada
set Dest=%CANmetric%
robocopy "%Src%"  "%dest%%RoboPrefs%

REM --SET COPY CONDUIT TABLES - Metric & Imperial
set Src=%source%\Lookup Tables\Conduit
set Dest=%USImperial%\Conduit\000-Lookup_Conduit
robocopy "%Src%" "%dest%" %RoboPrefs%
set Dest=%USMetric%\Conduit\000-Lookup_Conduit
robocopy "%Src%" "%dest%" %RoboPrefs%
set Dest=%CANmetric%\Conduit\000-Lookup_Conduit
robocopy "%Src%"  "%dest%%RoboPrefs%

REM --SET COPY Metric & Imperial
set Src=%source%\Lookup Tables\Pipe
set Dest=%USImperial%\PIPE\000-Lookup_Pipe
robocopy "%Src%" "%dest%" %RoboPrefs%
set Dest=%USMetric%\PIPE\Lookup_Pipe
robocopy "%Src%" "%dest%" %RoboPrefs%
set Dest=%CANmetric%\PIPE\Lookup_Pipe
robocopy "%Src%"  "%dest%%RoboPrefs%

REM --SET COPY Project Templates
set Src=%source%\Templates\US Imperial
set Dest=%USImperial%\000-TEMPLATES-PROJECT
robocopy "%Src%" "%dest%" %RoboPrefs%

set Src=%source%\Templates\US Metric
set Dest=%USMetric%\000-TEMPLATES-PROJECT
robocopy "%Src%"  "%dest%%RoboPrefs%

set Src=%source%\Templates\Canada
set Dest=%CANMetric%\000-TEMPLATES-PROJECT
robocopy "%Src%" "%dest%" %RoboPrefs%

REM --SET COPY Dictionaries
set Src=%source%
set Dest=%USImperial%\000-KEYS-CLASS-DIC
robocopy "%Src%" "%dest%"  revitEN?.dic
set Dest=%USMetric%\000-KEYS-CLASS-DIC
robocopy "%Src%" "%dest%"  revitEN?.dic
set Dest=%CANmetric%\000-KEYS-CLASS-DIC
robocopy "%Src%" "%dest%"  revitEN?.dic



pause


Thursday, August 18, 2016

''ORACLE FORMATS TO TEXT- Missing is an ISO LIST in EXCEL of the COUNTRY/STATE codes.
Attribute VB_Name = "tEXTtOOLS"

Option Explicit

Function CleanString(strString As String)
Dim result, re

   Set re = New RegExp
   With re
    .Global = True
    .IgnoreCase = True
    '''http://www.regular-expressions.info/vbscriptexample.html
    '''regex   =      *.  9999    .  RFA or RVT or RTE
    ''.Pattern = ".*?(\.)(\d{4})(\.)(rfa|rvt|rte)"
    .Pattern = "[^A-Z,0-9,a-z]"
    CleanString = .Replace(strString, "_")
   End With


Set re = Nothing
End Function

Function SplitOracle(strOracle As String, Optional retval As Integer)
''United States/CA/San Diego/US - San Diego, CA - 401 West A Street
retval = retval - 1
Dim result, re, objRow
Dim ISO As ListObject
Set ISO = shtISO3166.ListObjects("ISO3166x")
       
On Error GoTo SplitOracle_Err

   Set re = New RegExp
   With re
    .Global = True
    .IgnoreCase = True
    .Pattern = "(.*?)(?:\/|,|$)"
    ''CleanString = .Replace(strString, "_")
    Set result = .Execute(Trim(strOracle))
   End With
  
   If retval > result.count - 1 Then retval = result.count
  
    SplitOracle = UCase(result(retval).Value)
    SplitOracle = Trim(Left(SplitOracle, Len(SplitOracle) - 1))
  
    ''UNITED STATES OF AMERICA (THE) is proper ISO3166 formats the name
    If SplitOracle = "UNITED STATES" Then SplitOracle = UCase("United States of America (the)") ''commonly malformed entry IN ORACLE
       
   Select Case retval
    Case 0: ''country code

        For Each objRow In ISO.DataBodyRange.Rows
            If UCase(objRow.Cells(1, 1)) Like "*" & SplitOracle & "*" Then
                SplitOracle = UCase(objRow.Cells(1, 4).Value)
                Exit For
            End If
        Next objRow

    Case Else:
        With re
         .Global = True
         .IgnoreCase = True
         .Pattern = "[^A-Z,0-9,a-z]"
         ''CleanString = .Replace(strString, "_")
         SplitOracle = .Replace(SplitOracle, "_")
        End With
    End Select


GoTo SplitOracle_cleanup:
SplitOracle_Err:
SplitOracle = "#ERR"

SplitOracle_cleanup:
Set re = Nothing

End Function

Function OracleToName(strOracle As String)

''United States/CA/San Diego/US - San Diego, CA - 401 West A Street
Dim result, re, objRow
Dim intResults As Integer
Dim arrX() As String
Dim I As Integer

Dim ISO As ListObject
Set ISO = shtISO3166.ListObjects("ISO3166x")
       
On Error GoTo OracleToName_cleanup_Err

   Set re = New RegExp
   With re
    .Global = True
    .IgnoreCase = True
    .Pattern = "(.*?)(?:\/|,|$)"
    ''CleanString = .Replace(strString, "_")
    Set result = .Execute(Trim(strOracle))
   End With
   intResults = result.count - 1
  
   ReDim arrX(0 To intResults)
  
   For I = 0 To intResults

    ''regexp doesn't behave exactly correctly to the vanilla version- clean up delimeters:
    arrX(I) = UCase(result(I).Value) ''set workable value
   
    Do While Right(arrX(I), 1) = "/" Or Right(arrX(I), 1) = "," Or Right(arrX(I), 1) = " " Or Right(arrX(I), 1) = "\"
        arrX(I) = Trim(Left(arrX(I), Len(arrX(I)) - 1))
    Loop

    ''UNITED STATES OF AMERICA (THE) is proper ISO3166 formats the name
    If arrX(I) = "UNITED STATES" Then arrX(I) = UCase("United States of America (the)")   ''commonly malformed entry IN ORACLE
      
    Select Case I
    Case 0: ''country code
   
        For Each objRow In ISO.DataBodyRange.Rows
            If UCase(objRow.Cells(1, 1)) Like "*" & arrX(I) & "*" Then
                arrX(I) = UCase(objRow.Cells(1, 4).Value)
                Exit For
            End If
        Next objRow
   
    Case Else:
        With re
         .Global = True
         .IgnoreCase = True
         .Pattern = "[^A-Z,0-9,a-z]"
         ''CleanString = .Replace(strString, "_")
         arrX(I) = .Replace(arrX(I), "_")
        End With
    End Select
    Next I

    I = 0
    Do While arrX(I) > ""
        If I > 0 Then OracleToName = OracleToName & "."
        OracleToName = OracleToName & arrX(I)
        I = I + 1
    Loop


GoTo OracleToName_cleanup:
''''''''''''''''''''''''''''''''''''''''''''''''
'''error out
OracleToName_cleanup_Err:
'MsgBox Err.Description, vbCritical + vbOKOnly
'Stop
'Resume
'
''VBA provides a function called CVErr that takes a numeric input parameter
'specifying the error and returns a real error value that Excel will recognize
'as an error. The values of the input parameter to CVErr are in the XLCVError
'Enum and are as follows:
'
'xlErrDiv0 (= 2007) returns a #DIV/0! error.
'xlErrNA (= 2042) returns a #N/A error.
'xlErrName (= 2029) returns a #NAME? error.
'xlErrNull (= 2000) returns a #NULL! error.
'xlErrNum (= 2036) returns a #NUM! error.
'xlErrRef (= 2023) returns a #REF! error.
'xlErrValue (= 2015) returns a #VALUE! error.
OracleToName = CVErr(xlErrNA)

'''''''''''''''''''''''''''''''''''''''''''''''''''
''exit clean
OracleToName_cleanup:
    Set re = Nothing
   
End Function