How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! Question

Hey, Scripting Guy! In an Excel spreadsheet, how can I locate and replace information for a specific computer?

— BB

SpacerHey, Scripting Guy! AnswerScript Center

Hey, BB. You know, there’s an old saying that goes, “If all you have is a hammer then everything looks like a nail.” Well, all we Scripting Guys have at the moment is some code for modifying a record in a database. So guess what: to us everything looks like a database, including your Excel spreadsheet.

Fortunately, though, you actually can use database techniques to work with an Excel spreadsheet. And while we don’t have to use this approach to answer your question, using some basic database commands seemed a lot easier than writing a bunch of code to open the spreadsheet, search for the computer in question, figure out what row and column we’re in, change the data, save the spreadsheet, etc. etc. How much easier did it seem? This much easier:

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

Before we talk about the script itself we should mention that, for this sample script, we’re assuming you have a very simple spreadsheet, one that looks something like this:

Hey, Scripting Guy!


As you can see, we have only two fields here: Name and IPAddress. Needless to say you aren’t limited to just two fields; we kept our spreadsheet small in order to keep the script as simple as possible (and to enable us to keep the screenshot as small as possible). But you can have as many fields as you want (or at least as many fields as there are columns in Excel).

We should also note that we won’t explain today’s script in minute detail; that’s because we have an Office Space article that already does that for us. We’ll focus primarily on the code that updates the spreadsheet, and simply refer you to the Office Space article for more information on the ADO (ActiveX Data Objects) code that lets you treat Excel as a database.

The script begins by defining three constants we need to use in order to make the ADO connection to the spreadsheet. After creating instances of the ADODB.Connection and ADODB.Recordset objects we then use this clunky-looking line of code to actually make the connection to the Excel spreadsheet:

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

Like we said, there’s no need to worry about things like Provider and Extended Properties, at least for now; instead, just note that you set the Data Source to the path to your Excel spreadsheet and let it go at that.

After connecting to the spreadsheet we then use this line of code to select all the records (that is, all the rows in the spreadsheet):

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

Notice that, in our Select statement, we select the worksheet where the data is stored; in this case, that’s Sheet1, so we specify [Sheet1$]. What if we needed to get information from a worksheet we renamed Computers? No problem; just change the query accordingly:

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

This gives us a recordset containing information about all the computers listed in the spreadsheet. That’s fine, except that we’re interested in only one computer: atl-fs-01. Therefore, we use these two lines of code to define a search criteria and to locate the record for atl-fs-01:

strSearchCriteria = “Name = ‘atl-fs-01′”
objRecordSet.Find strSearchCriteria

When we execute the Find method the cursor gets positioned at the record for atl-fs-01. (Incidentally, an error will be generated if no computer named atl-fs-01 can be found in the database. Because of that you might want to implement error-handling of some kind somewhere in the script.) What do we do now that we’ve found the record we were looking for? Why, we do this, of course:

objRecordset(“IPAddress”) = “192.168.1.100”
objRecordset.Update

All we’re doing here is assigning a new value to the IPAddress property; note that we don’t need to worry about specifying which computer we’re giving the new value. Why not? That’s easy: the Find method has already located the computer for us. After assigning the new IP address we then call Update to write the changes to the database. Which, in this case, means changing the appropriate cell in the spreadsheet.

Will this really work? Judge for yourself:

Hey, Scripting Guy!


Yes, we know: we thought we were crazy, too when we first suggested using ADO to update an Excel spreadsheet. But as you can see, ADO works pretty well, and with minimal effort on our part. In fact, if we could just use ADO to pound in all these nails we’d be home free. Anyone want to trade a database for a hammer?

0 comments

Discussion is closed.

Feedback usabilla icon