C# Export to Excel Help

[Fyfey96]

Hello I am trying to export data to an excel file with a variable file name it works when i set the path in the code but when i insert variables i get the issue.  Here is my code..

 

 public void export_run()
        {
            
                Excel.Application xlApp = new
                    Microsoft.Office.Interop.Excel.Application();

                if (xlApp == null)
                {
                    MessageBox.Show("Excel is not properly installed!!");
                    return;
                }
                string export_time1 = DateTime.Now.ToString("dd-MM-yyyy");
                string export_time2 = DateTime.Now.ToString("hh:mm:ss");
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                xlWorkSheet.Cells[1, 4] = "Exported - " + export_time1 + " @ " + export_time2;
                xlWorkSheet.Cells[1, 1] = "Cans Produced";
                xlWorkSheet.Cells[1, 2] = cont_prod_old;
                xlWorkSheet.Cells[2, 1] = "Cans Rejected";
                xlWorkSheet.Cells[2, 2] = cont_rej_old;
                xlWorkSheet.Cells[3, 1] = "Cans Underfilled";
                xlWorkSheet.Cells[3, 2] = cont_und_old;
                               
                string desktop = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
                string path = System.IO.Path.Combine(desktop, "HeuftDataExport" + export_time2 + export_time1 + ".xls")
                                
                xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);

                MessageBox.Show("Excel file created , you can find the file on your desktop", "Export Sucessfull");
           
}

I have tried creating the "HeuftDataExport + export times in its own variable but this does not work either. The  error is "System.Runtime.InteropServices.COMException"

any help would be appreciated. 

Thanks.

[Nuluvius]

What's the version of Excel that you are attempting to automate and what automation interface reference are you using?

[Fyfey96]

2 hours ago, Nuluvius said:

What's the version of Excel that you are attempting to automate and what automation interface reference are you using?

Im using c# in visual studio

[Nuluvius]

Just now, Fyfey96 said:

Im using c# in visual studio

I have no words for you...

[Fyfey96]

3 minutes ago, Nuluvius said:

I have no words for you...

whats up? Should i be using something different?

[Nuluvius]

1 minute ago, Fyfey96 said:

whats up?

It's obvious that you are using C# and VS... A chimp could see that.

 

I asked you about the specifics of what you were trying to automate - you do know that what you are trying to do is called automation right?

[Fyfey96]

3 minutes ago, Nuluvius said:

It's obvious that you are using C# and VS... A chimp could see that.

 

I asked you about the specifics of what you were trying to automate - you do know that what you are trying to do is called automation right?

Yes, I dont really think excel version matters because the export works. when this line...

 string path = System.IO.Path.Combine(desktop, "HeuftDataExport.xls")

Is like that it works but when like this...

 string path = System.IO.Path.Combine(desktop, "HeuftDataExport" + export_time2 + export_time1 + ".xls")

Then i get the error on this line.

 xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

 Sorry if i was unclear.

[Nuluvius]

7 minutes ago, Fyfey96 said:

Yes, I dont really think excel version matters because the export works. when this line...

Yes the version matters because it determines the shape of the interface that has been registered...

 

That aside the problem seems to be fairly simple and unrelated. Inspect those strings that you are using as components of the filename and you'll likely see it for yourself...

[Fyfey96]

2 minutes ago, Nuluvius said:

Yes the version matters because it determines the shape of the interface that has been registered...

 

That aside the problem seems to be fairly simple and unrelated. Inspect those strings that you are using as components of the filename and you'll likely see it for yourself...

If i use console write line to show the variable path before the crash. it is the same as what it would be if i had typed it in.

[Nuluvius]

4 minutes ago, Fyfey96 said:

If i use console write line to show the variable path before the crash. it is the same as what it would be if i had typed it in.

You should be using breakpoints and inspecting the variables but anyway have a look at the result of that Path.Combine function:

HeuftDataExport05:32:2925-11-2016.xls

Now have a think about valid Windows filenames....

[Fyfey96]

2 minutes ago, Nuluvius said:

You should be using breakpoints and inspecting the variables but anyway have a look at the result of that Path.Combine function:

HeuftDataExport05:32:2925-11-2016.xls

Now have a think about valid Windows filenames....

Wow how did i not realize this haha! the " : "  cant be in it.?

[Nuluvius]

2 minutes ago, Fyfey96 said:

Wow how did i not realize this haha! the " : "  cant be in it.?

 It's surprising how one may get blindsided sometimes.

 

Also you should consider using implicit types:

var someVariable = new SomeType();

And string interpolation:

var someString = $"something combined with {someVariable} and {someOtherVarialbe}";

 

[Fyfey96]

2 minutes ago, Nuluvius said:

 It's surprising how one may get blindsided sometimes.

 

Also you should consider using implicit types:

var someVariable = new SomeType();

And string interpolation:

var someString = $"something combined with {someVariable} and {someOtherVarialbe}";

 

Thanks!  Yea im new too all this. I keep seeing that done need to use it myself Thanks!

[Nuluvius]

7 minutes ago, Fyfey96 said:

Thanks!  Yea im new too all this. I keep seeing that done need to use it myself Thanks!

No problem, good luck with it.

 

Remember that automation is awful at the best of times, especially so with MS Office. If you want to break the dependency on the presence of Excel on the target machine then there's at least one library that I know of that works directly with the .xls/.xlsx zip file - it's not free though. Otherwise one is left with writing their own mechanism for manipulating its contents, not difficult by any measure but quite a time sink.