VISTA: multiple excel apps

[Kindly_Kaela]

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...

[VBA]
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
[/VBA]

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

[Simon Lloyd]

I think it may be something along the lines of [VBA]Excel.WorkBook("SOC_TV_Left.xls").Activate [/VBA]Hope this helps,
Regards,
Simon

[Kindly_Kaela]

No luck Simon, it brought up an error message in Excel 2007.

Thanks for trying.

[Bob Phillips]

Don't think it will make any difference, but it should be Workbooks (plural), not Workbook.

[Kindly_Kaela]

Run-Time Error 9
Subscript out of Range

[Bob Phillips]

That means your workbook is not there in the workbooks collection.

[Simon Lloyd]

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/show...untime+error+9

Regards,
Simon

[Kindly_Kaela]

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]

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]

OK, I figured something new out that maybe will help someone help me

The following command works in Excel 2007, IF I open a 2nd workbook within the SAME Excel application that is already open.

[VBA]
Excel.Workbooks("TV_Left.xls").Activate
[/VBA]

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

[Kindly_Kaela]

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]

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]

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]

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]

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]

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]

Even with APIs, you are going to struggle controllig those instances. You would have to enumerate all instances, real messy.

[Bob Phillips]

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)

[vba]

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
[/vba]

but I have forgotten what you want them for.

[Simon Lloyd]

Bob, Just like to say.............WOW! amazing amount of work there!

Regards,
Simon

[Bob Phillips]

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.