fred s.

I am creating an Excel spreadsheet from VB.net. The program populates some cells of the spreadsheet. The user can also enter data into cells. These two activities can happen consecutively (not concurrently).

If the user does not hit ENTER after entering their data, VB.net cannot regain control of Excel. Is there a way around this

Below is a short test stub that demonstrates this. The user enters a number into cell A1. After he does this, the user hits ok to the msgbox. The square of the number goes into A2. If the user does not hit enter after typing in their number, a HRESULT: 0x800A03EC error occurs.

Any help appreciated,

Fred

Sub Main()

Dim excel As Excel.Application

Dim wb As Excel.Workbook

Dim sheet As Excel.Worksheet

Dim data3 As Integer

Dim rng As Excel.Range

excel = New Excel.Application

wb = excel.Workbooks.Add

sheet = CType(wb.Worksheets.Add, Microsoft.Office.Interop.Excel.Worksheet)

excel.Visible = True

wb.Activate()

rng = sheet.Range("A1")

MsgBox("enter number in cell A1")

data3 = CInt(rng.Value)

Do While data3 <> 100

rng = sheet.Range("A2")

rng.Value = data3 * data3

rng = sheet.Range("A1")

MsgBox("enter number in cell A1")

data3 = CInt(rng.Value)

Loop

End Sub



Re: Visual Basic Language Regaining control of Excel spreadsheet from VB.net

Woodered

I'm gettig the same error as you and I'm pretty good at excel automation. The problemis that the cell A1 doesn't have a value until you hit enter.

Will something like this work for you:

Dim excel As Excel.Application

Dim wb As Excel.Workbook

Dim sheet As Excel.Worksheet

Dim data3 As Integer

Dim rng As Excel.Range

excel = New Excel.Application

wb = excel.Workbooks.Add

sheet = CType(wb.Worksheets.Add, Excel.Worksheet)

excel.Visible = True

wb.Activate()

sheet.Range("A1").Value = InputBox("Enter your value")

data3 = CInt(sheet.Range("A1").Value)

Do While data3 <> 100

sheet.Range("A2").Value = data3 * data3

sheet.Range("A1").Select()

sheet.Range("A1").Value = InputBox("Enter your value")

data3 = CInt(sheet.Range("A1").Value)

Loop





Re: Visual Basic Language Regaining control of Excel spreadsheet from VB.net

Riquel Dong ¨C MSFT

Hi Fred,

Based on your post, my understanding is that after your application automates Excel application, you need to modify Excel data when the user enter "Edit mode".

As so far I know, your application can't do this. Now two thread simultaneously access Excel worksheet. It is not thread safe. I recommend when your application automates Excel application, the user dosen't enter "Edit mode". Your application also can modify Excel data in your code at runtime. Also note that Excel doesn't provide an interface for outside components to detect "Edit mode" (e.g. when a cell is accepting input). I recommend when your application automates Excel application, the user doesn't enter "Edit mode" simultaneously.

Best regards,

Riquel.






Re: Visual Basic Language Regaining control of Excel spreadsheet from VB.net

fred s.

Thanks. It seems that there is not a way to regain control of Excel when it is edit mode. I am going to restructure my application so that all user input to the Excel spreadsheet is controled through the vb.net application.

Thanks for your help,

Fred