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?
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?