By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,745 Members | 1,897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,745 IT Pros & Developers. It's quick & easy.

Excel VBA set active document

100+
P: 142
help please,
I use excel VBA to open a MS application and set that application's document as activedocument. After the first MS application opened the excel VBA open another MS application and set that application's document as activedocument. After the second application quit, I need the excel automative detect the first opened MS application back as active application. How can it possible?
thanks
Apr 24 '07 #1
Share this Question
Share on Google+
6 Replies


100+
P: 142
help please,
I use excel VBA to open a MS application and set that application's document as activedocument. After the first MS application opened the excel VBA open another MS application and set that application's document as activedocument. After the second application quit, I need the excel automative detect the first opened MS application back as active application. How can it possible?
thanks
never mind
Apr 24 '07 #2

Expert 5K+
P: 8,434
never mind
I'll notify the team to stop work on this immediately. ;)
Apr 25 '07 #3

100+
P: 142
I'll notify the team to stop work on this immediately. ;)
Actually, i still need help on that. There is a little bit change on the description above. Instead open two MS application but only one MS application with two workbooks. And the second workbooks should not be quit but just finish runing.
So the full description should be:
I use excel VBA to open a MS application and set document(A) as activedocument. After the document(A) opened, the excel VBA open document(B) in the same MS application and set that document(B) as activedocument. After VBA finish using document(B), with leaving document(B) open, I need the excel automative detect the document(A) back as active document. How can it possible?
Thanks
Apr 25 '07 #4

SammyB
Expert 100+
P: 807
>open a MS application
What application?

>set document(A) as activedocument
How? Be sure to save a reference to this doc.

>detect the document(A) back as active document.
Use the code above (the "How?") with the saved reference.

If this is all Excel, then after activating A:
Set wbA = ActiveWorkbook

Then when you want to switch back to A
wbA.Activate

In fact Activate should do the trick for most MS apps. Works for Word. but not for Access (but, nothing works the same for Access).
Apr 30 '07 #5

100+
P: 142
>open a MS application
What application?

>set document(A) as activedocument
How? Be sure to save a reference to this doc.

>detect the document(A) back as active document.
Use the code above (the "How?") with the saved reference.

If this is all Excel, then after activating A:
Set wbA = ActiveWorkbook

Then when you want to switch back to A
wbA.Activate

In fact Activate should do the trick for most MS apps. Works for Word. but not for Access (but, nothing works the same for Access).
what I do just set up two functions in module, they are the same except one function declare another application at begining and Set app=nothing at the end, and then use the Call function to call those two function. I was thinking it may open a new application window, but it won't, so it work.
thanks anywhere
Apr 30 '07 #6

P: 1
To the poster above, if you're having trouble with the macro reference (if you are only doing one file) in Excel (I don't know about other products - didn't try) but you can just add this line after your initial open:

ActiveWindow.ActivateNext

because excel opens new windows to open files in macros, its still looking at the window with your initial file. This will force it to open the next window.

Now, I'm having a similar problem to him, but somewhat more complicated. Mine is running a loop to open all files in a particular folder, print them and close them. I have the macro working - to some degree. here is my code (this is excel):
Expand|Select|Wrap|Line Numbers
  1. Sub RMPProducer()
  2.  
  3.   OldPath = "S:\RMBS_Performance_Analytics\Analysis\1 Staging Folder For Monthly Model Templates\2007\200704\VV\Deals"
  4.   Dim t As Workbook
  5.   Dim s As String
  6.   Dim a As Window
  7.  
  8.   With Application.FileSearch
  9.     .NewSearch
  10.     .LookIn = OldPath
  11.     .SearchSubFolders = False
  12.     .Filename = "*.xls"
  13.     .MatchTextExactly = True
  14.     If .Execute() > 0 Then
  15.       For i = 1 To .FoundFiles.Count
  16.         s = .FoundFiles(i)
  17.         Workbooks.Open (s)
  18.         Set t = Workbooks(i)
  19.         t.Activate
  20.         ActiveWindow.ActivateNext
  21.         ActiveWorkbook.Sheets("Summary").Select
  22.         ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
  23.         ActiveWorkbook.Sheets("Collateral Graphs").Select
  24.         ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
  25.         ActiveWorkbook.Sheets("CLASS CE GRAPH").Select
  26.         ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
  27.         ActiveWorkbook.Sheets("XS AND OC TABLE").Select
  28.         ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
  29.       Next i
  30.     Else
  31.       MsgBox "There were no files found."
  32.     End If
  33.   End With
  34. End Sub
I have 2 problems. First problem is that I am trying to add a function to make the window close once its finished printing. This actually works - but it limits my loop iterations from i = 1 to 2 (so it only runs 2 times and I'm not exactly sure why). If I don't include the statement - this can run for up to 7 - 8 times (these are very large files) then excel runs out of memory. Unfortunately, i can't set the loop to run from i = anything other than one.

So What I would prefer is to be able to actually activate the ActiveWindow.close command - because this way I can run the entire loop. But I'm not sure how to. I tried window referencing, I tried adding a new active Window so when the file opens, its opening in the active window already, but nothing works.

Preferably, I need to be able to reference both windows, as my next macro will involve referencing 2 windows (one static - always open) and the other the loop above.

Anyone have any ideas/ All help is appreciated.
May 7 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.