Solved

Workbooks.Activate won't work

Posted on 2001-09-12
48
1,374 Views
Last Modified: 2007-11-27
Got a macro open a workbook with an openworkbook macro which calls another macro. In this third macro, I switch to another workbook. It doesn't work.
Workbooks("name.xls").Activate

It used to work - before I added the macro that opens the workbook with the openworkbook macro. It seems like none of my activates work anymore...I've gone through my manually changing windows to check...what's going on?

I also tried the Windows.activate - same thing
0
Comment
Question by:starl
  • 27
  • 17
  • 3
  • +1
48 Comments
 
LVL 7

Expert Comment

by:q2eddie
ID: 6478110
Hi, starl.

#Try This
Use the path in the "windows.activate" command.

Windows("c:\temp\name.xls").Activate

Bye. -e2
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6478137
Hi, again.

or maybe ...

Application.Windows("name.xls").Activate

#Questions
1. What are the names of the workbooks?

Bye. -e2
0
 
LVL 7

Accepted Solution

by:
q2eddie earned 100 total points
ID: 6478163
Hi, again.

Sorry for all the notifs.

#Comment
I was able to successfully reproduce your situation.
It appears that the [Application.Windows("name.xls").Activate] command prevents the error.

#Sample Files
I have posted my example files in my briefcase for peer review.

http://briefcase.yahoo.com/q2eddie
Public > Experts-Exch... > Q20182084

(The readme.txt file explains how to use the spreadsheets to recreate the error.)

Bye. -e2
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6478261
Hi starl, what does that openworkbook macro, you said that since placing it things aren't working as usual...:O)Bruintje
0
 
LVL 8

Author Comment

by:starl
ID: 6479347
bruintje, the entire project is a timesheet, copying certain lines from one workbook to another. The openworkbook macro checks to see whose opening the workbook and then there are several options from there. I can't drop the openworkbook.

e2 - gimme a few and I'll give your code a try. btw, thanks for introducing me to yahoo briefcase - pretty darn handy!
0
 
LVL 8

Author Comment

by:starl
ID: 6479357
btw, workbook names will differ. at the moment, the ones I'm using are tysexmpt.xls (which is the one with the first running macro) and nbach.xls (this one will differ w/every timesheet - and it includes the openworkbook macro).

did I answer all your qs this time???
0
 
LVL 8

Author Comment

by:starl
ID: 6479708
WAH!!! now nothing works!!!!
grrrr, grumble.....
HELP!!!!
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6479716
Hi, starl.

#Questions
1. Did you try the [Application.Windows("name.xls").Activate] command?
2. Could you post some (non-sensitive) code from both workbooks?
3. What type of error are you getting?

Bye. -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6479736
ok - I got around one of my probs (the latest - I was pointing at the wrong workbook).

1. yes, I'm currently working w/the suggestion...
2. none of the code is sensitive - just the names
3. the error is pretty general - it doesn't always like line from #1. currently stopping at
Application.Windows(exempt).Activate
where I've created exempt as a constant which it DOES read properly...error - subscript out of range.
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6479749
Hi, again.

#Questions
1. What happens if you use a string instead of a constant?
ex. Application.Windows("tysexmpt.xls").Activate

I'm attempting to reproduce the situation using this new information.

Bye. -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6479751
ugh - posting my code is terrible... it's already been pointed out to me what a terrible programmer I am!

sigh - here's the latest stumper:

Private Sub Find_location(time_sheet)
Application.Windows(exempt).Activate
Worksheets("Variables").Activate
Range("A1").Select
firstrow = 1
lastrow = 10
namecolumn = 4
firstcellcolumn = 5
time_sheet = LCase(time_sheet)

For i = firstrow To lastrow
    Name = Range(Cells(i, namecolumn), Cells(i, namecolumn)).Value
  If time_sheet = Name Then
        startrow = Range(Cells(i, firstcellcolumn), Cells(i, firstcellcolumn)).Value
        Range("pasterow").Value = startrow
    Exit Sub
    End If
Next i

End Sub


this macro is called from the main macro - I got the openworkbook working. The openworkbook eventually calls Transfer_Times which is the main macro - it does all the transferring. But before that, Transfer_Times call Find_Location which finds where on the final timesheet the information transferred should be pasted. It's pretty simple! I've tried App.Windows and Workbooks. they only reason I can think of a problem is b/c it's open...
0
 
LVL 8

Author Comment

by:starl
ID: 6479759
I'll try the string... but that's a lot of typing!
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6479768
Hey, starl.

Just confirmed that the constant/string thing will not make any difference.

Looking at your code now.

Bye. -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6479778
yeah - I confirm your confirmation. I tried again both windows and workbooks. removed the application.. *sigh* no go.
0
 
LVL 8

Author Comment

by:starl
ID: 6479794
here's the openworkbook macro(w/a few changes for confidentiality)
Const Administrator = "L"
Const Programmer = "S"
Const Boss = "J"
Const adminPW = "time"

Const drv = "c:\"
Const pth = "initls\"
'Const savedrv = "\\G_M\SYS"
'Const savepth = "\users\engr\common\timesht\"
Const savedrv = "d:"
Const savepth = "\timesht\"

Public abrvusr
Public SentRange
Public SignRange
Public Signed
Public Sent
Public sigfile
Public noUser
Public IsAdmin
Public IsBoss
Public time_sheet

Private Sub Workbook_Open()

'Sub Test()
ActiveWorkbook.Worksheets("Timesheet").Activate
'Application.Worksheets(savedrv & savepth & "TimeSheet").Activate

If LastName = Administrator Then
    response = MsgBox("Do you need to fill out the timesheet?", vbYesNo)
        If response = vbYes Then
            ActiveSheet.Unprotect Password:=adminPW
            EmpName
            ActiveSheet.Protect Password:=adminPW
            Exit Sub
        Else
            time_sheet = ThisWorkbook.Name
            Application.Run "TYSEXMPT.XLS!Transfer_Times", time_sheet
            Exit Sub
        End If
End If

If LastName = Programmer Then
    response = MsgBox("Program?", vbYesNo)
        If response = vbYes Then
            ActiveSheet.Unprotect Password:=adminPW
            Exit Sub
        Else
            response2 = MsgBox("Test Program?", vbYesNo)
                If response2 = vbYes Then
                    time_sheet = ThisWorkbook.Name
                    Application.Run "TYSEXMPT.XLS!Transfer_Times", time_sheet
                    Exit Sub
                Else
                    ActiveSheet.Unprotect Password:=adminPW
                    EmpName
                    ActiveSheet.Protect Password:=adminPW
                    Exit Sub
                End If
        End If
End If

EmpName 'put username on timesheet
 
End Sub
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6479841
Hi, again.

I have reproduced the condition (while not an error - it does not return to the original calling workbook).

Debugging. -e2
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6480206
starl, ...

Doesn't look good.
I found a workaround (sort of).  It sucks, but it may keep you from re-writing your code.

This is my sample test code - it's here just for discussion purposes.  Code intended for you is in the section after this one.

<------------ My test sample code begins ------------->
With Application.Workbooks("book2.xls").Sheets("Vari")
    ' both msgbox return correct results
    MsgBox .Name
    MsgBox .Range("A1").Value
    ' this line errors out - Select method of Range class failed.
    .Range(.Cells(1, 1), .Cells(1, 1)).Select
    ' this for-next loop appears to work just fine.
    For i = 1 To 3
        uValue = .Range(.Cells(i, 1), .Cells(i, 1)).Value
        If VarType(uValue) = vbEmpty Then
            .Range(.Cells(i, 1), .Cells(i, 2)).Value = 3
            Exit Sub
        End If
    Next i

End With
<------------ My test sample code ends ------------->

So, ... how does this help you?
Here is your Find_location procedure modified for this workaround.  (My goal here was to keep you from having to "re-locate" your code.)

<--------- Modified Find_location Code Begins ---------->
With Application.Workbooks(exempt).Sheets("Variables")
    firstrow = 1
    lastrow = 10
    namecolumn = 4
    firstcellcolumn = 5
    time_sheet = LCase(time_sheet)
   
    For i = firstrow To lastrow
        Name = .Range(.Cells(i, namecolumn), .Cells(i, namecolumn)).Value
        If time_sheet = Name Then
            startrow = .Range(.Cells(i, firstcellcolumn), .Cells(i, firstcellcolumn)).Value
            .Range("pasterow").Value = startrow
            Exit Sub
        End If
    Next i
End With
<--------- Modified Find_location Code Ends ---------->

Nuts!  And the wanted workbook still won't "activate".

Bye. -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6480411
I wish I understood why it won't switch anymore... I was switching back and forth before...
one difference was that the time_sheet workbook would already be open. Now, I'm opening it with code - and it stays on top. So that has to be part of the problem!

Maybe modify my workbook open...here's that code:

Sub OpenSheets()
Workbooks("tysexmpt.xls").Activate
Worksheets("Variables").Activate
firstrow = 1
lastrow = 10
namecolumn = 4
firstcellcolumn = 5

For i = firstrow To lastrow
    Name = Range(Cells(i, namecolumn), Cells(i, namecolumn)).Value
    Workbooks.Open lsavedrv & lsavepth & Name
Next i

End Sub

hmm. I see a few changes I can make... let me give it a try.
0
 
LVL 8

Author Comment

by:starl
ID: 6480540
*sigh* no luck...this is driving me nuts - it just ignores the activate (depending on how it's written) w/o an error...
0
 
LVL 8

Author Comment

by:starl
ID: 6480570
ok - if I skip the open sheets and open the workbook myself - the rest of my code has no problem. btw, I've changed the code to the way it was before I ran into any problems (no more application.windows("").Activate).
soo.. what's wrong with my opensheets macro that prevents other windows from going active???
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6480594
Hey, starl.

#Try This

Workbooks("tysexmpt.xls").Activate
'Worksheets("Variables").Activate        ' <--- comment out
ActiveWorkbook.Sheets("Variables").Activate    ' <--- new
firstrow = 1
lastrow = 10
namecolumn = 4
firstcellcolumn = 5

Bye. -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6480775
no difference.
this opensheets code is already in workbook tysexmpt.xls.
it opens another workbook which becomes the active one. It then needs to switch back to tysexmpts.xls - and it won't.
0
 
LVL 8

Author Comment

by:starl
ID: 6480788
oh - this is very interesting. I added a line to select a cell in the openworkbook - and it DOESN'T. though the rest of my open code does work...
this IS getting somewhere!
0
 
LVL 8

Author Comment

by:starl
ID: 6480801
I set some watches, tho, and the workbook & sheet are active...
but it won't select a cell..
I'm gonna change the macro from an auto and see what happens.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 7

Expert Comment

by:q2eddie
ID: 6480804
I'm following ya.  -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6480812
ok - if I skip the auto macro, it runs.. but I don't know how to get it to run a macro in a workbook who's name will vary...
   Application.Run Name & "!OpenT", time_sheet
doesn't work
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6480825
darn looks like spam :O)

what's this all about
1. workbook X
2. opens workbook Z
3. in Z there's an open_macro
4. then return focus to workbook X
0
 
LVL 8

Author Comment

by:starl
ID: 6480826
uhm - drop the ,time_sheet
but still doens't work - says it can't find the macro. It's not private..
0
 
LVL 8

Author Comment

by:starl
ID: 6480833
bruintje - that's what you get for ignoring me!

workbook x opens workbook z which runs macro A which jumps back and forth between the two workbooks.

code works fine if I remove the opensheets macro which open the other workbook. Or, if I do have the macro going, then only part of my automacro will work - it will run but it won't let me even select a cell!
0
 
LVL 8

Author Comment

by:starl
ID: 6480843
it's gotta be that workbooks open - it somehow messes up everything...
now that I think about it - it never even prompts to ask me if I wanna run the macros in it...I wonder if that's part of the problem..
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6480844
Hey, starl.

I'm curious...

>I added a line to select a cell in the openworkbook
What did you change in your code?

Bye. -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6480867
just added a "Range("A1").Select to see if it would select the cell - it didn't. I even tried adding ActiveSheet in front of it and nothing changed.
0
 
LVL 1

Expert Comment

by:manf788265
ID: 6480943
Hi starl,
 I am not entirely sure if this is what you ty to achieve:
From within book1 run a macro to open book2.
Book 2 automatically calls book3 (via Auto_Open macro).
Book3 selects  one of the Open books (i.e. book2).
If this is what you require, the following code will do it (tested on of2000)

in book1-module1:
Sub OpenBook2()
    Workbooks.Open Filename:="c:\temp\book2.xls"
    Windows("book2.xls").Activate
    ActiveWorkbook.RunAutoMacros xlAutoOpen
End Sub

In book2 - module1:
Sub AUTO_OPEN()
    Workbooks.Open Filename:="c:\temp\book3.xls"
    Windows("book3.xls").Activate
 End Sub

In book3 - This Workbook:
Private Sub Workbook_Open()
       Application.OnTime Now, "SelectBook2"
End Sub

In book3 - module1:
Sub SelectBook2()
Application.Windows("book2.xls").Activate
End Sub

Hope this helps,
Regards, Manfred
0
 
LVL 8

Author Comment

by:starl
ID: 6480947
I'm running 97 - but I'll give it a try tomorrow...
I don't know if that'll autopen will work tho b/c the reactiving of book 1 is within an if then of book 2...
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6480962
Hi, starl.

I just tried manf788265's suggestion.  It appears to work.  I am impressed - I would have never thought to use the OnTime method to accomplish this.  Now that I think about it, I've seen similar techniques used in VB to execute multithreaded code.

<--------- Simplified Code Begins ------------->
' Module in First Spreadsheet
Private Sub test()
    Workbooks.Open FileName:="c:\temp\book3.xls"
    Windows("book3.xls").Activate
    ActiveWorkbook.RunAutoMacros xlAutoOpen
End Sub

' Module in Second Spreadsheet
Sub Auto_Open()
    Application.OnTime Now, "testd"
End Sub
Sub testd()
    Application.Windows("book2.xls").Activate
    Worksheets("Variables").Activate
    MsgBox Range("A1").Value
End Sub
<--------- Simplified Code Ends ------------->

Bye. -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6482571
ok, will test the code...never heard of OnTime.. guess I'll have to look it up.

btw, thanks everyone! wish me luck.
0
 
LVL 8

Author Comment

by:starl
ID: 6482587
wait a minute.. I only have 2 books, not 3... and the second has a workbookopen...
It's gonna take some major processing on my side to see how to make this work..
honestly, last night, I was thinking of dropping the code from book 2 once the timesheet is filled out.. it would make a lot of things easier...
0
 
LVL 8

Author Comment

by:starl
ID: 6482595
If anyone wants to hold my hand and take babysteps with me - it's deffy worth the points.
to reiterate:
Book 1 calls Book 2
Book 2 opens up and runs a WorkBook_Open macro which goes back to Book 1 which goes back to Book 2 which goes back...you get it.

My current plan is to drop the WorkBook_Open macro in Book 2 (once the timesheet is filled out and returned). That way, Book 1 opens Book 2, but Book 1 continues running the macro instead of switching. Yousee, I wondered if part of the problem was due to the fact that I was still within the very first macro.. (a For loop)
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6482748
Hi, starl.

(morning here)

#Assumptions
1. WorkBook_Open macro is on [Book2]
2. Book1 = "TYSEXMPT.XLS"; Book2 = "NBACH.XLS" (changes)
3. The WorkBook_Open macro on [Book2] calls the Transfer_Times macro on [Book1].
4. You want to keep the code where it is.
5. "Variables" is the first sheet in [book1].

#Requests
Perhaps, if you drew a progress map, then I could understand the back-and-forth stuff a little better.

Right now, I see it like this:

Book1.(SomeCode?)  ->  Book2.WorkBook_Open
Book2.WorkBook_Open  ->  Book1.Transfer_Times

what happens next?

#Try This
1. Rename the WorkBook_Open macro to Auto_Open and cut-and-paste it into a module.
2. Change the code in [book1] that opens the spreadsheet and runs the code to look something like this:

' Opens the [book2] spreadsheet.
' Remember: Since you renamed the WorkBook_Open macro to Auto_Open, it will not run automatically.
Workbooks.Open FileName:="c:\temp\book2.xls"
Windows("book2.xls").Activate
' This will run the Auto_Open subroutine.
ActiveWorkbook.RunAutoMacros xlAutoOpen

3. You don't seem to need the OnTime method - even though I think it's cool.
4. Actually, using the Auto_Open subroutine in the module allows you to use your original code.
In other words, your OpenSheets code from {09/13/2001 10:30AM PST} should work just fine from within (or being called by) the Auto_Open subroutine.

Bye. -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6482862
I'll try this all a little later (middle of something else), but to answer your questions:
1-4 are correct.
5. Variables is a second sheet - but does it matter since it's named? and it'll usually be hidden.

>>what happens next.
how about I post Transfer_Times??? I've posted everything else *lol* but no criticism on my code! Suggested improvement, yes, of course - but be nice!

hell - here's basically all the code that's left that I haven't pasted! Some things obv commented out as I tried a variety of corrections. Basically what Transfer_Times does is grab certain lines from Book2 and pastes them in Book1.
Find_location is supposed to find the section set aside for each user to start pasting (it not working 100%) yet. And obv, OpenSheets was supposed to open each sheet individually - this is the macro that stopped all the other from working properly.

Const Administrator = "L"
Const Programmer = "S"
Const Boss = "J"
Const adminPW = "time"

Const savedrv = "\\G_M\SYS"
Const savepth = "\users\engr\common\timesht\"
Const lsavedrv = "D:"
Const lsavepth = "\timesht\"
Const exempt = savedrv & savepth & "forms\tysexmpt.xls"
Const finalsheet = "tysexmpt.xls"

Sub Transfer_Times(time_sheet)
'time_sheet = lsavedrv & lsavepth & time_sheet
'Find_location
'Find_location (time_sheet)

'Workbooks(finalsheet).Activate
Application.Windows(finalsheet).Activate
Worksheets("Exempt").Activate
numofrows = 18 'number of rows on sheet, counting the first 2 as one and not the total row
markercol = 23 'col w = how the rows that have totals are marked.
total_row = 19
sel_row = Range("countrow").Value 'keeps track on indytimesheet
pasterow = Range("pasterow").Value 'keep track on maintimesheet
firstrow = pasterow
row_9012 = 17
row_9025 = 18
'adminPW = "time"
Workbooks(time_sheet).Activate
Range("test9012").Value = "false"
Range("test9025").Value = "false"

'transfer will always start on row 7 b/c need to deffy grab top two rows
Windows(finalsheet).Activate
Cells(pasterow, 1).Select
'Selection.EntireRow.Insert
'Selection.EntireRow.Insert

Windows(time_sheet).Activate
Worksheets("Timesheet").Activate
ActiveSheet.Unprotect Password:=adminPW

Range(Cells(sel_row, 1), Cells(sel_row + 1, 22)).Select 'grabs first two emp rows from timesheet
Selection.Copy
Windows(finalsheet).Activate
Range(Cells(pasterow, 1), Cells(pasterow, 1)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
pasterow = pasterow + 2
sel_row = sel_row + 2
Range(Cells(pasterow, 1), Cells(pasterow, 1)).Select
Selection.EntireRow.Insert
Workbooks(time_sheet).Activate
ActiveSheet.Shapes("Init").Select
Selection.Copy
Workbooks(finalsheet).Activate
Range(Cells(pasterow - 1, 1), Cells(pasterow - 1, 1)).Select
ActiveSheet.Paste
Workbooks(time_sheet).Activate

For i = 7 To numofrows 'grabs rows w/times
    If Range(Cells(i, markercol), Cells(i, markercol)).Value = "yes" Then
   
        If i = row_9025 Then 'checks if row grabbed is 9025
            Range("test9025").Value = True
        End If
       
        If i = row_9012 Then 'checks if row grabbed is 9012
            Range("test9012").Value = True
        End If
       
        Range(Cells(i, 1), Cells(i, 22)).Select
        Selection.Copy
        Windows(finalsheet).Activate
        Range(Cells(pasterow, 1), Cells(pasterow, 1)).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
'        sel_row = sel_row + 1
        pasterow = pasterow + 1
        Application.CutCopyMode = False
        Range(Cells(pasterow, 1), Cells(pasterow, 1)).Select
        Selection.EntireRow.Insert
        Workbooks(time_sheet).Activate
    End If
Next i

If Range("test9025").Value = False Then 'add 9025 if not already there
    Workbooks(time_sheet).Activate
    Range("proj9025").Select
    Selection.Copy
    Windows(finalsheet).Activate
    Range(Cells(pasterow, 1), Cells(pasterow, 1)).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    Workbooks(time_sheet).Activate
    Range("test9025").Value = True
    pasterow = pasterow + 1
    Windows(finalsheet).Activate
    Range(Cells(pasterow, 1), Cells(pasterow, 1)).Select
    Selection.EntireRow.Insert
End If

Workbooks(time_sheet).Activate
Range(Cells(total_row, 1), Cells(total_row, 22)).Select 'grabs total row
Selection.Copy
Windows(finalsheet).Activate
Range(Cells(pasterow, 1), Cells(pasterow, 1)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False 'add an underline under the total row
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
lastrow = pasterow
pasterow = pasterow + 1
Application.CutCopyMode = False
Range("pasterow").Value = pasterow

If (pasterow - firstrow < 5) Then 'checks to see if there are enough rows if not, add 9012
    Windows(finalsheet).Activate
    Range(Cells(lastrow, 1), Cells(lastrow, 1)).Select
    Selection.EntireRow.Insert
    Workbooks(time_sheet).Activate
    Range("proj9012").Select
    Selection.Copy
    Windows(finalsheet).Activate
    Range(Cells(lastrow, 1), Cells(lastrow, 1)).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    pasterow = pasterow + 1
    Range("pasterow").Value = pasterow
End If
Windows(finalsheet).Activate
End Sub
'Sub Find_location()
Private Sub Find_location(time_sheet)
Windows(finalsheet).Activate
Worksheets("Variables").Activate
Range("A1").Select
firstrow = 1
lastrow = 10
namecolumn = 4
firstcellcolumn = 5
time_sheet = LCase(time_sheet)

For i = firstrow To lastrow
    Name = Range(Cells(i, namecolumn), Cells(i, namecolumn)).Value
    If time_sheet = Name Then
        startrow = Range(Cells(i, firstcellcolumn), Cells(i, firstcellcolumn)).Value
        Range("pasterow").Value = startrow
    Exit Sub
    End If
Next i

End Sub

Sub OpenSheets()
'Worksheets("Variables").Activate
ActiveWorkbook.Sheets("Variables").Activate
firstrow = 1
lastrow = 10
namecolumn = 4
firstcellcolumn = 5

For i = firstrow To lastrow
    Name = Range(Cells(i, namecolumn), Cells(i, namecolumn)).Value
    Workbooks.Open lsavedrv & lsavepth & Name
'            Application.Run "TYSEXMPT.XLS!Transfer_Times", time_sheet
    Name = UCase(Name)
'    Application.Run Name & "!OpenT"
            Application.Run "NBACH.XLS!Sign_Return", time_sheet

    Application.Run "NBACH.XLS!OpenT"

Next i

End Sub
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6483051
Hi, starl.

(This is posted just to re-iterate what I was trying to say in my previous comment.  I'm posting this to clarify.  I am also still reading through your code to see if there might be something that I need to test.)

The more that I experiment with Auto_Open vs WorkBook_Open, the more that I become convinced that this may have been the problem all along.

It appears that the program flow should be like this:

Book1.(SomeCode?)  ->  Book2.Auto_Open  [instead of WorkBook_Open]
Book2.Auto_Open  ->  Book1.Transfer_Times
etc.

Code that runs in my Auto_Open subroutine (in the module) will not run correctly in the WorkBook_Open subroutine (in ThisWorkBook).

For example:
This code runs perfectly in Auto_Open.  This same code errors out if pasted into the WorkBook_Open event.
<----------- Sample Code Begins ---------------->
Sub Auto_Open()
    Workbooks("book1.xls").Activate
    Worksheets("Variables").Activate
    For i = 1 To 6
        uValue = Range(Cells(i, 1), Cells(i, 1)).Value
        If VarType(uValue) = vbEmpty Then
            ' Where I've defined a name "pasterow" as D1.
            Range("pasterow").Value = Now
            Exit Sub
        End If
    Next i
   
End Sub
<----------- Sample Code Ends ---------------->

Also, if you call subroutines from within Auto_Open, they will work just as well.

Bye. -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6483094
Guess I'm a little worried since I don't know much about Auto_Open. Whats the difference between that and Workbook_Open?
my workbook open does more than run transfer times.. it checks the user name, from that, makes a decision which procedure(s) in an if-then loop to run, a few msgboxes...sets a few variables for later use...

guess the only thing for me to do is give it a try. I will after lunch.. trying to get some other stuff done.
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6483168
Hi, starl.

>Guess I'm a little worried since I don't know much about >Auto_Open. Whats the difference between that and
>Workbook_Open?

Auto_Open:
1. By default, this will run automatically when you load in the workbook file (xls) manually.
2. By design, it will _not_ run automatically when you load it programmatically.  We use the RunAutoMacros method to "run" it instead.
3. Select works when used from within Auto_Open or any subroutine that was called by Auto_Open.

WorkBook_Open:
1. By default, this will run automatically when you open the spreadsheet.  It doesn't matter if it is manual or programmatic.  This event will fire either way.
2. Select does _not_ work when used from within WorkBook_Open or any subroutine that was called by WorkBook_Open.
3. Perhaps, the reason that the code has such a hard time using other workbooks is because this event is running at the worksheet level.  (just a guess)

>my workbook open does more than run transfer times..

It appears that you could simply paste your WorkBook_Open code into the Auto_Open method (in a module) without changing anything.  Just realize that you may also have to copy the constants into the module.

Bye. -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6483244
ok...
the average user will open book2 manually - the macro needs to run. the secretary will run book1 which will open book 2 programmatically...macro needs to run there too - unless I do do away with the macro after the user fills out the page...
I think that's the way I wanna go.
which means that the macro in book 2 won't have to run if it's opened through book 1.
did I lose you?
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6483269
No, you did not lose me, starl.  -e2
0
 
LVL 8

Author Comment

by:starl
ID: 6483728
well, I think I found a way around the entire thing. I've removed the auto macro after the workbook is processed. I'm still rewriting some parts of it, but those are related to other probs.
Though, with my luck and everything else I still need to do to this workbook..
I'll be back.

thanks for being so patient.

btw, I am using eddie's suggestion - just to play it safe - and I suppose it's a good idea just in case, somehow, wonders of wonders, my secretary is able to start the work workbook or something...
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6483744
Hi, all.

I can't forget manf788265.  He reminded me that Auto_Open was placed in the module.  (Duh, me dummy!)

Please find your points Q here:
http://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=20183067

Bye. -e2
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6484530
hi starl, i wasn't ignoring :O)
-couldn't reproduce at first
-so i didn't have a clue
-and besides q2eddie had already put in so much time it would be rather stupid to just jump in, if i ever could've made a worthy contribution
-and after all you got a working solution
-i admire q2eddie for being so persistent
-have a enjoyable weekend
:O)Bruintje
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How many times recently have you prepared a presentation or emailed a document to a client and you have found that they have older versions of MS Office and they can not open the file you have prepared.  Although most visitors to this site are exper…
Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now