#1
|
|||
|
|||
How do you import data from another spread sheet?
For work, we have all the data from one of our programs spit out into an excel spreed sheet. I need to know how get data from a specific spreed sheet, always with the same name in the same location, from specific cells.
The path seems to be through using the data TAB, but all of the explanations online are giving this head scratching feeling. |
#2
|
|||
|
|||
Quote:
|
#3
|
|||
|
|||
what is the file type of the data dump? xls/xlsx/txt/csv?
|
#4
|
|||
|
|||
Thinking about it, if you just want the data to show in another workbook and the location & file name of the the data dump are static you could just import in power query and then refresh each day.
You might find that power query can do some of the other stuff you do with that file as well |
#5
|
|||
|
|||
As has been already pointed out, the best method is to setup a connection thru power query.
Import the First Sheet in a Workbook with Power Query - Excelerator BI |
#6
|
|||
|
|||
The format is .xls
Quote:
Edit: All I need is to be able to copy past the entire first sheet of this spreadsheet into the second sheet of the file I'll be using to interpret this data. |
#7
|
|||
|
|||
Maybe you can attach a copy (desensitized) of the worksheet you are trying to import.
|
#8
|
|||
|
|||
I dont think xls Files work with power query.
if the file can be extracted as txt, csv or xlsx power query would be perfect. Otherwise i think you would have to go down the macro route. A Macro could be as simple or as complex as you want to make it. To just open a spreadsheet and copy 1 sheet to an other work The attached is very much a starter - you can add the file name & location etc on the data worksheet and then import. It is very basic and doesnt check for the file or anything else it just imports the sheet. But it should give you a pointer Last edited by Purfleet; 08-30-2020 at 01:31 PM. Reason: typo |
#9
|
|||
|
|||
You can use a simple ODBC Query instead Power Query.
1. In source workbook, data to import must be organized as table (i.e. it must have a single header row with unique headers for every column in table range, and there must not be any gaps in table range). NB! I'm speaking about simple table here, not about Defined Table! 2. You define the table in source workbook as static Named Range (like $A$1:$Z$1000). In case there will be added new entries in future, you may define Named Range with surplus of empty rows at bottom. 3. In your target workbook, on separate sheet you create an ODBC query (Data > From Other Sources > From Microsoft Query > Excel Files) with Named Range in source workbook as data source. Set the query to be refreshed on open. You'll get a current copy of table in source workbook anytime you open your working workbook. There may be some issues - probably when you have 64-bit windows with 32-bit office. At least we have this setup, and I had to create Open Event for Excel apps, which use ODBC queries to get data from other Excel files. The event edits the Datasource and Querystring every time when file is opened (i.e. simply overwrites them). Without this the query returns an error. |
#10
|
|||
|
|||
If you are familiar with the Connections feature in Excel, you can import another worksheet data into current workbook, and your imported data will be updated with the original data automatically.
Go to click Data > Connections In the Workbook Connections dialog, click Add button, and in the popped out Existing Connections dialog, click Browse for More button,\ And then choose the workbook that you want to export its data to current worksheet. After choosing the workbook, please click Open button, and select one worksheet that you want to use. In the Existing Connections dialog box, click the workbook that you are added just now, and click Open button. And in the popped out Import Data dialog, choose one option you like from the Select how you want to view this data in your workbook, and then choose one the existing worksheet range or a new worksheet to put the data. Then click OK, the data from another worksheet has been imported into your specified worksheet I hope these steps will be helpful! Matt Henry |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I am trying to remove the $- from the cells with no data on a large spread sheet | gch777 | Excel | 3 | 07-05-2018 09:42 AM |
Data from a specific row in a spread sheet into a form | JohnnySTI | Excel Programming | 13 | 01-24-2018 01:20 PM |
Simple Spread Sheet | TThomas | Excel | 1 | 04-07-2016 09:01 PM |
Using combobox to pass data from an Excel spread sheet to text boxes on userform | Stewie840 | Word VBA | 14 | 12-17-2015 10:13 PM |
Condensing a spread sheet | hawkeyefxr | Excel | 4 | 08-22-2012 05:17 AM |