locked
Syntax Help RRS feed

  • Question

  • My code is hitting the else block even when the workbook name is Sam or Mitchell....did I miss something obvious here?  (and of course my workbook names are not actually wb1, wb2 etc just changed for privacy)

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Threading;
    using System.IO;
    using System.Data.SqlClient;
    using Excel = Microsoft.Office.Interop.Excel;
    using Outlook = Microsoft.Office.Interop.Outlook;
    
    namespace WeeklyDellReports
    {
        class workbookname
        {
            public static Excel.Application oXL;
            public static Excel._Workbook oWB;
            public static string TemplatesLocation = "C:\\TemplateWorkbooks\\";
            public static string SaveLocation = "C:\\Done\\";
            public static string ProductionLocation = "Z:\\LookAtMe\\";
            public static string file1 = "file1.xlsx";
            public static string file2 = "file2.xlsx";
            public static string file3 = "file3.xlsx";
            public static string SupervisorWorkbook1 = "Mitchell.xlsx";
            public static string SupervisorWorkbook2 = "Sam.xlsx";
            public static string[] workbooknames;
            public static DateTime nextMonday = DateTime.Today.AddDays(((int)DateTime.Today.DayOfWeek - (int)DayOfWeek.Monday) + 7);
            public static string SQLConnectionString = "Data Source=Server0;Initial Catalog=DB01;Integrated Security=True;MultipleActiveResultSets=True";
            public static DateTime upcomingMonday = DateTime.Now;
    
            static void Main(string[] args)
            {
                string filename = null;
                string mkdirname = null;
                if (oXL == null)
                { 
                    oXL = new Excel.Application();
                    oXL.Visible = false;
                    oXL.DisplayAlerts = false;
                }
    
                workbooknames = new string[5] { "wb1", "wb2", "wb3", "Mitchell", "Sam"};
                while (upcomingMonday.DayOfWeek != DayOfWeek.Monday)
                {
                    upcomingMonday = upcomingMonday.AddDays(1);
                }
                foreach (string workbookname in workbooknames)
                {
                    switch (workbookname)
                    {
                        case "wb1":
                            filename = file1;
                            mkdirname = "wb1";
                            break;
                        case "wb2":
                            filename = file2;
                            mkdirname = "wb2";
                            break;
                        case "wb3":
                            filename = file3;
                            mkdirname = "wb3";
                            break;
                        case "Mitchell":
                            filename = SupervisorWorkbook1;
                            mkdirname = SaveLocation + upcomingMonday.ToString("MM.dd.yy") + "_SupervisorReports\\";
                            break;
                        case "Sam":
                            filename = SupervisorWorkbook2;
                            mkdirname = SaveLocation + upcomingMonday.ToString("MM.dd.yy") + "_SupervisorReports\\";
                            break;
                    }
                    if (filename != "Sam.xlsx" || filename != "Mitchell.xlsx")
                    {
                        Directory.CreateDirectory(SaveLocation + upcomingMonday.ToString("MM.dd.yy") + "_SupervisorReports");
                        Directory.CreateDirectory(SaveLocation + upcomingMonday.ToString("MM.dd.yy") + "_SupervisorReports\\" + workbookname);
                        Console.WriteLine("Creating Directory For: " + workbookname);
                    }
                    oWB = (Excel._Workbook)oXL.Workbooks.Open(TemplatesLocation + filename);
                    Console.WriteLine("Opening " + workbookname + " Workbook");
                    oWB = oXL.ActiveWorkbook;
                    oWB.RefreshAll();
                    Console.WriteLine("Refreshing Queries For " + workbookname);
                    Thread.Sleep(1000);
                    if (filename == "Sam.xlsx" || filename == "Mitchell.xlsx")
                    {
                        oWB.SaveAs(mkdirname + upcomingMonday.ToString("yyyyMMdd") + "_" + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Saving " + workbookname + " Workbook");
                        File.Exists(ProductionLocation + oXL.ActiveWorkbook.Name);
                        {
                            File.Delete(ProductionLocation + oXL.ActiveWorkbook.Name);
                        }
                        oWB.SaveAs(ProductionLocation + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Copying " + workbookname + " To Production Directory");
                        oWB.Close();
                        oXL.Quit();
                    }
                    else
                    {
                        oWB.SaveAs(SaveLocation + upcomingMonday.ToString("MM.dd.yy") + "_SupervisorReports\\" + mkdirname + "\\" + upcomingMonday.ToString("yyyyMMdd") + "_" + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Saving " + workbookname + " Workbook");
                        oWB.SaveAs(ProductionLocation + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Copying " + workbookname + " To Production Directory");
                        oWB.Close();
                        oXL.Quit();
                    }                
                }
            }
    }
    

    Monday, July 6, 2015 1:38 AM

Answers

  • I suspect that the results are not as they should be because of some other bug. Run the app manually and step through it line by line. If you don't know how to do that, then add some debugging statements that tell you where it's going and run it.

                    Console.WriteLine("filename = '" + filename + "'"; // Note the single quotes, to make sure there are no leading or trailling spaces.
                    if (filename == "Sam.xlsx" || filename == "Mitchell.xlsx")
                    {
                        Console.WriteLine("In the if part.");
                        oWB.SaveAs(mkdirname + upcomingMonday.ToString("yyyyMMdd") + "_" + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Saving " + workbookname + " Workbook");
                        File.Exists(ProductionLocation + oXL.ActiveWorkbook.Name);
                        {
                            File.Delete(ProductionLocation + oXL.ActiveWorkbook.Name);
                        }
                        oWB.SaveAs(ProductionLocation + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Copying " + workbookname + " To Production Directory");
                        oWB.Close();
                        oXL.Quit();
                    }
                    else
                    {
                        Console.WriteLine("In the else part.");
                        oWB.SaveAs(SaveLocation + upcomingMonday.ToString("MM.dd.yy") + "_SupervisorReports\\" + mkdirname + "\\" + upcomingMonday.ToString("yyyyMMdd") + "_" + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Saving " + workbookname + " Workbook");
                        oWB.SaveAs(ProductionLocation + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Copying " + workbookname + " To Production Directory");
                        oWB.Close();
                        oXL.Quit();
                    }   

    • Marked as answer by Kristin Xie Wednesday, July 15, 2015 8:42 AM
    Monday, July 6, 2015 2:25 AM
  • Hi,

    Since you said code hitting the else block even when the workbook name is Sam or Mitchell.

    I've tested your code on my side, change this array only has "Sam" as below.

       workbooknames = new string[1] { "Sam" };

    The following is my debug result

    From my test result, it looks ok, I would suggest you also use debugger and check step by step.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Kristin Xie Tuesday, July 7, 2015 2:54 AM
    • Marked as answer by Kristin Xie Wednesday, July 15, 2015 8:41 AM
    Tuesday, July 7, 2015 2:53 AM

All replies

  • That code appears to go through the correct "if" block, as far as I can tell. What makes you say that it doesn't? Have you checked it under the debugger?
    Monday, July 6, 2015 2:01 AM
  • That code appears to go through the correct "if" block, as far as I can tell. What makes you say that it doesn't? Have you checked it under the debugger?

    It is a console app and I have it scheduled to run via Windows Scheduler at a set time 1x a week.  And when I manually check the output, the results are not as they should be (meaning the syntax is not hitting letting the Sam/Mitchell workbook hit the else block)

    Monday, July 6, 2015 2:07 AM
  • I suspect that the results are not as they should be because of some other bug. Run the app manually and step through it line by line. If you don't know how to do that, then add some debugging statements that tell you where it's going and run it.

                    Console.WriteLine("filename = '" + filename + "'"; // Note the single quotes, to make sure there are no leading or trailling spaces.
                    if (filename == "Sam.xlsx" || filename == "Mitchell.xlsx")
                    {
                        Console.WriteLine("In the if part.");
                        oWB.SaveAs(mkdirname + upcomingMonday.ToString("yyyyMMdd") + "_" + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Saving " + workbookname + " Workbook");
                        File.Exists(ProductionLocation + oXL.ActiveWorkbook.Name);
                        {
                            File.Delete(ProductionLocation + oXL.ActiveWorkbook.Name);
                        }
                        oWB.SaveAs(ProductionLocation + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Copying " + workbookname + " To Production Directory");
                        oWB.Close();
                        oXL.Quit();
                    }
                    else
                    {
                        Console.WriteLine("In the else part.");
                        oWB.SaveAs(SaveLocation + upcomingMonday.ToString("MM.dd.yy") + "_SupervisorReports\\" + mkdirname + "\\" + upcomingMonday.ToString("yyyyMMdd") + "_" + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Saving " + workbookname + " Workbook");
                        oWB.SaveAs(ProductionLocation + oXL.ActiveWorkbook.Name);
                        Console.WriteLine("Copying " + workbookname + " To Production Directory");
                        oWB.Close();
                        oXL.Quit();
                    }   

    • Marked as answer by Kristin Xie Wednesday, July 15, 2015 8:42 AM
    Monday, July 6, 2015 2:25 AM
  • Did you check the case sensitive? in C# "sam" is not as "Sam".

    Fouad Roumieh

    Monday, July 6, 2015 6:39 AM
  • Check the case of file names

    if (filename.ToLower() != "sam.xlsx" || filename.ToLower() != "mitchell.xlsx")
    


    Muthukrishnan Ramasamy
    net4.rmkrishnan.net
    Use only what you need, Reduce global warming

    Monday, July 6, 2015 10:14 AM
  • Hi,

    Since you said code hitting the else block even when the workbook name is Sam or Mitchell.

    I've tested your code on my side, change this array only has "Sam" as below.

       workbooknames = new string[1] { "Sam" };

    The following is my debug result

    From my test result, it looks ok, I would suggest you also use debugger and check step by step.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Kristin Xie Tuesday, July 7, 2015 2:54 AM
    • Marked as answer by Kristin Xie Wednesday, July 15, 2015 8:41 AM
    Tuesday, July 7, 2015 2:53 AM