Any way to extract a range of cells from a closed workbook without using a named range? RRS feed

  • Question

  • Hello,

    I'm trying to use Power Query to pull out a (non-named) range of cells from Sheet1 in all of the workbooks in a given folder.

    For the sake of argument, let's say the range is Sheet1!$I$13:$L$19 in each of these workbooks.

    Is there any way of doing this without the range being a named range?

    The code I've created only works where the range is named, and is:-

    let    Source = Folder.Files("Q:\DTL\PRIVAT\Measuring ICT\ICT Database\1 UNCTAD questionnaire\2018\DEPLOYMENT VERSION\DEPLOY\Final test"),    #"Lowercased Text" = Table.TransformColumns(Source,{{"Extension", Text.Lower, type text}}),    #"Filtered Rows" = Table.SelectRows(#"Lowercased Text", each Text.Contains([Extension], "xls")),    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "GetExcelObjects", each Excel.Workbook([Content],true)),    #"Expanded GetExcelObjects" = Table.ExpandTableColumn(#"Added Custom", "GetExcelObjects", {"Name", "Data"}, {"Name.1", "Data"}),    #"Filtered Rows1" = Table.SelectRows(#"Expanded GetExcelObjects", each [Name.1] = "chrisusagesize" or [Name.1] = "chrisentsize"),    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Data", {"Indicators (in total numbers)", "n1", "n2", "n3", "Total", "0-9 ", "10-49 ", "50-249 ", "250+ ", "ICTVar", "A-IctUseItem_dic_Code", "Source", "Unit", "Country Code", "Economy", "h", "Reference Year", "Questionnaire Year"}, {"Indicators (in total numbers)", "n1", "n2", "n3", "Total", "0-9 ", "10-49 ", "50-249 ", "250+ ", "ICTVar", "A-IctUseItem_dic_Code", "Source", "Unit", "Country Code", "Economy", "h", "Reference Year", "Questionnaire Year"}),    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Data", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Name.1", "Indicators (in total numbers)", "n1", "n2", "n3", "ICTVar", "A-IctUseItem_dic_Code", "Source", "Unit", "Country Code", "Economy", "h", "Reference Year", "Questionnaire Year"}, "Attribute", "Value"),    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "EntSize"}}),    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"h"})in    #"Removed Columns"

    Many thanks,


    Thursday, June 28, 2018 1:37 PM