Pulling in data from a closed XLS file (not CSV) without all the formatting? Just the plain data?

d0rian

Active Member
Joined
May 30, 2015
Messages
313
Office Version
  1. 365
I've learned how to pull data from a closed CSV file using the import-as-text function (specifying "comma" as the delimiter), but am having harder time doing do the same when the source file is a proper Excel .xls file. What i do is: Data...Connections...Add...Browse For More...[select the source XLS file, e.g. source.xls]...which brings up a "Select Table" dialog. Here's where I get lost. Am I not able to specify the specific cells/range that I want to import from the source file?


It appears all I can do is select the entire worksheet (named "Data"): e.g. displayed as "Data$" in the "Select Table" dialog box...or I can select a named range in the source file, as they appear to be listed there too (as just their names, e.g. "data_range")

Anyway, after I select one of the lines in that box, I go to Existing Connections...then click on the Connection I just created, then select "Table" from the "Import Data" dialogue box, but regardless of how I've proceeded to this point, what populates is a formatted table that looks like the image below. I don't want the formatting or the dropdowns...I just want the values. But I didn't see anywhere during the setup process I could specify that. Anyway, 2 questions:

1) Can I drill down to specify just what cells/range from the source file I want this connection to link to? e.g. let's say I want to paste columns C:G from the source file to columns A:E in Sheet1 of the destination file...i'm not sure how I was supposed to set that up(?)
2) Can I get ONLY the values, none of this table/formatted business?

kusyvwK.jpg
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top