Message-ID: <12599979.1075840183213.JavaMail.evans@thyme> Date: Tue, 29 Jan 2002 13:36:00 -0800 (PST) From: hal.mckinney@enron.com To: kam.keiser@enron.com Subject: FW: Netco(UBSWenergy) Excel Conversions Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-From: McKinney, Hal X-To: Keiser, Kam X-cc: X-bcc: X-Folder: \ExMerge - Keiser, Kam\Inbox X-Origin: KEISER-K X-FileName: kam keiser 7-11-02.PST FYI: -----Original Message----- From: McKinney, Hal Sent: Tuesday, January 29, 2002 3:21 PM To: Rivera, Oscar; Lin, Yi; Khuri, Basem; Frisbie, Randal; Woo, Tai; Cirlig, Bogdan; Fraylon, Damon; Leggett, Tim; Gupta, Sanjeev; Ma, Felicia; Cheung, Cecilia; Pathak, Sanjesh; Arellano, Fabian Dan Cc: Lim, Francis S. Subject: Netco(UBSWenergy) Excel Conversions Hello again, We need to convert DSN references in more excel spreadsheets... this time to be used by NetCo(UBSWenergy). The location of these files begins here: O:\erms\erms_adm\NetCo including all sub-folders. Convert excel files(.xls) only. After you convert a spreadsheet move it to your current folder's "_Converted" folder. Before you start, please run both the "ODBC DSN Entries for ERMT" and "ODBC DSN Entries for RMS" scripts. These can be found by going thru the menus "Start", "Programs","System Utility Applications", then "Installation & Diskette Creation". Afterwards you should see the new DSN's by running your windows "Data Sources (ODBC)" program and looking on the "System DSN" tab. Note that the DSN's using Oracle drivers now are followed by "-ora" and the DSN's without "-ora" were previously reserved for use with Microsoft (for oracle) drivers. Remember, after opening an excel to convert, type [Alt]-[F11] to get to the VBA code. Then type [Ctrl]-[F] and type "dsn" in the "Find What" field. Make sure the "Search" selection is on "Current Project". Keep pressing [Find Next] until you have addressed all dsn references. The goal is to change all old dsn references to point to the new Netco(UBSWenergy) databases. For example egsprod will become "ermtp-ora". Do NOT put "est" in front of these dsn's because that will point to the old ESTate versions of the database! ERMS Examples: Example 1: This is the most common type of change. Original: ODBCString = "ODBC;DSN=egsprod;UID=" & Sheet2.Range("UID") & ";PWD=" & Sheet2.Range("PW") Should be changed to: ODBCString = "ODBC;DSN=ermtp-ora;UID=" & Sheet2.Range("UID") & ";PWD=" & Sheet2.Range("PW") Example 2: Original ODBCstring = "ODBC;DSN=egsprod;DBQ=ermt;UID=" & Worksheets("Top Pages").Range("UID") & ";PWD=" & Worksheets("Top Pages").Range("PW") Should be changed to: ODBCstring = "ODBC;DSN=ermtp-ora;DBQ=ermtp;UID=" & Worksheets("Top Pages").Range("UID") & ";PWD=" & Worksheets("Top Pages").Range("PW") (Note that IF their is a DBQ reference, it does NOT have "-ora" afterwards. This s/b the actual oracle database name) Example 3: In this example note that the DSN is referencing the variable "DBase" which points to cell B3 (see print-screen below) ODBCString = "ODBC;DSN=" & DBase & ";UID=" & UserName & ";PWD=" & Password Originally: Should be changed to: Example 4: RMS Example Original: .Connection = "ODBC;DSN=New Rms Production;DBQ=tns:rmsprod_ded;UID=jdoe_pc;PWD=traderjoe;""" Should be changed to: .Connection = "ODBC;DSN=rmsp-ora;DBQ=tns:rmsp;UID=jdoe_pc;PWD=traderjoe;""" (Note that IF their is a DBQ reference, it does NOT have "-ora" afterwards. This s/b the actual oracle database name) There are 650+ excel files to convert so we really appreciate all of the help we can get. Please call me if you have any questions. Thanks, (very much!) Hal McKinney (713)345-3707