Posts by Duki

As easy as 1 -> 2 -> or 3

i am putting all of this in caps because usually that helps people understand...

LOL
(i put that in caps, to help you better understand)

And sure enough - click me

:)

Try this

have you tried..

How are you filling the data grid? Are you doing a SELECT * or some other query?

Hey everyone,

I have an application I'm writing (that a few of you have given me a good bit of help on) that will be used to check out assets and check them back in. It's working pretty great for items that are checked out *today* but I need a way to schedule them in advance.

So for example, I need to be able to open the app, schedule Asset-A to be checked out for 2 days a week from now, and still allow that Asset-A to be checked out and checked in between now and the scheduled date a week from *today*.

Here's something like I want

Load App
  |
  |
  Schedule Asset-A for 11/1/2010 through 11/3/2010
    |
    |
  Between today and 11/1/2010, allow Asset-A to be checked out
    |
    |
  On 11/1/2010, remove Asset-A from Available Assets list

Does anyone have an idea of an easy way to do this? Right now, I have a dbTable for each type of asset. One way I thought of was to create a scheduling table to keep track of scheduled assets and then query that database every time the app loads. But I'm still trying to figure out how to check for future scheduling conflicts (i.e., If Asset-A is scheduled for 11/1/2010 through 11/3/2010, do not allow someone to later try to schedule it for 10/30/2010 through 11/2/2010).

If anyone has a good idea of how to do this, I would be very thankful. I'm kind …

Thanks for the input guys - been driving me crazy.

nick> I only have (it seems) one EXCEL.EXE after each run of the program - sometimes two since I'm actually modifying two separate files. Also, I added the Marshal.Release shortly after I posted this code - didn't seem to help much.

Ryshad> I had no clue about the tmp files - I'll give it a whirl. Thanks

I'll mark it as solved :)
Thanks for the help!

I just tried calling, and the phone number isn't working anyways so no worries! :)

Another piece to the puzzle - I'm getting a lot of tmp files (attachment). I've also noticed that any time the program errors out (and sometimes when it closes fine), I still have numerous instances of EXCEL.EXE in task manager. What am I doing wrong?

Yeah - that's what ShowDialog(); does. Have you tried it? If you don't trust me (and everyone else), do

this.enabled = false;

if it makes you feel any better.

Look here.

More specifically, this post.

Have a look at this thread. That should help.

............

Shucks - so the continue doesn't work there?

Go it working - for completeness, here's my code as of now. If you see any errors, let me know. Thanks for the help!

public void CreateSheet(Laptop lptp, string file, int id)
{
    for (loop = 0; loop < MAX_TRIES; loop++)
    {
        try
        {
            subLog.LogMessageToFile("Opening sheet in " + file + " for asset id: " + id);

            //Open specified file and set activate worksheed according to asset ID
            xlBook = xl.Workbooks.Open(file_path + file, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
            xlSheet = xlBook.Worksheets.get_Item(2);

            subLog.LogMessageToFile("Creating sheet in " + file + " for asset id: " + id);

            //Create a copy of sheet #2 at the end of the workbook
            int last = xlBook.Worksheets.Count;
            xlSheet.Copy(oMissing, xl.Worksheets[last]);
            xl.Worksheets[last + 1].name = id.ToString();     //rename to corresponding asset ID

            xlSheet = xlBook.Worksheets.get_Item(last + 1);
            xlSheet.Cells[2, 1] = lptp.laptop_ID;
            xlSheet.Cells[2, 2] = lptp.pc_name;
            xlSheet.Cells[2, 3] = lptp.manufacturer;
            xlSheet.Cells[2, 4] = lptp.model;
            xlSheet.Cells[2, 5] = lptp.location;

            subLog.LogMessageToFile("Saving file: " + file);
            xlBook.Save();

            break;
        }
        catch (Exception ex)
        {
            Thread.Sleep(3000);

            subLog.LogMessageToFile("Create Error - Source: " + ex.Source);
            subLog.LogMessageToFile("Create Error Message: " + ex.Message);                    
            continue;
        }
        finally
        {
            subLog.LogMessageToFile("Closing session: " + file);

            xl.Application.Workbooks.Close();
            xl.Workbooks.Close();
            xl.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xl);
        }
    }

    if (loop == MAX_TRIES)
    {
        subLog.LogMessageToFile("Closing session: " + file);
        subLog.LogMessageToFile("Error Occured in session: " + file);

        MessageBox.Show("Create Error - Check log file for details", "Could not create sheet", MessageBoxButtons.OK, MessageBoxIcon.Error);

        xl.Application.Workbooks.Close();
        xl.Workbooks.Close();
        xl.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xl);
    }

}

Ahh -

continue;

is something to never be forgotten, but I did :(. Thanks!

How about something like

int loop;
for (loop=0; loop < MAX_TRIES; loop++)
{
    try
    {
        // do stuff
    }
    catch (Exception ex)
    {
        // report waiting
        Thread.Sleep(3000);
    }
    finally
    {
        if ( loop != MAX_TRIES )
        {
            //do stuff
            break;
        }    
        else
        {
            //do stuff
        }
    }
}

See any err?

If an exception hits, my finally-stuff block won't execute if it's not part of the try-catch-finally block will it?

edit> just saw your post

I'm pretty sure it's one based - according to some posts I saw on msdn sites.

Thanks for the help - I'll give that a shot.

Edit: P.S. xlSheet = xlBook.Worksheets.get_Item(2); //<-- will fail if item 2 does not exist!

I know - sheet 2 is a template used for creating new sheets. It should always exist. Thanks again

Hey everyone,

I have a warning message in my app saying "Unreachable code detected" - Here's a snippet:

//Files are sometimes not closed quickly enough - these loop variables allow for the system to wait and try again
        const int MAX_TRIES = 3;
        int loop = 0;

...


public void CreateSheet(Laptop lptp, string file, int id)
{
    PointOfRetry:
    try
    {
        subLog.LogMessageToFile("Opening sheet in " + file + " for asset id: " + id);

        //Open specified file and set activate worksheed according to asset ID
        xlBook = xl.Workbooks.Open(file_path + file, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
        xlSheet = xlBook.Worksheets.get_Item(2);

        subLog.LogMessageToFile("Creating sheet in " + file + " for asset id: " + id);

        //Create a copy of sheet #2 at the end of the workbook
        int last = xlBook.Worksheets.Count;
        xlSheet.Copy(oMissing, xl.Worksheets[last]);
        xl.Worksheets[last+1].name = id.ToString();     //rename to corresponding asset ID

        xlSheet = xlBook.Worksheets.get_Item(last + 1);
        xlSheet.Cells[2, 1] = lptp.laptop_ID;
        xlSheet.Cells[2, 2] = lptp.pc_name;
        xlSheet.Cells[2, 3] = lptp.manufacturer;
        xlSheet.Cells[2, 4] = lptp.model;
        xlSheet.Cells[2, 5] = lptp.location;

        subLog.LogMessageToFile("Saving file: " + file);
        xlBook.Save();
    }
    catch (Exception ex)
    {
        for (loop = 0; loop < MAX_TRIES; loop++) ////  << loop -> "Unreachable code detected"
        {
            MessageBox.Show("File Locked - Waiting for access...");
            Thread.Sleep(3000);
            goto PointOfRetry;
        }

        subLog.LogMessageToFile("Create Error - Source: " + ex.Source);
        subLog.LogMessageToFile("Create Error Message: " + ex.Message);
        MessageBox.Show("Create Error: " + ex.Message, "Could not create sheet", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        subLog.LogMessageToFile("Closing session: " + file);

        xl.Application.Workbooks.Close();
        xl.Workbooks.Close();
        xl.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xl);
    } …

Ok, so my problem seems to be occurring only when I have an exception thrown. But I would think the finally{...} block would still take care of that. Still working on a solution...

Thanks for any help

Try something along these lines:

this.Hide();
Form_Login form_login = new Form_Login();
form_login.Owner = this;
form_login.Show();

Figured it out - here's an excerpt from my code:

class ExcelWriter
{
private object oMissing = System.Reflection.Missing.Value;
private Excel.Application xl = new Excel.Application();
private Excel.Workbook xlBook;
private Excel.Worksheet xlSheet;

private string file_path = "C:\\Users\\ctote.REG\\Documents\\";

public void CreateSheet(string file, string sheet)
{
     try
     {
         xlBook = xl.Workbooks.Open(file_path + file, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
         xlSheet = xlBook.Worksheets.get_Item(2);
               
         int last = xlBook.Worksheets.Count;
         xlSheet.Copy(oMissing, xl.Worksheets[last]);
         xl.Worksheets[last+1].name = sheet;

         xlBook.Save();
    }
    catch (Exception ex)
    {
         MessageBox.Show("Create Error:  " + ex.Message, "Could not create sheet", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
         xl.Application.Workbooks.Close();
         xl.Workbooks.Close();
         xl.Quit();
         System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
         System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
         System.Runtime.InteropServices.Marshal.ReleaseComObject(xl);
     }
}
...

Hey everyone,

I'm having an issue where my application will keep Excel running even after it closes. I think I'm closing everything properly, but is there a way to check on close?

Here's part of my code:

////////////// Part of ExcelWriter class
public void CreateSheet(Laptop lptp, string file, string sheet)
        {
            try
            {
                xlBook = xl.Workbooks.Open(file_path + file, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                xlSheet = xlBook.Worksheets.get_Item(1);
                xl.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);
                xl.Worksheets[1].name = sheet;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Create Error:  " + ex.Message, "Could not create sheet", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                xlBook.Save();
                xl.Application.Workbooks.Close();
                xl.Workbooks.Close();
            }
        }

/////////////// Part of ExcelWriter class.
public void UpdateFile(Laptop lptp, string file)
        {
            try
            {
                xlBook = xl.Workbooks.Open(file_path + file, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                xlSheet = xlBook.Worksheets.get_Item(1);
                xl.ActiveSheet.Name = "List";
                int num_rows = xlSheet.Rows.Count;
                xlSheet.Cells[1, num_rows+1] = "Test";
            }
            catch (Exception ex)
            {
                MessageBox.Show("Write Error:  " + ex.Message, "Could not write to file", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                xlBook.Save();
                xl.Application.Workbooks.Close();
                xl.Workbooks.Close();
            }
        }

Found an answer to the sorting question - seems a little overboard, but it works.

How can I specify which sheet I want to write to by name? For example, I want to specify that the sheet I should be modifying should be labeled "212".

Also, perhaps a more challenging question. How can I ensure that the sheets I create are always in alphabetical order? Is that a property I can set through excel maybe?

Is there a way to get an array of items that aren't of the same datatype?

maybe this will help - i have:

this.vehiclesTableAdapter.GetDataByID(ID_Number);

but i cant save it to an array

Hey everyone,

For logging purposes, I want to pass an entire row of my DB to a file. Is there an easy way to store a row of items into an array or something similar that I could pass to a function for parsing?

e.g., array = SELECT * FROM table WHERE id=001
Then pass array to some function so that it can write the items to a log file.

Ok it's strange. My app works fine as long as no exceptions are thown - but then it locks the file permanently.

my code:

public void create(string file, string sheet)
        {
            try
            {
                xlBook = xl.Workbooks.Open(file_path + file, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                xlSheet = xlBook.Worksheets.get_Item(1);
                //xlSheet.Name = sheet;
                xl.Worksheets.Add(oMissing, oMissing, oMissing, oMissing);
                xl.Worksheets[1].name = sheet;
                xlBook.Save();
            }
            catch (Exception)
            {
                MessageBox.Show("Create Error");
            }
            finally
            {
                xl.Application.Workbooks.Close();
                xl.Workbooks.Close();
            }
        }

If for the name I put an invalid value, "205:", it throws the exception, then locks my file forever and ever (until I log out, etc.).

Great thanks - would this be causing the locked files?

Hey everyone,

I have an excel file I'm trying to work with. I can open it, write to it, and save it fine. But now I'm trying to work with adding sheets, and modifying the names of those sheets. For some reason though, my app keeps locking my excel files. I've had to create about 5 test.xlsx files because all of the other test files are permanently read-only (until I log off) for some reason. Here's my code - it looks like I should be closing the files after each run, no matter what, but for whatever reason I'm still getting locked messages.

object oMissing = System.Reflection.Missing.Value;
            Excel.Application xl = new Excel.Application();
            Excel.Workbook xlBook;
            Excel.Worksheet xlSheet;
            string laPath = "C:\\Users\\ctote.REG\\Documents\\My Dropbox\\test3.xlsx";

            try
            {
                xlBook = xl.Workbooks.Open(laPath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                xlSheet = xlBook.Worksheets.get_Item(1);
                xlSheet.Name = "108";
                xl.Worksheets.Add();
                xl.Worksheets[0].name = "Testing";
                xlBook.Save();
            }
            catch (Exception)
            {
                MessageBox.Show("locked");
            }
            finally
            {
                xl.Application.Workbooks.Close();
                xl.Workbooks.Close();
            }

Can someone tell me what I'm doing wrong?

I'll start a new thread since my first question was solved.

Ok, now I'm getting locked errors. Every file I open gets permanently locked (when I try to open it, it says "Read Only"). Here's my code:

object oMissing = System.Reflection.Missing.Value;
            Excel.Application xl = new Excel.Application();
            Excel.Workbook xlBook;
            Excel.Worksheet xlSheet;
            string laPath = "C:\\Users\\ctote.REG\\Documents\\My Dropbox\\test3.xlsx";

            try
            {
                xlBook = xl.Workbooks.Open(laPath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                xlSheet = xlBook.Worksheets.get_Item(1);
                xlSheet.Name = "108";
                xl.Worksheets.Add();
                xl.Worksheets[0].name = "Testing";
                xlBook.Save();
            }
            catch (Exception)
            {
                MessageBox.Show("locked");
            }
            finally
            {
                xl.Application.Workbooks.Close();
                xl.Workbooks.Close();
            }

Another question though - How do I (add a new / change the name of) a sheet?

cout << "Nick saves the day again." << endl ;

Thank you!!

Thanks for the suggestion. Unfortunately, excelApp.Sheets[] isn't a modifier of sorts. I couldn't find anything else on the page you provided.

Hey everyone,

Does anyone know how to specify which sheet you're going to be modifying in an exel worksheet? For example, if I have an Excel file that has 5 sheets, and I want to modify (4,7) on sheet 3, how would I do that? Here's what I have so far:

Excel.Application excelApp = new Excel.Application();
string myPath = "C:\\Users\\ctote.REG\\Documents\\My Dropbox\\test.xlsx";
excelApp.Workbooks.Open(myPath);

excelApp.Cells[3,3] = "TESTING INFO FROM MY AWESOME APP LOL";
excelApp.Visible = true;

This works, but it modifies sheet1 only.

Thanks for the input. I figured if I was going to add that much code anyways, I would just create a new database query to get the data I needed from the DB instead of the textbox.

int start = Convert.ToInt32(this.vehiclesTableAdapter.QueryStartingMiles(Vehicle_ID));

Thanks for the help.

Hey everyone,

I have a label that gets data from a database and uses that as the .Text field. I want to compare this .Text field with an integer value, but I can't convert the text. The value I'm trying to convert to integer is "25,000". Here's what I've tried:

Int32.TryParse(label_Start_Miles1.Text, System.Globalization.NumberStyles.Integer, null, out start);

Convert.ToInt32(label_Start_Miles1.Text);

Int32.Parse(label_Start_Miles1.Text, out start);

All of them blow up during run, except tryparse which just doesn't work.

Nevermind, I figured it out. For anyone else who's wondering, it's under InstallShield Section #2 (in visual studio) called "Redistributables"

Hey everyone,

I'm not sure where I should be posting this - if a mod finds a more appropriate form, feel free to move it.

I'm trying to figure out how to not only require .Net 4.0 with my installation package, but in the case 4.0 isn't installed already have it either install it through my deployment, or have the client machine point to the internet to download it. I've searched Google for a while, but I can't figure it out. If anyone has experience with InstallShield, can you please give me a hand? Thanks

Does the combo box contain data from your database? In other words, did you manually put the selections in your combo box or are they from the same database?

As far as I can tell, it happens randomly after running the program. I tried locking the datagridview - in designer it still resets to show all fields, but when I run it, only the fields I selected show. very strange.

Hey everyone,

I've attached a screenshot of what I'm experiencing. I've selected the fields I want to view in my DataGridView several times, but (seemingly with no pattern) my application will reset the DataGridView to show every field in my database.

Attached is a screenshot of what fields I want, and what it looks like after the grid is "reset".

Awesome. Thanks! What happens if you += more than once? Will it throw it x-times every time?

Another quick question in regards to this. Here's my code:

private void textBox_End_Miles_TextChanged(object sender, EventArgs e)
        {
            int End_Miles_Value;
            int Start_Miles_Value;

            //Checks for valid data within label_Start_Miles1.Text and textBox_End_Miles.Text
            //If values are valid (numerical) and if Ending Miles is more than Starting Miles, update the Miles_Drove label
            if (!Int32.TryParse(label_Start_Miles1.Text, System.Globalization.NumberStyles.Number, null, out Start_Miles_Value)
                || !Int32.TryParse(textBox_End_Miles.Text, System.Globalization.NumberStyles.Number, null, out End_Miles_Value))
            {
                MessageBox.Show("Numerical Data Only.", "Invalid Miles Value", MessageBoxButtons.OK);
                textBox_End_Miles.ResetText();
                label_Miles_Drove1.ResetText();

            }
            else if (End_Miles_Value > Start_Miles_Value)
                label_Miles_Drove1.Text = (End_Miles_Value - Start_Miles_Value).ToString();
        }

When I do the textBox_End_Miles.ResetText(); it triggers the Text_Changed event, and shows the MessageBox twice. Is there a way around this?

I only made it to "a" :)
I'll change that now - thanks.