{"id":67593,"date":"2006-04-06T10:15:00","date_gmt":"2006-04-06T10:15:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/04\/06\/how-can-i-locate-and-replace-information-for-a-specific-item-in-a-spreadsheet\/"},"modified":"2006-04-06T10:15:00","modified_gmt":"2006-04-06T10:15:00","slug":"how-can-i-locate-and-replace-information-for-a-specific-item-in-a-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-locate-and-replace-information-for-a-specific-item-in-a-spreadsheet\/","title":{"rendered":"How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

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

— BB<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, BB. You know, there\u2019s an old saying that goes, \u201cIf all you have is a hammer then everything looks like a nail.\u201d 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.<\/P>\n

Fortunately, though, you actually can<\/I> use database techniques to work with an Excel spreadsheet. And while we don\u2019t have<\/I> 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\u2019re in, change the data, save the spreadsheet, etc. etc. How much easier did it seem? This much easier:<\/P>

On Error Resume Next<\/p>\n
Const adOpenStatic = 3\nConst adLockOptimistic = 3\nConst adCmdText = &H0001<\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)\nSet objRecordSet = CreateObject(“ADODB.Recordset”)<\/p>\n
objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _\n    “Data Source=C:\\Scripts\\Inventory.xls;” & _\n        “Extended Properties=””Excel 8.0;HDR=Yes;””;” <\/p>\n
objRecordset.Open “Select * FROM [Sheet1$]”, _\n    objConnection, adOpenStatic, adLockOptimistic, adCmdText<\/p>\n
strSearchCriteria = “Name = ‘atl-fs-01′”\nobjRecordSet.Find strSearchCriteria<\/p>\n
objRecordset(“IPAddress”) = “192.168.1.100”\nobjRecordset.Update<\/p>\n
objRecordset.Close\nobjConnection.Close\n<\/PRE>\n
Before we talk about the script itself we should mention that, for this sample script, we\u2019re assuming you have a very simple spreadsheet, one that looks something like this:<\/P>\"Hey, \n

As you can see, we have only two fields here: Name<\/B> and IPAddress<\/B>. Needless to say you aren\u2019t 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).<\/P>\n
We should also note that we won\u2019t explain today\u2019s script in minute detail; that\u2019s because we have an Office Space<\/B><\/A> article that already does that for us. We\u2019ll 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.<\/P>\n
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<\/B> and ADODB.Recordset<\/B> objects we then use this clunky-looking line of code to actually make the connection to the Excel spreadsheet:<\/P>
objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _\n    “Data Source=C:\\Scripts\\Inventory.xls;” & _\n        “Extended Properties=””Excel 8.0;HDR=Yes;””;”\n<\/PRE>\n
Like we said, there\u2019s no need to worry about things like Provider and Extended Properties, at least for now; instead, just note that you set the Data Source<\/B> to the path to your Excel spreadsheet and let it go at that.<\/P>\n
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):<\/P>
objRecordset.Open “Select * FROM [Sheet1$]”, _\n    objConnection, adOpenStatic, adLockOptimistic, adCmdText\n<\/PRE>\n
Notice that, in our Select statement, we select the worksheet where the data is stored; in this case, that\u2019s Sheet1, so we specify [Sheet1$]<\/B>. What if we needed to get information from a worksheet we renamed Computers? No problem; just change the query accordingly:<\/P>
objRecordset.Open “Select * FROM [Computers$]”, _\n    objConnection, adOpenStatic, adLockOptimistic, adCmdText\n<\/PRE>\n
This gives us a recordset containing information about all the computers listed in the spreadsheet. That\u2019s fine, except that we\u2019re interested in only one<\/I> 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:<\/P>
strSearchCriteria = “Name = ‘atl-fs-01′”\nobjRecordSet.Find strSearchCriteria\n<\/PRE>\n
When we execute the Find<\/B> 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\u2019ve found the record we were looking for? Why, we do this, of course:<\/P>
objRecordset(“IPAddress”) = “192.168.1.100”\nobjRecordset.Update\n<\/PRE>\n
All we\u2019re doing here is assigning a new value to the IPAddress property; note that we don\u2019t need to worry about specifying which computer we\u2019re giving the new value. Why not? That\u2019s easy: the Find method has already located the computer for us. After assigning the new IP address we then call Update<\/B> to write the changes to the database. Which, in this case, means changing the appropriate cell in the spreadsheet.<\/P>\n
Will this really work? Judge for yourself:<\/P>\"Hey, \n

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\u2019d be home free. Anyone want to trade a database for a hammer?<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! In an Excel spreadsheet, how can I locate and replace information for a specific computer?— BB Hey, BB. You know, there\u2019s an old saying that goes, \u201cIf all you have is a hammer then everything looks like a nail.\u201d Well, all we Scripting Guys have at the moment is some code for […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[19,146,710,48,49,3,5],"class_list":["post-67593","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-activex-data-objects-ado","tag-databases","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! In an Excel spreadsheet, how can I locate and replace information for a specific computer?— BB Hey, BB. You know, there\u2019s an old saying that goes, \u201cIf all you have is a hammer then everything looks like a nail.\u201d Well, all we Scripting Guys have at the moment is some code for […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67593","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=67593"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67593\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=67593"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67593"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67593"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}