¡Hola, chicos del scripting!

  • Artículo

Bienvenido a la columna TechNet en la cual, por medio de Microsoft Scripting Guy, se abordan las preguntas frecuentes acerca de las secuencias de comandos para la administración de sistemas. ¿Tiene alguna pregunta sobre las secuencias de comandos para la administración de sistemas? Envíe un correo electrónico a scripter@microsoft.com. No podemos garantizarle que seremos capaces de responder todas las preguntas que nos lleguen, pero haremos todo lo posible.

Y no se olvide de consultar el Archivo de ¡Hola, chicos del scripting!.

Pregunta del día: en una hoja de cálculo de Excel, ¿cómo puedo buscar y reemplazar información de un equipo específico?

¿Cómo puedo buscar y reemplazar información de un elemento específico en una hoja de cálculo?

P

¡Hola, chicos del scripting! En una hoja de cálculo de Excel, ¿cómo puedo buscar y reemplazar información de un equipo específico?

-- BB
R

Hola BB. Existe un viejo dicho que señala algo así como “Si todo lo que tienes es un martillo, todo te parecerá un clavo.” Bien, todo lo que los chicos del scripting tenemos en este momento es código para modificar un registro en una base de datos. Así que adivine: a nosotros todo nos parece una base de datos, incluida su hoja de cálculo de Excel.

Pero, por suerte, se pueden utilizar técnicas de bases de datos para trabajar con las hojas de cálculo de Excel. Y aunque no tenemos que seguir este enfoque para responder a su pregunta, utilizar algunos comandos de bases de datos básicos nos parece mucho más sencillo que escribir código para abrir la hoja de cálculo, buscar el equipo en cuestión, adivinar en qué fila y columna nos encontramos, cambiar los datos, guardar la hoja de cálculo, etc., etc. ¿Realmente es más sencillo? Compruébelo con este código:

 
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\Inventory.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";" 
objRecordset.Open "Select * FROM [Sheet1$]", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText
strSearchCriteria = "Name = 'atl-fs-01'"
objRecordSet.Find strSearchCriteria
objRecordset("IPAddress") = "192.168.1.100"
objRecordset.Update
objRecordset.Close
objConnection.Close

Antes de hablar de la propia secuencia de comandos, debemos mencionar que para esta secuencia de muestra hemos supuesto que dispone de una hoja de cálculo muy sencilla, con un aspecto similar al siguiente:

Como puede observar, sólo tenemos dos campos: Name e IPAddress. No es necesario aclarar que pueden existir más campos; en nuestro caso deseábamos que la hoja de cálculo no fuera muy extensa para que la secuencia de comandos fuera lo más simple posible (y para que pudiéramos incluir una captura de pantalla lo más pequeña posible). Pero puede incluir tantos campos como desee (o al menos tantos como permitan las columnas de Excel).

Debemos señalar que no vamos a proporcionar una explicación detallada de la secuencia, ya que se ha hecho anteriormente en un artículo de Office Space (en inglés). Nos centraremos fundamentalmente en el código que actualiza la hoja de cálculo, por lo que le invitamos a que consulte el artículo de Office Space para que obtenga más información sobre el código ADO (objetos de datos ActiveX) que permite tratar Excel como una base de datos.

La secuencia comienza definiendo tres constantes que debemos utilizar para establecer la conexión de ADO con la hoja de cálculo. Tras crear instancias de los objetos ADODB.Connection y ADODB.Recordset, utilizamos la siguiente línea de código para establecer la conexión con la hoja de cálculo de Excel:

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\Inventory.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"

Como mencionamos anteriormente, no debe preocuparse de cosas como Provider y Extended Properties, al menos por el momento; simplemente observe que Data Source se establece como la ruta a la hoja de Excel.

Después de conectar con la hoja de cálculo, empleamos esta línea de código para seleccionar todos los registros (es decir, todas las filas que contiene):

objRecordset.Open "Select * FROM [Sheet1$]", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Observe que en la instrucción Select seleccionamos la hoja en la que se almacenan los datos; en este caso Sheet1, por lo que especificamos [Sheet1$]. Y ¿qué ocurre si necesitamos obtener información de una hoja a la que hemos cambiado el nombre a Computers? Ningún problema; basta con cambiar la consulta:

objRecordset.Open "Select * FROM [Computers$]", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Esto nos proporciona un conjunto de registros que contiene información de todos los equipos enumerados en la hoja de cálculo. Todo bien, excepto por el hecho de que sólo nos interesaba un equipo: atl-fs-01. Por este motivo utilizamos estas dos líneas de código para definir criterios de búsqueda y localizar el registro de atl-fs-01:

strSearchCriteria = "Name = 'atl-fs-01'"
objRecordSet.Find strSearchCriteria

Cuando ejecutamos el método Find, el cursor se posiciona en el registro correspondiente a atl-fs-01. (Por cierto, se generará un error si no se encontrara ningún equipo llamado atl-fs-01 en la base de datos. Por este motivo podría ser que deseara implementar algún tipo de un control de errores en la secuencia de comandos.) ¿Y qué hacemos una vez que hemos encontrado el registro que buscábamos? ¿Por qué preguntar? Hacemos lo siguiente, por supuesto:

objRecordset("IPAddress") = "192.168.1.100"
objRecordset.Update

Asignamos un nuevo valor a la propiedad IPAddress; observe que no debemos preocuparnos por especificar a qué equipo se lo asignamos. ¿Por qué? Muy sencillo: el método Find ya se ha encargado de localizar el equipo. Tras asignar la nueva dirección IP, llamamos a Update para que escriba los cambios en la base de datos. Lo que en este caso significa cambiar la celda adecuada en la hoja de cálculo.

¿Realmente funcionará? Juzgue por sí mismo:

Sí, lo sabemos: también pensábamos que estábamos locos cuando sugerimos por primera vez utilizar ADO para actualizar una hoja de cálculo de Excel. Pero como puede comprobar, ADO funciona bastante bien y con el mínimo esfuerzo por nuestra parte. De hecho, si pudiéramos utilizar ADO para clavar todos los clavos que encontramos, no tendríamos nada que hacer y podríamos marcharnos a casa. ¿Algún interesado en intercambiar una base de datos por un martillo?

Para más Información

Consulte el Archivo de ¡Hola, chicos del scripting!.

Arriba Arriba