Importing just raw data from another workbook (no table-formatting!)

d0rian

Active Member
Joined
May 30, 2015
Messages
313
Office Version
  1. 365
I have a table of data in File_1.xlsm that I want to export into into File_2 via a data connection, since I update the data in File_1 often and want to import it into File_2 at the click of a button.

I've tried every permutation I can think of in the Data Connection dialog, but I can't get it to just pull in the raw values. It will only import the data as a TABLE, with a header row (with filter arrow!) and a bunch of unwanted table formatting. I just want the values, no header row, no filter arrows, no formatting. The 1st pic below is File_1 (source file). 2nd pic is File_2 (destination file, with the ugly formatting I can't seem to avoid.)

Feels like it shouldn't be this hard, but I'm striking out. Any suggestions? FWIW
- File_1 has to be a .xlsm file, it can't be a CSV, otherwise I'd know how to do this.
- I thought about just using formulas in File_2 to refer to the static cells in File_1 but I really want to avoid any external file linkages between workbooks in the formulas themselves; they've caused headaches in the past.

9HlVPcF.jpg


OVh4Osf.jpg
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
your source file uses unnecessary formatting "align to Center"
result file is a proper clean format (maybe except date which is a number)
filter arrows you can turn off in Data - Filter

anyway you can use PowerQuery (Get&Transform) where you can define how often you want to refresh
result will be as Table
 
Last edited:
Upvote 0
Sorry I should have specified Excel 2007...from my search it appears PowerQuery / Get and Transform aren't available for 2007.

Yes, my source file has stuff aligned-to-center, but I can obviously remove that, since the source file need not be pretty...even if I could just prevent Excel from adding that F1/F2/F3...etc header row I'd be happy. Not sure why it's Data Connection feature insists on creating and adding it to the destination file...but I don't think I can avoid it.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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