Macro: Importing data from another xls document

[heyviggy]

I am trying to write VBA-code to import data from another document, but I have very limited experience with VBA so I wonder if anyone could help me. What I want to do is somethink like this:

Function ImportDataFromCell(x)
ImportDataFromCell ='C:\project\data\[x.xls]sheet1'!$A$1
Function end

..so the value/name from cell A1 in document x.xls is returned in my spreadsheet. x refers to a cell in my spreadsheet with the name of the file in the datafolder.

[arlu1201]

You will need to explain some more.

Why dont you attach a sample file?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[heyviggy]

Example added

[arlu1201]

So the files listed in column A will be present in 1 folder in your system?

The macro needs to open each file and populate the 3 fields onto your sheet?

[heyviggy]

Yes. If you look at the attached file, the main.xlsx would be located at C:\project\ while all the data files will be at C:\project\data\. The names in column A is the filename of the files listed in C:\project\data\. Hence if it was only one file I could simply write

='C:\project\data\[test.xls]sheet1'!$A$1
='C:\project\data\[test2.xls]sheet1'!$A$1
='C:\project\data\[test3.xls]sheet1'!$A$1

in the cells respectively. The problem is that I've got hundreds of files in C:\project\data\ and I need 10-15 cells transported from each document, hence a function would ease my work a lot :\

[arlu1201]

You actually do not need to write it as ='C:\project\data\[test3.xls]sheet1'!$A$1 in the sheet to have the value populate. You can have a macro that opens each file and then copies the values into the main file.

[heyviggy]

Yes, that is more or less what I am trying to do But I need a function, as I have to add IFs for some of the cells. Maybe I wasn't precise enough in my post, sorry about that!

[Anthem12]

Hi there!

I am trying to accomplish a similar task. I currently have 1 extract that 6 different departments are using to add data and then create pivot tables/charts. I would like to just take the 6 departments files and copy the relevant values into the main extract so that I can create all pivots and dashboards at once. My goal is to create one data source and then use power pivot for charts, etc.

Would the code above apply in this scenario. Can you post just a general vba script that I can use for this purpose?

Thanks!

[arlu1201]

What do you need the if's for?

[heyviggy]

Arlette, at some of the cells I only want to include the number if it is positive, otherwise zero. Other places I want to categorize strings by numbers, i.e. 1 to 4, as I have a final number of alternatives apparent.

Hence a function would be something like this:

Function ImportDataFromCell(x) 
IF a condition THEN
ImportDataFromCell ='C:\project\data\[x.xls]sheet1'!$A$1     [<--this doesnt work and is my problem. I can't get the x, which is the name of the file i.e. name.xlsx, to work in a proper way]
ELSE
ImportDataFromCell = 0
END IF
Function End

Any idea on how I can do this?

[arlu1201]

Anthem12,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[arlu1201]

If i understand your code correctly, it means that if a condition is met, only then you want the values to populate in the columns, else - dont populate?

[heyviggy]

Yes! Any ideas on how to handle this?

[arlu1201]

Can you tell me which are the values from each file that need to be shown in this master file?

What is the condition to be checked?

[heyviggy]

I upload a second description, that might be easier to understand. If I can handle the location address in the function itself, more or less any conditions can easily be added (I think)

The problem is to take the filename as an input in a function and use it in the location address for the file.