Datagridview to Excel.....Automate Reporting with Timer

[PDTUM]

Good Afternoon, So, I am using this very nice code to generate Excel spreadsheets from my Datagridview, and I could not be more pleased:

private void CreateExcelReport()
{
Microsoft.Office.Interop.Excel.Workbook wBook;
Microsoft.Office.Interop.Excel.Worksheet wSheet;
Microsoft.Office.Interop.Excel.Application wApp = new Microsoft.Office.Interop.Excel.Application();
wApp.Visible = false;
wBook = wApp.Workbooks.Add(true);
wSheet = wApp.ActiveSheet;
try
{
for (int i = 0; i < dataGridViewReport.Columns.Count; i++)
{
wSheet.Cells[1, i + 1] = dataGridViewReport.Columns[i].HeaderText;
}

            for (int i = 0; i < dataGridViewReport.Rows.Count; i++)
            {
                DataGridViewRow row = dataGridViewReport.Rows\[i\];
                for (int j = 0; j < row.Cells.Count; j++)
                {
                    DataGridViewCell cell = row.Cells\[j\];
                    try
                    {
                        wSheet.Cells\[i + 2, j + 1\] = (cell.Value == null) ? "" : cell.Value.ToString();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error : " + ex.Message);
                    }
                }
            }
            wApp.Visible = true;
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error : " + ex.Message);
        }
        wApp.UserControl = true;    
    }

Now I would like to have the application run in the background and generate these reports automatically and put them in a folder (anyplace) perhaps by using the timer. I have created the timer part and can call a named file from a database and launch it, however, I do not know where to begin adding onto the Excel code to do it automatically. I need to be able name the file in the spreadsheet, point this script at a location where it can be saved and found later and and have it run automatically as long as the app is running. A piece of code here (perhaps a sample) would be much appreciated. Thank You....Pat

[Dalek Dave]

You could autoname the file using the date/time function.

------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave CCC Link[^] Trolls[^]

[PDTUM]

Thank you, Dave....yes, I have used that to actually create the name by creating an addendum with the date/time, but what I am really looking for is some code that, after the timer calls the method and the Excel sheet is generated by the Excel application, saves the file in a specific location as you would manually from the SaveDialog box, but instead it would do it automatically. The purpose of this method is to run predetermined (by time) SQL Select statements that are retrieved from a database and then store the results in a file so they can be examined at a later time. I am having a problem explaining it here..I am sorry...:-). Thank you again for your response. BTW...I see you are British...my new neighbors are from England. He is here (U.S., California) as an Engineer to work with the Air Force on the Harrier Jet at Edwards AFB. They are a real "kick"...way too funny:-). Thanks Again, Pat

[Mycroft Holmes]

Having read your reply to DD I wonder why you are using a DGV at all. I would do the following Go into Excel and record a macro that saves your workbook to a folder. Take the macro and rewrite it into c# code. This will be the save method, consider that you will pass in a workbook and a filename to the method. Use a timer with a tick method set to the interval you need Your form starts the timer and has a listbox of the selected queries and the pass/fail of each process and a textbox with the destination folder. In the tick event you: Stop the timer Get the datatable from SQL instead of looping the columns/rows in the DGV loop the Rows/Columns of the datatable

for (int i = 0; i < dataGridViewReport.Columns.Count; i++)

use

for (int i = 0; i < Datatable.Columns.Count; i++)

Call your save method you have reconstructed from the excel macro start the timer. Your exception handling needs to deal with failed methods and write to the listbox data store and restart the timer. In various places you need to log results to the listbox data store

Never underestimate the power of human stupidity RAH