DZone Snippets: excel code

Excel VBA : read registry key values on a remote computer using WMI

bouffon69 (Sylvain Le Courtois) — Fri, 19 Oct 2007 12:03:49 GMT

// VBA code to paste in an module



Function ORACLEHOMES(strComputer As String)

    Const HKEY_LOCAL_MACHINE = &H80000002

    ORACLEHOMES = ""

    Dim strKeyPath

    Dim arrSubKeys

    Dim oReg

    Dim strValueName

    Dim strValue

    'strComputer = "."

    Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")

    strKeyPath = "SOFTWARE\ORACLE"

    oReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys

    For Each subkey In arrSubKeys

        If Left(subkey, 4) = "KEY_" Then

            strValueName = "ORACLE_HOME"

            oReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath & "\" & subkey, strValueName, strValue

            If ORACLEHOMES = "" Then

                ORACLEHOMES = strValue

            Else

                ORACLEHOMES = ORACLEHOMES & ";" & strValue

            End If

        End If

    Next

End Function

Excel : Make a query on a Oracle database and return the result (useful for sheet formulas)

bouffon69 (Sylvain Le Courtois) — Mon, 10 Sep 2007 15:26:22 GMT

// This should be pasted in a module of the workbook



Function ORAQUERY(strHost As String, strDatabase As String, strSQL As String, strUser As String, strPassword As String)

  Dim strConOracle, oConOracle, oRsOracle

  Dim StrResult As String

  

  StrResult = ""

  

  strConOracle = "Driver={Microsoft ODBC for Oracle}; " & _

         "CONNECTSTRING=(DESCRIPTION=" & _

         "(ADDRESS=(PROTOCOL=TCP)" & _

         "(HOST=" & strHost & ")(PORT=1521))" & _

         "(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"

  Set oConOracle = CreateObject("ADODB.Connection")

  Set oRsOracle = CreateObject("ADODB.Recordset")

  oConOracle.Open strConOracle

  Set oRsOracle = oConOracle.Execute(strSQL)

  Do While Not oRsOracle.EOF

      If StrResult <> "" Then

        StrResult = StrResult & Chr(10) & oRsOracle.Fields(0).Value

      Else

        StrResult = oRsOracle.Fields(0).Value

      End If

    oRsOracle.MoveNext

  Loop

  oConOracle.Close

  Set oRsOracle = Nothing

  Set oConOracle = Nothing

  ORAQUERY = StrResult

End Function

VBA procedure to pen an Excel workbook and refresh all datas in the QueryTables and PivotTable objects

bouffon69 (Sylvain Le Courtois) — Fri, 07 Sep 2007 07:44:29 GMT

// description of your code here
// Can also use the Workbook Open event ( Private Sub Workbook_Open() )



Sub Auto_Open()

Application.DisplayAlerts = False

    ChDir "T:\EXPLOIT\TSMENV\EXCEL\politiques"

    Workbooks.Open Filename:="t:\EXPLOIT\TSMENV\EXCEL\politiques\politiques.xls", _

        UpdateLinks:=3

    For i = 1 To ActiveWorkbook.PivotCaches.Count

        ActiveWorkbook.PivotCaches(i).RefreshOnFileOpen = False

    Next

    For i = 1 To ActiveWorkbook.Sheets.Count

        For j = 1 To ActiveWorkbook.Sheets(i).QueryTables.Count

            ActiveWorkbook.Sheets(i).QueryTables(j).RefreshOnFileOpen = False

        Next

    Next

    ActiveWorkbook.RefreshAll

    ActiveWorkbook.RefreshAll

    ActiveWorkbook.RefreshAll

    ActiveWorkbook.Save

    ActiveWindow.Close

    Application.Quit

End Sub