How Can I Copy Data From One Spreadsheet to Another?

How Can I Copy Data From One Spreadsheet to Another?

  • Comments 1
  • Likes
Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I copy data from one Excel spreadsheet to another Excel spreadsheet?

-- AC

SpacerHey, Scripting Guy! AnswerScript Center

Hey, AC. You know, we’re 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’s because he arrived home late last night (well after midnight) following a flight from Rome to Seattle. (A flight, in case you’re wondering, that takes just a shade under forever to complete.) And yet, despite being tired from a full day of travel, and despite the effects of jet lag, he still got up at 6:00 this morning and came into work. Why did he come in to work rather than take one more day off? Because he’s a dedicated Microsoft employee who absolutely loves his job, that’s why.

Well, that and the fact that he’s trying to stockpile enough vacation days to enable him to take the entire month of December off. But no one needs to know about that, least of all his manager or anyone he works with.

Actually, today’s article should be an interesting one. After all, the Scripting Guy who writes this column often wanders through the hallways of Microsoft, telling anyone who has the misfortune to run into him, “A script that can copy data from one Excel spreadsheet to another? Why, I could write a script like that in my sleep!”

So can he really write a script like that in his sleep? We’re about to find out:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("c:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets("Sheet1")

Set objRange = objWorksheet.Range("A1:A20")
objRange.Copy

Set objExcel2 = CreateObject("Excel.Application")
objExcel2.Visible = True

Set objWorkbook2 = objExcel2.Workbooks.Add
Set objWorksheet2 = objWorkbook2.Worksheets("Sheet1")

objWorksheet2.Paste

OK, not too bad; now let’s see if he can explain 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 object and then sets the Visible property to True; that gives us a running instance of Microsoft Excel that we can see onscreen. We next use the Open 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:

Set objWorksheet = objWorkbook.Worksheets("Sheet1")

That was pretty easy, wasn’t it?

Let’s see, what’s 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 method. That’s what these two lines of code are for:

Set objRange = objWorksheet.Range("A1:A20")
objRange.Copy

As you can see, in line one we create an instance of Excel’s Range 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.

You know what? That was pretty easy, too.

But wait; we’re not done yet. After all, we still have to paste this data into a second spreadsheet. For our sample script, we’re 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 Visible property to True:

Set objExcel2 = CreateObject("Excel.Application")
objExcel2.Visible = True

Déjà vu, huh?

After creating our second instance of Excel, we use the Add 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:

Set objWorksheet2 = objWorkbook2.Worksheets("Sheet1")

At this point all we need to do is call the Paste method and, by extension, call it a day:

objWorksheet2.Paste

Good observation: Paste is actually a method that belongs to the Worksheet 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’s a line of code that makes cell D5 the active cell on our worksheet:

objWorksheet2.Range("D5").Activate

Now when we call the Paste method the data will be pasted into cell D5 (and any adjoining cells).

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’d 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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • ScriptingGuy,

    Your articles are very helpful... thanks for posting them. I am kind of stuck though and I am hoping that, even though it is several years down the road, you might be able to help.

    I am trying to do what you explained above (which sadly I hadn't found this article when I started it) but it is acting strangely. The copy and paste works fine but the column widths are the defaults. If I try to change them it still doesn't match to what my template is. The code I used for that is below.

    t_cols = db.Worksheets(ws).Range(db.Worksheets(ws).PageSetup.PrintArea).Columns;

    rng = s.Range(t_rng.Address).Columns;

    for(var n=1; n<=t_cols.count; n++)

    {

    rng.item(n).EntireColumn.ColumnWidth = t_cols.item(n).ColumnWidth;

    }

    It is also doing the same thing with borders... Is there a way that I can fix this (preferably when copying)? The only thing I can think of that is different between your article and what I've done is that they are in the same Excel.Application. Would that matter?

    Thanks,

    -Matt