M
Math
Advice please!
We have a file held on a groupdrive that's accessible to all via our
portal. On that workbook I want to add a sheet to show data pulled
from a sheet in another workbook that's on a different server. That
other workbook is refreshed daily, and so the new sheet needs to be
easy to refresh. Furthermore, the import should only pull a few of
the fields and needs to filter out certain records.
Initially I thought about macroing to open the other workbook, copying
the entire worksheet, and pasting into the first before chopping and
filtering down the data. That should be fairly easy, even though I'm
always terrible at identifying the data (i.e. making a recorded macro
useful on different files of different sizes).
Then I thought that the Import External Data tools should be able to
do it, and reduce down the data at the same time.
Certainly, it proved easy to import the entire sheet. But then I've
come a cropper trying to figure out how to query that import down.
Any pointers to useful threads/pages (i have looked), or other advice,
please?
Just to go through what I've seen so far...
- on a blank worksheet, I went to Data > Import External Data > Import
Data
- In the resulting 'Select Data Source' diag, I've entered the
location to the file (
http://jhpn3/minisite/portal/docs/Data Report.xls).
- Clicked on open
- Select Table diag opens, listing all the worksheet titles (twice for
some reason, the second versions with a _ at the end).
- I select the table I want (List1$)
- IMport Data diag opens - select existing sheet and cell A1
- in Properties, I change the title to plain 'Data Report'
- Edit Query leaves me stuck. As it is, the command text just holds
the table name (List1$)
- OK (and OK again) runs the import and imports the worksheet.
So how do I change it to reduce down the data? I presume i should be
able to put some SQL in the command text, but it always produces an
error.
I don't have admin access and can't add data sources. When I tried
recording my actions, the macro pointed at my temp folder - I need the
import to be available to everybody. Groupdrives don't always use the
same letter, so I can't use them.
So help please
Thankyou
We have a file held on a groupdrive that's accessible to all via our
portal. On that workbook I want to add a sheet to show data pulled
from a sheet in another workbook that's on a different server. That
other workbook is refreshed daily, and so the new sheet needs to be
easy to refresh. Furthermore, the import should only pull a few of
the fields and needs to filter out certain records.
Initially I thought about macroing to open the other workbook, copying
the entire worksheet, and pasting into the first before chopping and
filtering down the data. That should be fairly easy, even though I'm
always terrible at identifying the data (i.e. making a recorded macro
useful on different files of different sizes).
Then I thought that the Import External Data tools should be able to
do it, and reduce down the data at the same time.
Certainly, it proved easy to import the entire sheet. But then I've
come a cropper trying to figure out how to query that import down.
Any pointers to useful threads/pages (i have looked), or other advice,
please?
Just to go through what I've seen so far...
- on a blank worksheet, I went to Data > Import External Data > Import
Data
- In the resulting 'Select Data Source' diag, I've entered the
location to the file (
http://jhpn3/minisite/portal/docs/Data Report.xls).
- Clicked on open
- Select Table diag opens, listing all the worksheet titles (twice for
some reason, the second versions with a _ at the end).
- I select the table I want (List1$)
- IMport Data diag opens - select existing sheet and cell A1
- in Properties, I change the title to plain 'Data Report'
- Edit Query leaves me stuck. As it is, the command text just holds
the table name (List1$)
- OK (and OK again) runs the import and imports the worksheet.
So how do I change it to reduce down the data? I presume i should be
able to put some SQL in the command text, but it always produces an
error.
I don't have admin access and can't add data sources. When I tried
recording my actions, the macro pointed at my temp folder - I need the
import to be available to everybody. Groupdrives don't always use the
same letter, so I can't use them.
So help please
Thankyou