c# – Using Access to Export in Excel, Parameterized Query

Question:

I'm using Access database, I need to export a query to excel.

My problem is the following:

I'm using a parameterized query in Access for the access to export to excel.

When I make the call to export, it opens the screen to type the parameter. I would like to pass this parameter via code)

 private void btnExportarParaExcel_Click(object sender, EventArgs e)
    {
        saveFileDialog1.Title = "Salvar Arquivo";
        saveFileDialog1.Filter = "Excel|.xlsx";
        saveFileDialog1.FilterIndex = 0;
        saveFileDialog1.FileName = "Match_" + DateTime.Now.ToString("ddMMyyyy");
        saveFileDialog1.DefaultExt = ".xlsx";
        saveFileDialog1.InitialDirectory = @frmPrincipal.caminhoDoSistema;
        saveFileDialog1.RestoreDirectory = true;

        DialogResult resultado = saveFileDialog1.ShowDialog();

        if (resultado == DialogResult.OK)
        {
            String mesAno = frmPrincipal.mesAno;
            mesAno = mesAno.Substring(3, 4) + mesAno.Substring(0, 2);
            var accessApp = new Access.Application();
            try
            {
                accessApp.OpenCurrentDatabase(@frmPrincipal.caminhoDoSistema + @"\Database\" + mesAno + @"\prjRecon.accdb", false);
                accessApp.DoCmd.SetParameter("companhia", frmPrincipal.companhia);
                accessApp.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport, Access.AcSpreadSheetType.acSpreadsheetTypeExcel12Xml, "Match_LancamentosContabeis2", @saveFileDialog1.FileName);
                MessageBox.Show("Arquivo salvo com sucesso!", "Sucesso", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (COMException ex)
            {

                MessageBox.Show("Erro:" + "\n" + ex.ToString());
            }
            accessApp.CloseCurrentDatabase();
            accessApp.Quit();
        }
        else
        {
            MessageBox.Show("Operação cancelada");
        }
    }

Answer:

The most practical way would be to use temporary variables.

Try this:

  1. Assuming that your query uses something similar to the image you sent. Change the [What Date? (dd/mm/yy)] for [TempVars]![MeuCriterio]
  2. In your code include the following line TempVars.Add "MeuCriterio", valor_do_seu_criterio

This way, you can pass the criteria via code to the query and you won't have to fill the window during runtime.

Scroll to Top