Hello Randy & James,

Below are the forwarded instructions as to how we plan to update the excel dsn's for netco(UBSWenergy).  Only test excels in "_converted" folders, then create/move successful tests to a "_tested" folder (within the corresponding "_converted" folder).  

Call me if you have any prob/questions. 

Thanks,
Hal
x53707

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



<Embedded Picture (Device Independent Bitmap)>
<Embedded Picture (Device Independent Bitmap)>