really<\/I> write a script like that in his sleep? We\u2019re about to find out:<\/P>Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\nSet objWorkbook = objExcel.Workbooks.Open(“c:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(“Sheet1”)<\/p>\n
Set objRange = objWorksheet.Range(“A1:A20”)\nobjRange.Copy<\/p>\n
Set objExcel2 = CreateObject(“Excel.Application”)\nobjExcel2.Visible = True<\/p>\n
Set objWorkbook2 = objExcel2.Workbooks.Add\nSet objWorksheet2 = objWorkbook2.Worksheets(“Sheet1”)<\/p>\n
objWorksheet2.Paste\n<\/PRE>\n
OK, not too bad; now let\u2019s see if he can explain<\/I> a script that can copy data from one Excel spreadsheet to another in his sleep. To begin with, the script creates an instance of the Excel.Application<\/B> object and then sets the Visible<\/B> property to True; that gives us a running instance of Microsoft Excel that we can see onscreen. We next use the Open<\/B> method to open the file C:\\Scripts\\Test.xls, then use the following line of code to bind us to the first worksheet in the file:<\/P>Set objWorksheet = objWorkbook.Worksheets(“Sheet1”)\n<\/PRE>\nThat was pretty easy, wasn\u2019t it?<\/P>\n
Let\u2019s see, what\u2019s next? Well, in order to copy data to the Clipboard, we need to first specify the range of data to be copied (that is, the cells we want to copy), then call the Copy<\/B> method. That\u2019s what these two lines of code are for:<\/P>Set objRange = objWorksheet.Range(“A1:A20”)\nobjRange.Copy\n<\/PRE>\nAs you can see, in line one we create an instance of Excel\u2019s Range<\/B> object, in this case a Range object encompassing the cells A1 through A20 (although, needless to say, this could be any range of data we want). In line 2, we call the Copy method, a command that copies cells A1 through A20 (all the cells in our range) to the Clipboard.<\/P>\n
You know what? That was pretty easy, too. <\/P>\n
But wait; we\u2019re not done yet. After all, we still have to paste this data into a second spreadsheet. For our sample script, we\u2019re going to paste this information into a brand-new spreadsheet; however, we could also open and paste the data into another pre-existing spreadsheet if we so desired. With that in mind, we thus create a new instance of Microsoft Excel, giving this instance the object reference objExcel2 and setting its<\/I> Visible property to True:<\/P>Set objExcel2 = CreateObject(“Excel.Application”)\nobjExcel2.Visible = True\n<\/PRE>\nD\u00e9j\u00e0 vu, huh?<\/P>\n
After creating our second instance of Excel, we use the Add<\/B> method to add a new, blank workbook to objExcel2, then use this line of code to bind to the first worksheet in that new workbook:<\/P>Set objWorksheet2 = objWorkbook2.Worksheets(“Sheet1”)\n<\/PRE>\nAt this point all we need to do is call the Paste<\/B> method and, by extension, call it a day:<\/P>objWorksheet2.Paste\n<\/PRE>\nGood observation: Paste<\/B> is actually a method that belongs to the Worksheet<\/B> object (unlike the Copy method, which belongs to the Range object). By default, Excel will paste the data into cell A1 (and adjoining cells as needed). What if we want to paste this data somewhere else? Well, the only way to do that is to make a cell other than cell A1 the active cell. For example, here\u2019s a line of code that makes cell D5 the active cell on our worksheet:<\/P>objWorksheet2.Range(“D5”).Activate\n<\/PRE>\nNow when we call the Paste method the data will be pasted into cell D5 (and any adjoining cells).<\/P>\n
By the way, thanks in advance for not telling anyone how tired the Scripting Guy who writes this column really is. The fact of the matter is that if anyone knew the truth, they\u2019d make him go home and, in turn, use up one of his precious vacation days. As it is, right now the Scripting Guy who writes this column is wandering around in a daze, eyes half-closed, and muttering incoherently to himself. Which, considering the fact that this is pretty standard behavior for him anyway, means that no one suspects a thing.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I copy data from one Excel spreadsheet to another Excel spreadsheet?— AC Hey, AC. You know, we\u2019re going to let you in on a little secret here: the Scripting Guy who writes this column is a tad bit tired this morning. That\u2019s because he arrived home late last night (well […]<\/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":[710,48,49,3,5],"class_list":["post-64183","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I copy data from one Excel spreadsheet to another Excel spreadsheet?— AC Hey, AC. You know, we\u2019re going to let you in on a little secret here: the Scripting Guy who writes this column is a tad bit tired this morning. That\u2019s because he arrived home late last night (well […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64183","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=64183"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64183\/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=64183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \n