Import data between excel files

**losmi8** · 09-14-2010, 09:55 AM

Hi all. I need some help importing data from one excel file into another. Id like to use VBA but I dont even know where to start. Ive googled for hours and couldnt find a simple, logical way to import cells from one excel file into the other. Any help would be appreciated! Both excel files will never change their name nor will the cells i need from source ever change. Both files are on my desktop as well. So i just need a simple way to transfer some data over.

**JBeaucaire** · 09-14-2010, 10:06 AM

If the filenames and cells needed never change, why not just link the two files with dynamic formulas?

In book2 a formula like this would bring over the contents of cell A1 from sheet1 of Book1:

='[Book1.xls]Sheet1'!A1

**MarvinP** · 09-14-2010, 10:06 AM

Excel will allow you to open both files at the same time. You can then use the windows clipboad to copy and paste from one to the other. After opening both files you can view both side by side to see things. Look at the View tab.
If you know all this and it is much more involved you will need to supply a file(s) showing what you need.

**losmi8** · 09-14-2010, 02:43 PM

The dynamic thing works well and it is overly simple! However, I made a mistake..the cells do change. Columns dont. So I need vba to go down a column and find the last filled cell and then take that value into a new excel file. Sorry about the confusion. And thank you for your help!

**JBeaucaire** · 09-14-2010, 04:40 PM

This is a formula that returns the last text value in a column of values, you can't list the whole column, but you can list a range large enough for your purposes.

=LOOKUP(2, 1/($H$1:$H$200>""), $H$1:$H$200)

Here's the same formula looking in a different workbook, still doing the same thing:

=LOOKUP(2, 1/('[Book1.xls]Sheet1'!$H$1:$H$200>""), '[Book1.xls]Sheet1'!$H$1:$H$200)

So, this is still doable without VBA.

Here's the formula version for numeric values:

=LOOKUP(9.9E+307,$H$1:$H$200)
=LOOKUP(9.9E+307,'[Book1.xls]Sheet1'!$H$1:$H$200)

**losmi8** · 09-15-2010, 01:08 PM

JB, that works like a charm! I need exactly that, to look for the last value in a specified range in one of the columns! What you gave me is exactly what I need, however...is there any way to do this in VBA? Because Id like to manipulate the values further and add some of my VBA code to this. And when VBA pulls these values, it will store them in the workbook as just values, correct?

If the code is too complicated/complex, then Ill just stick with what you've provided me. Thanks again!

**losmi8** · 09-15-2010, 02:59 PM

For instance, I ran into a problem. The formula you gave me works, but now I need to look for the last numberic value in column K, but I dont want that value transfered into the new sheet....I need the cell to the right of it. In VBA, Id do the .Offset option and I could get it to work.

Can anyone re-write this in VBA? Hopefully it will be simple and easy enough to understand and work with.

**losmi8** · 09-15-2010, 03:32 PM

Actually I figured it out. What I am stuck on is telling my VBA code to find the last value in a specific range. I know how to find the last used cell in a column, but I need to tell it to find last value in a specific range...then I can run a loop and do other stuff.

Any help would be greatly appreciated!

Ps. I will post the code later, I can not post it from my work computer. I am on a phone right now