none
Error querying on folder of Excel workbooks RRS feed

  • Question

  • In Excel 2016, in a new workbook, when I query on a folder of Excel workbooks, and when done in the Query Editor I choose Close & Load, I get the error:

    "This query didn't return any columns Microsoft Excel can use."

    Then I find that it created 3 worksheets, two of which have different tables, and the other one just has "ExternalData_3" in cell A1.

    The first of those 3 worksheets has the data I want. And refreshing it works fine, with no error.

    In the Queries pane, I count 5 items in 3 folders, and one of the items, "Sample File", says "! Download did not complete".

    Holy hodgepodge, Batman!

    Am I doing something wrong to get that error, or is it expected? It happens even if I keep the query as simple as possible; just select the folder and load to Excel.

    The data in the files is simple test data. Three text fieldnames in cells A1:C1, and RAND() numbers, converted to values, in the next several rows below them (the RAND() formulas are all gone; replaced by the values they returned; there are no formulas at all in the workbooks in the folder I'm querying on).

    Am I doing something wrong to get those extra two worksheets? One of them has the table for the Sample Data. I understand that the query needs it, but I don't want that returned to Excel. And what about the sheet that just gets the error?

    Am I doing something wrong to get the "! Download did not complete" error on the "Sample File" query?

    Again, one of the queries returned is exactly what I expected, and there appears to be no error on that one. And it refreshes fine. And that query seems to be the first query it returns, as it is on the first worksheet it creates. So what's going on with all this other stuff?

    Repro steps:

    • In Excel 2016, create a new workbook.
    • Enter "Field1", "Field2", and "Field3" in cells A1:C1.
    • Select cells A2:C10.
    • Enter the formula =RAND(). Ctrl+Enter to enter it in all the cells.
    • Copy/Paste Special > Values to convert the formulas to values.
    • Save the workbook to a new folder.
    • Reselect cells A2:C10. Repeat the steps to enter the RAND() formula and then convert to values again. The values should all be different than before. Save that as another workbook in the same folder as the first workbook. Close the workbook.
    • Create a new workbook, to do the query in.
    • Select Data tab > Get & Transform Data section > Get Data > From File > From Folder. That brings up the Folder dialog.
    • Click Browse and browse to the folder you created. Click OK.
    • A dialog appears that has all the files in that workbook listed. At the bottom of that dialog, select Combine > Combine & Edit. That brings up the Combine Files dialog.
    • Select the sheet name and click OK. The Query Editor appears.
    • On the Queries window pane on the left, I count 10 items in 5 folders. But the last one shows all the expected data, from both workbooks.
    • To keep this sample simple, lets not do any transformations of our own here. Instead let's just return the data to Excel by selecting Home tab > Close section > Close & Load. Result is as described above.
    Friday, May 12, 2017 10:52 PM

Answers

  • Hi,

    Thanks for posting, I'm working on a fix so you don't have to worry about this in the future. In the meantime, I'd suggest choosing "close and load/load to" instead of "close and edit." This will do the combine, and load only the main query. From there, you can open the editor and make any changes as needed (and you won't see the load error when closing).

    You're right that there's nothing wrong with your queries (the sample file one will always result in an error), we prevent loading the helper queries in most cases but will have to address this one that's currently missed.

    Thanks,

    Zach

    Monday, May 22, 2017 6:25 PM