Using Access to export in Excel, Parameterized Query

Asked

Viewed 253 times

3

I am using the database Access, need to export a query to excel.

My problem is this::

I am using a parameterized query in Access for the access itself to export to excel.

When I make the export call, it opens the screen to enter the parameter. I wanted to pass this parameter by 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");
        }
    }
  • Screen to enter which parameter?

  • For example that screen: http://assets.gcflearnfree.org/topics/177/x_parameter_whatdatemmddyy.png

  • Cool screen. But where it fits in your example?

  • accessApp.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport, Access.AcSpreadSheetType.acSpreadsheetTypeExcel12Xml, "Match_LancamentosContabeis2", @saveFileDialog1.FileName); When I pass this command, to export the query pro excel, the screen appears to insert the parameter.

1 answer

1

The most practical way would be to use temporary variables.

Try the following:

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

This way, you can pass the criterion via code for the query and do not have to fill in the window during the execution time.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.