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

Excel object

sarunbe (Arun Kumar) — Wed, 18 Jul 2007 14:14:08 GMT

// Excel object



// 'create object

Set excelobj = CreateObject( "Excel.Application")

Set exp_data_workbook=excelobj.workbooks.open("c:\debug_expected.xls")





'get the sheet count

exp_sheetcnt=exp_data_workbook.Sheets.Count

	



'select a sheet

For i = 1 to exp_sheetcnt

	sheet_tblname=exp_data_workbook.Sheets(i).Name

	If  UCASE(Trim(sheet_tblname)) = "Sheet_name" Then

		exp_data_workbook.Sheets(i).select



		'once selected set the range object	

		set exp_rangeobj=exp_data_workbook.Sheets(i).UsedRange

	end if

next





'Get the column count for the 



columncnt=exp_rangeobj.columns.count



'find  the column index of  the column column_name

		For i=1 to columncnt

			colname=exp_rangeobj.cells(1,i).value

			If UCASE(Trim(colname)) = "column_name" Then

				pos_id_idx = i

				Exit For

			End If

		Next		



'proceess the value from that column



rowcnt=exp_rangeobj.rows.count



		'Strat reading the values from the 2nd row since the first row contains the column names.

		If rowcnt > 1 Then

			For i=2 to rowcnt				 

				'process the expected file row if it has a value

				If  Len(Trim(exp_rangeobj.cells(i,1).value))>0 Then 

					posIndex = (exp_rangeobj.cells(i,pos_id_idx).value) -1

				end if

			next 

		end if

Create an SQLite Database from an Excel Workbook with Ruby

dmullet (David Mullet) — Wed, 27 Jun 2007 14:16:07 GMT

From the Ruby on Windows blog.

Here's a brief, unpolished snippet of code that reads data from an open Excel workbook and creates an SQLite database with a table for each worksheet in the Excel workbook:



require 'win32ole'

require 'sqlite3'



#   Connect to a running instance of Excel

xl = WIN32OLE.connect('Excel.Application')

#   Get the active workbook

wb = xl.ActiveWorkbook

#   Create the SQLite3 database

db = SQLite3::Database.new('excel.db')

#   Create a database table for each worksheet 

#   in the workbook

wb.Worksheets.each do |ws|

    #   Grab all values from worksheet into a 

    #   2-dimensional array

    data = ws.UsedRange.Value

    #   Grab first row of data to use as field names

    field_names = data.shift

    #   Create database table using worksheet name and 

    #   field names

    db.execute("CREATE TABLE [#{ws.Name}] \

        ( #{field_names.join(',')} );")

    #   For each row of data...

    data.each do |row|

        #   ...single-quote all field values...

        row.collect! { |f| f = "'" + f.to_s + "'" }

        #   ...and insert a new record into the 

        #   database table

        db.execute("INSERT INTO [#{ws.Name}] VALUES \

            ( #{row.join(',')} );")

    end

end

Further discussion can be found here.

Detect a field edit in Excel and refresh a Query

davetrane (David Davis) — Fri, 02 Mar 2007 04:29:25 GMT



Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wks As Worksheet

    Set wks = ActiveSheet



    If Target.Row = 1 And Target.Column = 1 Then

      wks.QueryTables(1).Refresh

    End If



    Set wks = Nothing

End Sub

Script Excel in Ruby

timmorgan (Tim Morgan) — Thu, 01 Mar 2007 14:18:31 GMT



require 'win32ole'

excel = WIN32OLE.new('excel.application')

excel.visible = true

excel.workbooks.open(file_path) # absolute file path

excel.range('A1').value = 'Hello, world.' # get/set value

VBA DDE WORD EXCEL

millerjohneric (John Miller) — Fri, 08 Dec 2006 22:50:58 GMT

// This is just sample code of some dde commands



Sub RUNEXCELMACRO()

'RUN MACRO

aChan = DDEInitiate(App:="Excel", Topic:="System")

DDEExecute Channel:=aChan, Command:="[Run(" & Chr(34) & _

    "Personal.xls!Macro1" & Chr(34) & ")]"

DDETerminate Channel:=aChan

'POKE

Chan = DDEInitiate(App:="Excel", Topic:="System")

DDEExecute Channel:=Chan, Command:="[OPEN(" & Chr(34) _

    & "C:\Sales.xls" & Chr(34) & ")]"

DDETerminate Channel:=Chan

Chan = DDEInitiate(App:="Excel", Topic:="Sales.xls")

DDEPoke Channel:=Chan, Item:="R1C1", Data:="1996 Sales"

DDETerminate Channel:=Chan

'This example opens the Microsoft Excel workbook Book1.xls and retrieves the contents of cell R1C1.

Chan = DDEInitiate(App:="Excel", Topic:="System")

DDEExecute Channel:=Chan, Command:="[OPEN(" & Chr(34) _

    & "C:\My Documents\Book1.xls" & Chr(34) & ")]"

DDETerminate Channel:=Chan

Chan = DDEInitiate(App:="Excel", Topic:="C:\DATA\SBS.xls")

msg = DDERequest(Channel:=Chan, Item:="R2C1")

msg = msg & " " & DDERequest(Channel:=Chan, Item:="R2C2")

MsgBox msg

DDETerminateAll

'This example opens a channel to the System topic in Microsoft Excel and then uses the Topics item to return a list of available topics. The example inserts the topic list, which includes all open workbooks, after the selection.

aChan = DDEInitiate(App:="Excel", Topic:="System")

TOPICLIST = DDERequest(Channel:=aChan, Item:="Topics")

Selection.InsertAfter TOPICLIST

DDETerminate Channel:=aChan



End Sub

Script Excel from Python

timmorgan (Tim Morgan) — Tue, 16 May 2006 00:07:51 GMT

A quick and dirty class for working with Excel via COM in Python. By far, not all of the power of Excel is available here, but it's a good start for simple tasks.



from win32com.client import constants, Dispatch

import pythoncom

import os



borderTop = 3

borderBottom = 4

borderLeft = 1

borderRight = 2

borderSolid = 1

borderDashed = 2

borderDotted = 3

colorBlack = 1

directionUp = -4162

directionDown = -4121

directionLeft = -4131

directionRight = -4152



class ExcelDocument(object):

  """

  Some convenience methods for Excel documents accessed

  through COM.

  """

  

  def __init__(self, visible=False):

    self.app = Dispatch("Excel.Application")

    self.app.Visible = visible

    self.sheet = 1

  

  def new(self, filename=None):

    """

    Create a new Excel workbook. If 'filename' specified,

    use the file as a template.

    """

    self.app.Workbooks.Add(filename)

  

  def open(self, filename):

    """

    Open an existing Excel workbook for editing.

    """

    self.app.Workbooks.Open(filename)

  

  def set_sheet(self, sheet):

    """

    Set the active worksheet.

    """

    self.sheet = sheet

  

  def get_range(self, range):

    """

    Get a range object for the specified range or single cell.

    """

    return self.app.ActiveWorkbook.Sheets(self.sheet).Range(range)

  

  def set_value(self, cell, value=''):

    """

    Set the value of 'cell' to 'value'.

    """

    self.get_range(cell).Value = value

  

  def get_value(self, cell):

    """

    Get the value of 'cell'.

    """

    value = self.get_range(cell).Value

    if isinstance(value, tuple):

      value = [v[0] for v in value]

    return value

  

  def set_border(self, range, side, line_style=borderSolid, color=colorBlack):

    """

    Set a border on the specified range of cells or single cell.

    'range' = range of cells or single cell

    'side' = one of borderTop, borderBottom, borderLeft, borderRight

    'line_style' = one of borderSolid, borderDashed, borderDotted, others?

    'color' = one of colorBlack, others?

    """

    range = self.get_range(range).Borders(side)

    range.LineStyle = line_style

    range.Color = color

  

  def sort(self, range, key_cell):

    """

    Sort the specified 'range' of the activeworksheet by the

    specified 'key_cell'.

    """

    range.Sort(Key1=self.get_range(key_cell), Order1=1, Header=0, OrderCustom=1, MatchCase=False, Orientation=1)

  

  def hide_row(self, row, hide=True):

    """

    Hide the specified 'row'.

    Specify hide=False to show the row.

    """

    self.get_range('a%s' % row).EntireRow.Hidden = hide

  

  def hide_column(self, column, hide=True):

    """

    Hide the specified 'column'.

    Specify hide=False to show the column.

    """

    self.get_range('%s1' % column).EntireColumn.Hidden = hide

  

  def delete_row(self, row, shift=directionUp):

    """

    Delete the entire 'row'.

    """

    self.get_range('a%s' % row).EntireRow.Delete(Shift=shift)

  

  def delete_column(self, column, shift=directionLeft):

    """

    Delete the entire 'column'.

    """

    self.get_range('%s1' % column).EntireColumn.Delete(Shift=shift)

    

  def fit_column(self, column):

    """

    Resize the specified 'column' to fit all its contents.

    """

    self.get_range('%s1' % column).EntireColumn.AutoFit()

  

  def save(self):

    """

    Save the active workbook.

    """

    self.app.ActiveWorkbook.Save()

  

  def save_as(self, filename, delete_existing=False):

    """

    Save the active workbook as a different filename.

    If 'delete_existing' is specified and the file already

    exists, it will be deleted before saving.

    """

    if delete_existing and os.path.exists(filename):

      os.remove(filename)

    self.app.ActiveWorkbook.SaveAs(filename)

  

  def print_out(self):

    """

    Print the active workbook.

    """

    self.app.Application.PrintOut()

  

  def close(self):

    """

    Close the active workbook.

    """

    self.app.ActiveWorkbook.Close()

  

  def quit(self):

    """

    Quit Excel.

    """

    return self.app.Quit()