View Full Version : VISTA: multiple excel apps
Kindly_Kaela
02-13-2007, 01:09 PM
Not sure how many of you have started playing with Vista and Office 07. I ran into a problem and hoping someone may have a solution.
I have 2 TV's running off of 1 computer. I want to run 2 Excel files (one on each TV). My objective was to have a userform on one of the Excel files that will control both Excel files, allowing the user to flip through graphs on either TV by using 1 UserForm.
Both Excel files are the same (just with different names) so the UserForm can easily adjust to both files. In XP, I used the following line of code to switch between Excel files via a radial button...
Private Sub OB_TVL_Click()
Windows("SOC_TV_Left.xls").Activate
End Sub
Private Sub OB_TVR_Click()
Windows("SOC_TV_Right.xls").Activate
End Sub
For whatever reason, Office 2007 does not recognize this line of code. And the funny thing is, it works great in Office 2003. I even tried recording a macro in 2007. While recording, I switched between Excel files . The macro in 2007 did not detect the transition and only detected what I did in the file that was recording. But in 2003, it detects the transition fine.
Hopefully I explained this correctly. Please feel free to ask me any questions. I would love to leave Vista running, but not if I can't fix this.
Thanks!!
Kaela
:cloud9:
Simon Lloyd
02-14-2007, 02:31 AM
I think it may be something along the lines of Excel.WorkBook("SOC_TV_Left.xls").Activate Hope this helps,
Regards,
Simon
Kindly_Kaela
02-14-2007, 08:50 AM
No luck Simon, it brought up an error message in Excel 2007.
Thanks for trying.
Bob Phillips
02-14-2007, 09:07 AM
Don't think it will make any difference, but it should be Workbooks (plural), not Workbook.
Kindly_Kaela
02-14-2007, 09:33 AM
Run-Time Error 9
Subscript out of Range
Bob Phillips
02-14-2007, 09:59 AM
That means your workbook is not there in the workbooks collection.
Simon Lloyd
02-15-2007, 03:34 AM
I'm sure you're aware you cannot activate a closed workbook, you have to open it first, that aside have a look at this thread http://www.vbaexpress.com/forum/showthread.php?t=10373&highlight=runtime+error+9
Regards,
Simon
Kindly_Kaela
02-15-2007, 08:46 AM
Here's the thing, the workbook is open. Here is the exact steps I am taking. I'm praying someone has a solution or can help me understand what I'm doing wrong.
STEPS I TAKE:
Open Excel, and drag that Excel to left TV (right TV is the one with the task bar, as they are both running on the same PC.
In the left Excel, open SOC_TV_Left.xls
Open Excel again, and drag the new Excel to the Right TV. In this excel, open SOC_TV_Right.xls.
Then I run the macro from the SOC_TV_Right.xls, and a userform appears.
This userform is not detecting SOC_TV_Left.xls, and it's definitely open.Is it Excel 07 (Vista), or is it me?
Halldo
02-15-2007, 09:33 AM
Kaela,
I have had a problem similiar to this in 2003 to 2007. What I found when using the Windows() command, is that if the Windows drop down menu shows the file with the xls extension then VBA would work. If this menu did not I had to code it without the xls extension and it would work.
So Windows("SOC_TV_Right.xls").Activate becomes Windows("SOC_TV_Right").Activate.
I just tried this in 2007, with the .xls extension I get the runtime 9 error, without it seems to work ok.
This may not resolve the issue, but did not think it could hurt to share.
Good Luck,
Halldo
Kindly_Kaela
02-15-2007, 09:34 AM
OK, I figured something new out that maybe will help someone help me :girlie:
The following command works in Excel 2007, IF I open a 2nd workbook within the SAME Excel application that is already open.
Excel.Workbooks("TV_Left.xls").Activate
However, the problem is I need a 2nd window that I can drag to the left TV. In Office 2003, all you have to do is click the check box for:
Tools -> Options -> Windows in Taskbar
...and it will split the windows up so I can drag either wherever I please. But I can't find this option in Excel 2007. Therefore, the only dragging I can do is within the main opened Excel application window (which just covers one TV).
Remember, my objective is to display and control 2 Excel workbooks (one on the Left TV and one on the Right TV) through 1 User_Form / Macro.
Thanks!!
Kaela
:cloud9:
Kindly_Kaela
02-15-2007, 09:47 AM
Kaela,
So Windows("SOC_TV_Right.xls").Activate becomes Windows("SOC_TV_Right").Activate.
No luck, same error for me, but thanks for trying.
Bob Phillips
02-15-2007, 09:51 AM
I don't have two monitors, so guessing, but how about opening a second Excel instance?
Also, do you really mean Vist, or just Excel 2007? Vista is the new OS.
Kindly_Kaela
02-15-2007, 10:20 AM
I don't have two monitors, so guessing, but how about opening a second Excel instance?
Also, do you really mean Vist, or just Excel 2007? Vista is the new OS.
Yea, that is exactly what I'm doing...opening up 2 instances of Excel. That's the only way I can figure out how to drag one of each to seperate TVs. But that's the problem, when 2 instances of Excel are open, I can't control them both from one User_Form....or I dont know the proper command.
I mean Excel 2007. But I'm running Vista too. I should change the title of this thread.
Norie
02-15-2007, 01:43 PM
Kaela
Since you have 2 instances of Excel I think the only way you'll be able to do this is with the Windows API.
I also don't have 2 monitors so can't test or try out anything.
I've got 2 TVs though but I normally just use them for watching television.:)
Kindly_Kaela
02-15-2007, 01:58 PM
Kaela
Since you have 2 instances of Excel I think the only way you'll be able to do this is with the Windows API.
Hi Norie, sorry to sound ignorant, but what is Windows API? Is that C++? If so, then I'm SOL.
Sounds like I'm heading back to Excel 2003 :(
Norie
02-15-2007, 02:32 PM
Kaela
It's the Windows Application Programming Interface and can be used from VBA.
I'm not sure exactly how you might be able to use it but it has functions like FindWindow, GetWindow etc.
Bob Phillips
02-15-2007, 03:40 PM
Even with APIs, you are going to struggle controllig those instances. You would have to enumerate all instances, real messy.
Bob Phillips
02-15-2007, 04:36 PM
Here is a simple routine that enumerates all of the windows and puts them into an array with its handle, class name, and ID type.
Excel has a class nameof XLMAIN, which is found in the array aryWindows(3, n)
Option Explicit
Private aryWindows
Private cWindows As Long
Public Declare Function EnumWindows Lib "user32" ( _
ByVal lpEnumFunc As Long, _
ByVal lParam As Long) As Long
Public Declare Function EnumChildWindows Lib "user32" ( _
ByVal hWndParent As Long, _
ByVal lpEnumFunc As Long, _
ByVal lParam As Long) As Long
Private Declare Function GetWindowTextLength Lib "user32" _
Alias "GetWindowTextLengthA" ( _
ByVal hwnd As Long) As Long
Private Declare Function GetWindowText Lib "user32" _
Alias "GetWindowTextA" ( _
ByVal hwnd As Long, _
ByVal lpString As String, _
ByVal cch As Long) As Long
Private Declare Function GetClassName Lib "user32" _
Alias "GetClassNameA" ( _
ByVal hwnd As Long, _
ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long
Private Declare Function IsWindowVisible Lib "user32" ( _
ByVal hwnd As Long) As Long
Private Declare Function GetParent Lib "user32" ( _
ByVal hwnd As Long) As Long
Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" ( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Public Sub GetWindows()
ReDim aryWindows(0 To 3, 0)
Call EnumWindows(AddressOf EnumWindowProc, &H0)
End Sub
Public Function EnumWindowProc(ByVal hwnd As Long, _
ByVal lParam As Long) As Long
Dim sTitle As String
Dim sIDType As String
Dim sClass As String
If GetParent(hwnd) = 0& And IsWindowVisible(hwnd) Then
sTitle = GetWindowIdentification(hwnd, sIDType, sClass)
ReDim Preserve aryWindows(0 To 3, 0 To cWindows)
aryWindows(0, cWindows) = hwnd
aryWindows(1, cWindows) = sTitle
aryWindows(2, cWindows) = sIDType
aryWindows(3, cWindows) = TrimNull(sClass)
cWindows = cWindows + 1
End If
EnumWindowProc = 1
End Function
Private Function GetWindowIdentification(ByVal hwnd As Long, _
sIDType As String, _
sClass As String) As String
Dim nSize As Long
Dim sTitle As String
nSize = GetWindowTextLength(hwnd)
If nSize > 0 Then
sTitle = Space$(nSize + 1)
Call GetWindowText(hwnd, sTitle, nSize + 1)
sIDType = "title"
sClass = Space$(64)
Call GetClassName(hwnd, sClass, 64)
Else
sTitle = Space$(64)
Call GetClassName(hwnd, sTitle, 64)
sClass = sTitle
sIDType = "class"
End If
GetWindowIdentification = TrimNull(sTitle)
End Function
Private Function TrimNull(startstr As String) As String
Dim pos As Long
pos = InStr(startstr, Chr$(0))
TrimNull = IIf(pos, Left$(startstr, pos - 1), startstr)
End Function
but I have forgotten what you want them for.
Simon Lloyd
02-16-2007, 04:37 AM
Bob, Just like to say.............WOW! amazing amount of work there!
Regards,
Simon
Bob Phillips
02-16-2007, 06:03 AM
Actually Simon, I have to come clean. I picked it out of some other code that I have. All I then did was to take out the bits not needed, and add some code to store the information in an array. Took me 10 minutes.
The hard bit comes in using it effectively, as I said I don't really understand what Kaela is doing/trying to do, so it is difficult to help at this point.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.