xlsx generated by ToSpreadsheet() isn't accepted by MS Access

edited January 6

I want to expose the result of a query to Access. This query dumps several tables.

The most straightforward way seems to be to do something like
Result1.ToSpreadsheet("Result 1").AddSheet(Result2, "Result 2").Save(@"C:\Users\me\Desktop\output.xlsx");
and then add this as an external data source in Access.

But upon importing this into access, it says The wizard is unable to access information in the file 'C:\Users\me\Desktop\output.xlsx'. Please check that the file exists and is in the correct format.
If I open this file in Excel, save and close, then the import works. (FWIW, Excel doesn't report any errors that I can see)

Looking into the structure of the xlsx, a bunch more stuff is added when I save it in excel. Maybe the one generated by Linqpad is too minimal and missing some files?

BTW, if you have any better idea for how to accomplish an import into Access, I'm open to that as well.

Host runtime version: 8.0.0
Default query runtime version: 8.0.0
Default query reference assembly version: 8.0.0
Roslyn Version: 4.8.0-7.23558.1
FSharp.Compiler.Service version: 43.7.400.0
NuGet client version:
Results rendering engine: Edge Chromium Engine 120.0.2210.91 (WebView2)


  • edited January 6

    The issue could also be with the ability of Access to read .xlsx files, especially with the various features that LINQPad enables by default. You could try specifying WorksheetOptions and disabling AutoFilter, FreezeTopRow and GenerateTable. Another option could be to use CSV format instead of .xlsx, using Util.WriteCsv.

  • I tried disabling the options but it didn't make a difference.

    Here's a minimal example, and the xlsx it produces, as well as opening and saving with Excel. As you can see the Excel'd version is bigger. I tried transplanting a few files from the Excel'd version to the original, but I haven't hit the magic combo yet.

    <Query Kind="Statements">
    Enumerable.Range(1, 10).ToSpreadsheet("My Sheet", options: new WorksheetOptions()
        GenerateTable = false,
        AutoFilter = false,
        FreezeTopRow = false,
        WrapMultilineCells = false
    }).Save(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Query7 - From LINQPad.xlsx"));
Sign In or Register to comment.