Question Locked

Trying to change combo box value in Excel from an outside VB6 program.

0 helpful
18 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I got an excel document with a combobox in a cell.
It has the values
"Commercial"
"Agricultural"

I have a VB6 program that opens this protected excel document, and auto fills data that we already know.

This combobox value is vital as it changes how the spreadsheet bases it's calculations. How can I change it's value? The spreadsheet is not visible during the pre-population.

For legal reasons, I am not allowed to change the document in any way. No adding code or changing layouts or anything. All my work must be contained within the VB6 program.

-Edit
It seems there are two types of Excel combo boxes. One that is done using form fill in fields that are changable even if the document is protected. And the VBA forms style which drops a windows like combo box into a cell. The latter is the one used in this document and what is causing my trouble. I need to change its value from outside. According to my boss, I am not allowed to just use a series of FindWindow and SendMessage commands to solve this.

18 replies

by slayer_

That just explains how to do it within Excel, which is of course easy, but from outside excel...

Even just getting an hwnd valud of the box would be acceptable... but I can't find anyways to get excel to give me information about its VBA controls.

by slayer_

This only lets me change values from Within Excel VBA, not from the outside.

It's really hard to search for, I wonder if anyone has ever done this.

by slayer_

I added a public sub to the sheet.

Public Sub ChangeType(ByVal sType As String)
Me.cboType.Text = sType
Call cboType_Click
End Sub

And wrote this code in VB6
Call oExcel.Run("Sheet1.ChangeType", "Agricultural")

And it works. BUT I do not want to be reliant on this changed spreadsheet, as the sheet can be changed without warning, thus losing the macro when a new version of this template is released.

I need a better way of doing this.

And thanks for trying Peconet Tietokoneet. You'll get thumbs if I get this resolved.

by slayer_

If the modification date is changed or the MD5, it needs to go through legal department and be revalidated.

At the moment, we are just hoping to get away with it, cause the contents of the document are unchanged, I just added a sub. But I would much rather a direct way to do it.

by slayer_

I added a public sub to the spreadsheet that changed the combobox values. Not an ideal solution but it worked.

by tobif

I don't know if it was later discontinued.
I've used Sendkeys for entering data into a mainframe terminal window, simualation of game playing and other weird stuff.
If you can find a way to do this with keyboard only, then sendkeys might help!

by slayer_

I should show you a video some time, its neat.

The code that keeps the sheet open is neat as well.

'Wait till it closes
   On Error Resume Next 'need this, incase the document is closed during a bad part
                        'of the loop, causing an error.
   bIsOpen = True
   Do Until bIsOpen = False
      bIsOpen = False
      For Each oBooks In oExcel.Workbooks
         If oBooks Is oWork Then 'Is the workbook we looped through and found, the same as the one we opened?     
            bIsOpen = True
            If Len(sFilePath) = 0 Then
               bSaved = oWork.Saved
               If bSaved Then
                  sFilePath = oWork.path & "\" & oWork.Name 'set the file path
               End If
            End If
         End If
      Next oBooks
      Call VBA.DoEvents
      'Call Sleep(100)
      If Err.Number <> 0 Then 'oh no, an error, something must be dead, let's break out                                   
         Err.Clear
         Exit Do
      End If
   Loop
   oExcel.Quit

by tobif

Would have been neat if the combo value was linked to a cell or named value, but you can't change the file.

Another approach might have been to prepare two versions of the file (i.e. save the file with the property set both ways) but, again, you can't touch the file in any way...

by slayer_

I added the code and had it pushed through compliance. Not an ideal solution but it worked. I do leave this question open in case someone comes up with the answer.

-Edit
I have edited the original question with more information. Maybe it will make it make more sense.

This discussion is closed to new replies.