Run Macro in remote file without opening. Import sub-set of data into Dashboard

[mond007]

Hi
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am looking to run a macro in a remote file across a network WITHOUT OPENING THE FILE which will create a sub-set of the data (say employee) eventually importing into a Reporting Hub. This will be from multiple data sources, basically the dashboard would be will be too large to keep importing all data and then filtering and subsequently only showing the limited employee data, if you see what I mean.
<o></o>
i.e. KPI_Data_Source.xls has 60,000 rows (across 20 columns) of data for many employees.
This file has a macro (exportdata) that will filter the correct employee and export the subs-set of 500 rows of data to a KPI Exported Data.xls file.
<o></o>
KPI Dashboard Reporting Hub.xls has nothing more than a results worksheet which imports the small file and NOT the large 60,000 row data file.
<o></o>
I want to be able to have the Dashboard import say only one employees data of 500 rows at any one time.
So, the intention is I select the employee in the dashboard and run a macro in the large data file KPI_Data_Source.xls which creates a KPI Exported Data.xls with only 500 rows and then import those into the dashboard for display (effectively a pseudo drill down).
<o></o>
THE PROBLEM : I have coded up the solution thinking the exportdata macro will simply be run using the following :
In the KPI Dashboard Reporting Hub.xls<o></o>

Application.Run “KPI_Data_Source.xls!exportdata”

<o></o>
BUT the source data file is opened when I run the remote macro code. ULIMATELY this means dragging the large data file across the network. Kind of self defeating really. L
<o></o>
Any way round this ? or any ideas ?
Many thanks in advance for your input. Kuldip

 

[ChrisM]

My main thought is, you are attempting to code a full-fledged database system via Excel. If you want database-like performance, you should use a database. Of course, many people use Excel as a database, and it's fine with small amounts of data. But 60,000 rows is starting to push it. What's more, your attempt to pull out just one employee's data to your dashboard is almost the exact definition of a database query.

Here's how I would do it:

1. Your main Excel data file gets turned into an Access database on your network. One database, one table that looks exactly like your 60,000 rows (same column headers, etc).

2. Open up your Excel dashboard. Using the "Data" ribbon in XL2007, create a "From other Sources/From MS Query" query. Point the query to your new table in your database. MS Queries allow you to use parameters pulled from an Excel cell. So say cell A1 holds employee name. Your query would be like "Select All From MyNewTable Where EmployeeName = A1"

3. Now anytime you change the employee name, just right click and hit 'refresh' query and you will get the new data.

This is obviously very high level, if you are interested post back and we can go step by step. But this is not hard, this can be done in less than 10 minutes.

If you don't have Access available, you can actually use MS Query to query another Excel workbook. That would be my second choice. The workbook data needs to be in a nice clean tabular format. Setting up MS Query is just about the same.

 

[mond007]

Hi Chris, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Sorry, I should have said, I actually thought of the Access idea but don't have it available, or rather and more to the point the audiance don't have access. <o></o>
<o></o>
Your later ideas sounds and great but I am not familier with MS Query to accquire data from another workbook. I guess I could use application.run to prepare the data for a specific employee say and then use MS Query to get the data. <o></o>
<o></o>
Another little side note is that I am eventually going to be placing the result workbook into Livelink, a Sharepoint type application across the intranet, don't think this will have any bearing though. <o></o>
<o></o>
How would you use the MS Query to get data from another workbook ? any instructions would be great.<o></o>
<o></o>
Thanks in advance. Kuldip<o></o>

 

[ChrisM]

Just to clarify on Access, your end users wouldn't need a copy of Access, only Excel. Only the person maintaining the 60,000 rows would need Access.

Anyways, to use Excel as a datasource, you need to create a clean data sheet in a workbook. Start on row 1, and set it up just like a database table. Row 1 contains unique headers, then data starts on row 2 through 60,000. Now the key part, you must give this table a named range. Highlight the whole table and name it whatever you want, 'MyData' or something.

Now close this workbook and open a second workbook. This second book is for your end users. Go to the Data section of the ribbon, and choose "From Other Sources / From MS Query". Choose the Excel file option, then navigate to your file. You should see your named range, MyData, etc. Select it, then follow the query wizard prompts. Go ahead and set a filter on employee name, yes it will only return one record but that's ok, just pick one name. Now, once you pass the filter screen it takes you to sort options, at this point hit cancel and it will prompt you to open in MS Query. Choose yes.

Now you are in an Access-like query editor. You will see your name criteria field. Replace the name you hardcoded with a bracketed dummy variable, like [myName]. Using brackets treats this field as a parameter. The query will ask you for this value every time it runs.

Hit the big X in top right corner (not the smaller x) and the query will run and ask you for a name. Manually type in one name you know will work. Now review your query output in your worksheet. Look ok?

Last step, tell Excel to use a worksheet cell for the criteria. To do this, you need to edit the connection. Go to Data / Connections, then choose the connection file you just created. Hit properties. Then the Definition tab, then click the parameters button, pretty straightforward from there.

This is a lot easier than it sounds. Would take 1 minute to show you in person.

 

[mond007]

Thanks Chris, I will have a go....

In the mean time i am also working on another simular related problem:

http://www.mrexcel.com/forum/showthread.php?p=2696574#post2696574

Thanks in advance.