Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-27-2020, 05:52 AM
SoMany SoMany is offline How do you import data from another spread sheet? Windows 7 64bit How do you import data from another spread sheet? Office 2016
Advanced Beginner
How do you import data from another spread sheet?
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 08-27-2020, 06:19 AM
SoMany SoMany is offline How do you import data from another spread sheet? Windows 7 64bit How do you import data from another spread sheet? Office 2016
Advanced Beginner
How do you import data from another spread sheet?
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default

Quote:
Originally Posted by SoMany View Post
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.
Actually, All I really need to know is how to make an import button that copies the whole sheet from another file next to the sheet I'm working with.
Reply With Quote
  #3  
Old 08-27-2020, 07:56 AM
Purfleet Purfleet is offline How do you import data from another spread sheet? Windows 10 How do you import data from another spread sheet? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

what is the file type of the data dump? xls/xlsx/txt/csv?
Reply With Quote
  #4  
Old 08-27-2020, 09:40 AM
Purfleet Purfleet is offline How do you import data from another spread sheet? Windows 10 How do you import data from another spread sheet? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #5  
Old 08-27-2020, 03:51 PM
jeffreybrown jeffreybrown is offline How do you import data from another spread sheet? Windows 10 How do you import data from another spread sheet? Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
  #6  
Old 08-30-2020, 06:33 AM
SoMany SoMany is offline How do you import data from another spread sheet? Windows 7 64bit How do you import data from another spread sheet? Office 2016
Advanced Beginner
How do you import data from another spread sheet?
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
what is the file type of the data dump? xls/xlsx/txt/csv?
The format is .xls

Quote:
Originally Posted by jeffreybrown View Post
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
Before learning about Power Query, it should be noted that when I go to Data-->Get Data-->From Workbook ... The message I get is "External table is not in the expected format."

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.
Reply With Quote
  #7  
Old 08-30-2020, 07:57 AM
jeffreybrown jeffreybrown is offline How do you import data from another spread sheet? Windows 10 How do you import data from another spread sheet? Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Maybe you can attach a copy (desensitized) of the worksheet you are trying to import.
Reply With Quote
  #8  
Old 08-30-2020, 01:31 PM
Purfleet Purfleet is offline How do you import data from another spread sheet? Windows 10 How do you import data from another spread sheet? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Attached Files
File Type: xlsm ImportMacro.xlsm (19.0 KB, 7 views)

Last edited by Purfleet; 08-30-2020 at 01:31 PM. Reason: typo
Reply With Quote
  #9  
Old 08-31-2020, 01:04 AM
ArviLaanemets ArviLaanemets is offline How do you import data from another spread sheet? Windows 8 How do you import data from another spread sheet? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
  #10  
Old 02-04-2021, 08:09 AM
matt_henry matt_henry is offline How do you import data from another spread sheet? Windows 8 How do you import data from another spread sheet? Office 2010
Novice
 
Join Date: Feb 2021
Posts: 4
matt_henry is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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
How do you import data from another spread sheet? Data from a specific row in a spread sheet into a form JohnnySTI Excel Programming 13 01-24-2018 01:20 PM
How do you import data from another spread sheet? Simple Spread Sheet TThomas Excel 1 04-07-2016 09:01 PM
How do you import data from another spread sheet? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft