#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0

    vb.net: Change Excel Checkbox Value


    Been a while since I used VB so bare with me please.

    I coded an app in php, but one part of it requires the ability to pull data from mysql
    and place it in an excel file. This can be done with PHPExcel, but it won't change the value of
    checkboxes in the excel file. No big deal, I figured VB should be able to do this in a separate process.

    I've never worked with the excel library in VB, I've always used CSV files. But this time my template
    has a checkbox..actually a few. I don't know the VB code to change the value. I searched and just
    found one example online, and it didn't work...it set a value to one and gave me a string conversion
    error.

    Humbly, I think I managed to open the workbook, but I really need to change the checkbox value
    programmatically using vb.net 2010. The excel version I'm using is also 2010, but the template file
    is 2003, of course I can save it to either one that works.

    Feel free to point out anything I'm doing wrong. Thanks in advance.

    Code:
    Dim appPath As String = My.Application.Info.DirectoryPath
            xlApp = New Excel.Application
    
            xlWorkBook = xlApp.Workbooks.Open(appPath & "\wb2.xlsx")          
            xlWorkSheet = xlWorkBook.Worksheets("Sheet1")   
            'xlWorkSheet.Shapes("Check box 202").ControlFormat.Value = 1  
           
    
            xlWorkBook.Save()
            xlWorkBook.Close()
    
            xlApp.Quit()
  2. #2
  3. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,453
    Rep Power
    9645
    An option: if PHP is running on a Windows machine that has has Excel installed, you can use COM to do the same sorts of stuff you can do using VBA. Looks something like
    PHP Code:
    $app = new COM("Excel.Application");
    $wb $app->Workbooks->Open($appPath "\\wb2.xlsx");
    $ws $wb->Worksheets->Item("Sheet1"); // slightly different here because Worksheets("Sheet1") is actually syntactic sugar
    // $ws->Shapes->Item("Check box 202")->ControlFormat->Value = 1;

    $wb->Save();
    $wb->Close();
    $app->Quit(); 
    Kinda hard to say anything without having the workbook itself available to look at. What are these checkboxes? How do they work?
    Would it be possible to put a macro in the spreadsheet that, on load, updates the checkbox according to whatever conditions? Would that prevent the checkbox from working correctly during normal usage?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0
    I considered the php option, but it's running on a linux machine. I also considered the macro approach, but can't get my copy of excel to run macros (greyed out), I've tried everything! All in all, I'd very much like it to run via vb.net. I didn't make the spreadsheet, it just says the checkbox is an object under properties. I'm an open office kinda person, so forgive me if I'm not hip on recent versions of excel. Last I used was 2003, 2010 seems like too much overkill, I really don't like it but that's what they use at the office.
  6. #4
  7. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,453
    Rep Power
    9645
    Not sure what you're saying is grayed out... Normally Alt+F11 (I think?) brings up the VBA editor, then you write code in there, then if you close and reload Excel will warn about macros and ask if you want them enabled. Ah, but files downloaded from the internet sometimes work differently, and it also depends on security settings in Excel, so maybe the macro approach won't work anyways.

    Perusing around MSDN, it looks like that ControlFormat.Value line you have should be correct. Have you tried it? Also make sure you're using the right name - it might be "CheckBox202" instead.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0
    I figured it out. Stumbled on some C# that read the value from the excel sheet. It lead me into the write direction of actually setting that value. Thanks for your help!

    For future reference to other users, here's what I ended up with. Not sure if it's the "best" way, but it works.

    Code:
     Dim appPath As String = My.Application.Info.DirectoryPath
            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Open(appPath & "\wb2.xlsx")
            Dim ws As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(xlWorkBook.Worksheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
            Dim cb As Microsoft.Office.Interop.Excel.CheckBox = DirectCast(ws.Shapes.Item("Check Box 211").OLEFormat.[Object], Microsoft.Office.Interop.Excel.CheckBox)
            cb.Value = False
            'MsgBox(cb.Value)
           
            xlWorkBook.Save()
            xlWorkBook.Close()
            xlApp.Quit()
  10. #6
  11. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,453
    Rep Power
    9645
    If that works then in VB I would expect you to be able to get away with just
    Code:
    xlWorkBook.Worksheets("Sheet1").Shapes.Item("Check Box 211").OLEFormat.Object.Value = False
    But maybe not.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0
    Didn't think of that! Actually, it works fine, thanks!

IMN logo majestic logo threadwatch logo seochat tools logo