Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
programming issue when using Excel automation
Hello,
My application interacts with Excel spreadsheet. Periodically it reads some data from the predefined sheet's column. Everything works fine, when user does not use that spreadsheet at the same time. However, when user is typing in the sheet at the same time when my app wants to read data from it there is a problem. Excel displays 'Component Busy' message with the Switch to..., Retry and Cancel options. How to solve that problem? My app uses Excel automation and it connects to Excel using this code: Public WithEvents moExcelApp As Excel.Application Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Your comments appreciated, Jack |
#2
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
programming issue when using Excel automation
Indeed you'll get that massage trying to attach a reference to an Excel
instance when it is in edit mode (tying in a cell before pressing enter). Following worked for me running in VB6 running from the IDE (having first put Excel into edit mode): -If Excel is not the active window bring it to front - Sendkeys Escape It means of course user might lose whatever was typed into some cell before pressing Enter, in practice relatively rare. Private Declare Function FindWindow Lib "user32" _ Alias "FindWindowA" ( _ ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare Function GetActiveWindow Lib "user32" () As Long Private Declare Function SetForegroundWindow Lib "user32" ( _ ByVal hwnd As Long) As Long Private moExcelApp As Object ' As Excel.Application Private Sub Form_Click() Dim bXLnotActive As Long Dim hWinActive Dim hXLwin As Long hXLwin = FindWindow("XLMAIN", vbNullString) If hXLwin = 0 Then ' no xl instance, use creatobject Else hWinActive = GetActiveWindow bXLnotActive = hWinActive < hXLwin If bXLnotActive Then SetForegroundWindow hXLwin Set moExcelApp = GetObject(, "Excel.Application") SendKeys "{ESC}" 'if bXLnotActive then reactivate the original active window, maybe End If Print moExcelApp.Caption End Sub Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) Set moExcelApp = Nothing End Sub I spent a while fiddling around with the Sendkeys in advance of doing the GetObject. Nothing worked, even using the keyboard event API instead of SendKeys. It seems counter intuitive but putting SendKeys after the GetObject seems to do the trick, at least it does for me. Not sure why though and it might not be the best way. Regards Peter T "Jack" <replyto@it wrote in message ... Hello, My application interacts with Excel spreadsheet. Periodically it reads some data from the predefined sheet's column. Everything works fine, when user does not use that spreadsheet at the same time. However, when user is typing in the sheet at the same time when my app wants to read data from it there is a problem. Excel displays 'Component Busy' message with the Switch to..., Retry and Cancel options. How to solve that problem? My app uses Excel automation and it connects to Excel using this code: Public WithEvents moExcelApp As Excel.Application Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Your comments appreciated, Jack |
#3
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
programming issue when using Excel automation
I do not understand your approach and how that can help me. In my case I want to have both: 1. being able to pull data out of spreadsheet and 2. do not interrupt user if he is using spreadsheet at the same time. Is that possible? Thanks, Jack "Peter T" <peter_t@discussions wrote in message ... Indeed you'll get that massage trying to attach a reference to an Excel instance when it is in edit mode (tying in a cell before pressing enter). Following worked for me running in VB6 running from the IDE (having first put Excel into edit mode): -If Excel is not the active window bring it to front - Sendkeys Escape It means of course user might lose whatever was typed into some cell before pressing Enter, in practice relatively rare. Private Declare Function FindWindow Lib "user32" _ Alias "FindWindowA" ( _ ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare Function GetActiveWindow Lib "user32" () As Long Private Declare Function SetForegroundWindow Lib "user32" ( _ ByVal hwnd As Long) As Long Private moExcelApp As Object ' As Excel.Application Private Sub Form_Click() Dim bXLnotActive As Long Dim hWinActive Dim hXLwin As Long hXLwin = FindWindow("XLMAIN", vbNullString) If hXLwin = 0 Then ' no xl instance, use creatobject Else hWinActive = GetActiveWindow bXLnotActive = hWinActive < hXLwin If bXLnotActive Then SetForegroundWindow hXLwin Set moExcelApp = GetObject(, "Excel.Application") SendKeys "{ESC}" 'if bXLnotActive then reactivate the original active window, maybe End If Print moExcelApp.Caption End Sub Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) Set moExcelApp = Nothing End Sub I spent a while fiddling around with the Sendkeys in advance of doing the GetObject. Nothing worked, even using the keyboard event API instead of SendKeys. It seems counter intuitive but putting SendKeys after the GetObject seems to do the trick, at least it does for me. Not sure why though and it might not be the best way. Regards Peter T "Jack" <replyto@it wrote in message ... Hello, My application interacts with Excel spreadsheet. Periodically it reads some data from the predefined sheet's column. Everything works fine, when user does not use that spreadsheet at the same time. However, when user is typing in the sheet at the same time when my app wants to read data from it there is a problem. Excel displays 'Component Busy' message with the Switch to..., Retry and Cancel options. How to solve that problem? My app uses Excel automation and it connects to Excel using this code: Public WithEvents moExcelApp As Excel.Application Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Your comments appreciated, Jack |
#4
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
programming issue when using Excel automation
hi Jack,
imho no. you can use always CreateObject to open the workbook in a second task and get data out of a sheet, even if this workbook is in use (in a first task) and even if it is in edit mode, but you don`t get the latest changes in the workbook which is open in the first task. if you need the latest data, you could use On Error (not sure if this works here, if not http://tinyurl.com/58awp8 ) to check if the sheet is in edit mode and than interact with the user (ie displaying a msgbox to stop edit mode) or cancel getting the data. stefan Jack wrote: I do not understand your approach and how that can help me. In my case I want to have both: 1. being able to pull data out of spreadsheet and 2. do not interrupt user if he is using spreadsheet at the same time. Is that possible? Thanks, Jack "Jack" <replyto@it wrote in message ... Hello, My application interacts with Excel spreadsheet. Periodically it reads some data from the predefined sheet's column. Everything works fine, when user does not use that spreadsheet at the same time. However, when user is typing in the sheet at the same time when my app wants to read data from it there is a problem. Excel displays 'Component Busy' message with the Switch to..., Retry and Cancel options. How to solve that problem? My app uses Excel automation and it connects to Excel using this code: Public WithEvents moExcelApp As Excel.Application Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Your comments appreciated, Jack |
#5
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
programming issue when using Excel automation
Thank you.
Your reply makes sense. I do not know, how to open the workbook in a second task. How to do that? Let assume the following scenario: My app on startup opens Excel spreadsheet. That sheet is what the user sees and it will let him to do editing. Now, how to open(?) the same sheet in the second task? Thanks, Jack "stefan onken" wrote in message ... hi Jack, imho no. you can use always CreateObject to open the workbook in a second task and get data out of a sheet, even if this workbook is in use (in a first task) and even if it is in edit mode, but you don`t get the latest changes in the workbook which is open in the first task. if you need the latest data, you could use On Error (not sure if this works here, if not http://tinyurl.com/58awp8 ) to check if the sheet is in edit mode and than interact with the user (ie displaying a msgbox to stop edit mode) or cancel getting the data. stefan Jack wrote: I do not understand your approach and how that can help me. In my case I want to have both: 1. being able to pull data out of spreadsheet and 2. do not interrupt user if he is using spreadsheet at the same time. Is that possible? Thanks, Jack "Jack" <replyto@it wrote in message ... Hello, My application interacts with Excel spreadsheet. Periodically it reads some data from the predefined sheet's column. Everything works fine, when user does not use that spreadsheet at the same time. However, when user is typing in the sheet at the same time when my app wants to read data from it there is a problem. Excel displays 'Component Busy' message with the Switch to..., Retry and Cancel options. How to solve that problem? My app uses Excel automation and it connects to Excel using this code: Public WithEvents moExcelApp As Excel.Application Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Your comments appreciated, Jack |
#6
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
programming issue when using Excel automation
hi Jack,
CreateObject opens a second task, you can see it in the Windows task manager. the task is not visible on the screen/taskbar until you make it visible. for using the task it is not necessary to make it visible. Set moExcelApp = CreateObject("Excel.Application") 'moExcelApp.Visible =True moExcelApp.Workbooks.Open "filename" 'get data moExcelApp.activeworkbook.close false moExcelApp.Quit Set moExcelApp = Nothing hth stefan On 21 Mai, 22:31, "Jack" <replyto@it wrote: Thank you. Your reply makes sense. I do not know, how to open the workbook in a second task. How to do that? Let assume the following scenario: My app on startup opens Excel spreadsheet. That sheet is what the user sees and it will let him to do editing. Now, how to open(?) the same sheet in the second task? Thanks, Jack |
#7
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
programming issue when using Excel automation
I do not understand your approach and how that can help me.
I think I explained the approach adequately so I take it you do not follow why I suggested to do it like that. OK, when Excel is in edit mode you cannot attach a reference to the Excel application. Also if you have previously attached the reference you cannot do anything with it while in edit mode. AFAIK the only way to exit edit mode is with Esc or Enter (some other actions may also exit edit mode like arrow keys, tab etc). So for your purposes you either need to force exit-edit-mode, or wait until user does it. The problem relying on the latter is user may have forgotten to complete some entry and tabbed away to some other window. I don't see a major problem in sending Esc to Excel, the worse that will happen is user loses a partial entry, thinks "that's odd" and tries again. In practice the scenario of you trying to attach to Excel while in edit mode is unlikely to occur often. I'm viewing this thread in Excel.programming but I see you have also multiposted to the vb group. If (?) you are using VB6 adapt what I posted previously to include OleServerBusyRaiseError (as suggested in the link referred to by Stefan). Something like this On error goto errH App.OleServerBusyRaiseError = True Set moExcelApp = GetObject(, "Excel.Application") in the error handler If Err.Number = -2147418111 And Not bEscpTried Then hWinActive = GetActiveWindow bXLnotActive = hWinActive < hXLwin If bXLnotActive Then SetForegroundWindow hXLwin SendKeys "{ESC}" bEscpTried = True ' to avoid multiple attempts and endless loop Resume End If (variables and declarations as per my previous post) You might also want to reduce OleServerBusyTimeout from (default in mine) 10000 milliseconds to say 5 sec's I've tested the above in VB6 and it appears to work fine. Note OleServerBusyRaiseError is n/a in VBA, unfortunately. If you want to open a workbook in "your own" instance, and your workbook might already be open - Dim oWB As Object ' As Excel.Workbook Dim bReadOnly As Boolean Set moExcelApp = CreateObject("excel.application") ' or with early binding ' set moExcelApp = Excel.Application ' moExcelApp.Visible = True '' for testing moExcelApp.DisplayAlerts = False Set oWB = moExcelApp.Workbooks.Open("C:\theFile.xls") bReadOnly = oWB.ReadOnly ' already open in another instance moExcelApp.DisplayAlerts = True Regards, Peter T "Jack" <replyto@it wrote in message ... I do not understand your approach and how that can help me. In my case I want to have both: 1. being able to pull data out of spreadsheet and 2. do not interrupt user if he is using spreadsheet at the same time. Is that possible? Thanks, Jack "Peter T" <peter_t@discussions wrote in message ... Indeed you'll get that massage trying to attach a reference to an Excel instance when it is in edit mode (tying in a cell before pressing enter). Following worked for me running in VB6 running from the IDE (having first put Excel into edit mode): -If Excel is not the active window bring it to front - Sendkeys Escape It means of course user might lose whatever was typed into some cell before pressing Enter, in practice relatively rare. Private Declare Function FindWindow Lib "user32" _ Alias "FindWindowA" ( _ ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare Function GetActiveWindow Lib "user32" () As Long Private Declare Function SetForegroundWindow Lib "user32" ( _ ByVal hwnd As Long) As Long Private moExcelApp As Object ' As Excel.Application Private Sub Form_Click() Dim bXLnotActive As Long Dim hWinActive Dim hXLwin As Long hXLwin = FindWindow("XLMAIN", vbNullString) If hXLwin = 0 Then ' no xl instance, use creatobject Else hWinActive = GetActiveWindow bXLnotActive = hWinActive < hXLwin If bXLnotActive Then SetForegroundWindow hXLwin Set moExcelApp = GetObject(, "Excel.Application") SendKeys "{ESC}" 'if bXLnotActive then reactivate the original active window, maybe End If Print moExcelApp.Caption End Sub Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) Set moExcelApp = Nothing End Sub I spent a while fiddling around with the Sendkeys in advance of doing the GetObject. Nothing worked, even using the keyboard event API instead of SendKeys. It seems counter intuitive but putting SendKeys after the GetObject seems to do the trick, at least it does for me. Not sure why though and it might not be the best way. Regards Peter T "Jack" <replyto@it wrote in message ... Hello, My application interacts with Excel spreadsheet. Periodically it reads some data from the predefined sheet's column. Everything works fine, when user does not use that spreadsheet at the same time. However, when user is typing in the sheet at the same time when my app wants to read data from it there is a problem. Excel displays 'Component Busy' message with the Switch to..., Retry and Cancel options. How to solve that problem? My app uses Excel automation and it connects to Excel using this code: Public WithEvents moExcelApp As Excel.Application Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Your comments appreciated, Jack |
#8
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
programming issue when using Excel automation
Thank you both, Peter and Stefan.
I like using the second task approach. My app can read the cell value without disturbing the user But, what will happen when my app decides to write back something to the cell? Will that info be updated in real time (without disturbing the user I assume) or it will be updated when the second task will cease to exist or it will be lost forever? Jack "Jack" <replyto@it wrote in message ... Hello, My application interacts with Excel spreadsheet. Periodically it reads some data from the predefined sheet's column. Everything works fine, when user does not use that spreadsheet at the same time. However, when user is typing in the sheet at the same time when my app wants to read data from it there is a problem. Excel displays 'Component Busy' message with the Switch to..., Retry and Cancel options. How to solve that problem? My app uses Excel automation and it connects to Excel using this code: Public WithEvents moExcelApp As Excel.Application Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Your comments appreciated, Jack |
#9
Posted to microsoft.public.excel.programming,microsoft.public.vb.general.discussion
|
|||
|
|||
programming issue when using Excel automation
I had to attend the different things and the 'second task approach' I put on
back burner, but now I am back at it. I am trying to do use the second task approach but I am unable to access it. I have started the new thread about it today. Thanks, Jack "stefan onken" wrote in message ... hi Jack, CreateObject opens a second task, you can see it in the Windows task manager. the task is not visible on the screen/taskbar until you make it visible. for using the task it is not necessary to make it visible. Set moExcelApp = CreateObject("Excel.Application") 'moExcelApp.Visible =True moExcelApp.Workbooks.Open "filename" 'get data moExcelApp.activeworkbook.close false moExcelApp.Quit Set moExcelApp = Nothing hth stefan On 21 Mai, 22:31, "Jack" <replyto@it wrote: Thank you. Your reply makes sense. I do not know, how to open the workbook in a second task. How to do that? Let assume the following scenario: My app on startup opens Excel spreadsheet. That sheet is what the user sees and it will let him to do editing. Now, how to open(?) the same sheet in the second task? Thanks, Jack |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel automation issue---enter date into cell based on other cellvalue | Excel Programming | |||
Excel Automation : numbers Copy/Paste issue from VB6 | Excel Programming | |||
where do I find docs on excel automation programming? | Excel Programming | |||
Excel Automation Issue | Excel Programming |