Programmatically export query table from MS Access to Excel using C#

Question

Today i have a software which downloads xml data from the web and exports it to a MS Access DB in appropriate tables.

In MS Access DB i have created a query using the tables to make columns and rows as i want it to look like in Excel.

When i right-click on my new query table, and chose Export to Excel, i'm able to create an Excel file from that query.

Basically what i want to do is extend my software so that i can export the query to Excel programmatically with C#.

How can i do this?

---------------------------

other side things related i also would like to solve.

I'm getting green triangles above the numbers on left side, check the image postimg.org/image/t6tvfw2cz how can i remove from c#.

Is it possible to format the table look and design with c# code?

Is it poosible to add filters to the headers with c# code? – Mana 15 hours ago

I suggest creating new questions to ask your follow-ups. You can make links between the related questions if you want. — Gabe
– Gabe, Commented Dec 11, 2013 at 8:04

JMK · Accepted Answer · 2013-12-10 14:32:37Z

6

Something like this should do it:

private static void ExportQuery(string databaseLocation, string queryNameToExport, string locationToExportTo)
{
    var application = new Application();
    application.OpenCurrentDatabase(databaseLocation);
    application.DoCmd.TransferSpreadsheet(AcDataTransferType.acExport, AcSpreadSheetType.acSpreadsheetTypeExcel12,
                                          queryNameToExport, locationToExportTo, true);
    application.CloseCurrentDatabase();
    application.Quit();
    Marshal.ReleaseComObject(application);
}

You would call it like this:

ExportQuery(@"C:\blah\blah.accdb", "myQuery", @"C:\blah\blah.xlsx");

Be sure to add these using statements:

using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Access;

answered Dec 10, 2013 at 14:32

JMK

28.2k56 gold badges176 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mana Over a year ago

im missing an assembly reference to Microsoft.Office.Interop.Access. where can i find it?

Mana Over a year ago

Oki, so i found it. Testing your solution now.

Mana Over a year ago

Hi, i have now implemented and tested some, but am getting the following error, "Excel cannot open the file [filename] because the file format or file extension is not valid". The excel file is generated, but i cant open the excel file.

JMK Over a year ago

@Mana The above will be different based you your version of Excel, you need to play about with the file extension and AcSpreadSheetType enumeration until it works.

JMK Over a year ago

Good stuff, the documentation for the TransferSpreadsheet method is here, it describes the different parameters and how they affect the output.

|

Brendan · Accepted Answer · 2013-12-10 15:16:31Z

0

Another solution is to create a CSV file which can be opened in Excel or other applications. See this function which returns a StringBuilder. This can then be saved as a file and opened via code as such:-

  string _filename = "a path" + "name.csv";
  File.WriteAllText(_filename, TheReturnedStringBuilder.ToString());
  System.Diagnostics.Process.Start(_filename);

The CSV creation

    /// <summary>
    /// Nvest Development Solutions
    /// Export the SQL data into a comma separated file
    /// </summary>
    /// <param name="ConnectionString">Valid SQL Server connection string</param>
    /// <param name="Sql">A valid SQL statement</param>
    /// <param name="TimeOut">A timeout specified in seconds</param>
    /// <returns>A stringbuilder with comma separated data</returns>
    public static StringBuilder ExportToCSVFormat(string ConnectionString, string Sql, int TimeOut = 30)
    {
        StringBuilder csv = new StringBuilder();
        string s = "";
        DataTable dt = SQL.BuildTableFromSQL(ConnectionString, Sql, TimeOut);

        //Add the titles
        foreach (DataColumn col in dt.Columns)
        {
            s += "\"" + col.ColumnName + "\", ";
        }
        if (s.Length > 1)
        {
            s = s.Remove(s.Length - 2);
        }
        csv.AppendLine(s);

        //Add the data
        foreach (DataRow row in dt.Rows)
        {
            object[] param = new object[dt.Columns.Count];
            int j = 0;
            s = "";

            foreach (DataColumn col in dt.Columns)
            {
                s += "{" + j + "";

                if (col.DataType == typeof(int) || col.DataType == typeof(long) || col.DataType == typeof(double))
                {
                    s += ":0},";
                }
                else
                {
                    s += ":},";
                }

                param[j] = row[col.ToString()];

                j++;
            }

            csv.AppendLine(string.Format(s, param));
        }

        return csv;
    }
}

answered Dec 10, 2013 at 15:16

Brendan

1,2451 gold badge16 silver badges34 bronze badges

2 Comments

Brendan Over a year ago

I must have misunderstood as I though you wanted C# code to output data to Excel which is what the above does. Never mind though - hope you find your solution.

Mana Over a year ago

Your solution is not wrong, and it would probably work, its just that, I would then have to re-program the whole software so that it would export the data to .csv file rather then to ms access DB. Other reason why your solution is not relevant is because there are lots of tables in access which i'm using to create query tables in access. And it is these query tables that i want out in Excel. It would be meaningless to start re-program all this to use .csv

Collectives™ on Stack Overflow

Programmatically export query table from MS Access to Excel using C#

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related