Variable set, then not set 2

[merlynsdad]

I'm moving data between Excel workbooks. I have the following variables declared at the module level, but just to make sure, I've also got them declared in this sub as well.

Dim wb As Workbook
Dim strName10 As String
Dim strPath10 As String
Dim strName11 As String
Dim strPath11 As String

I have file names and paths for the strName and strPath variables, e.g. strName10 = "myfile.xls"

I'm setting wb as follows:

For Each wb In Application.Workbooks
If wb.Name = strName10 Then
Set wb = Workbooks(strName10)
wb.Activate
ElseIf wb.Name = strName11 Then
Set wb = Workbooks(strName11)
wb.Activate
End If
Next wb

I then do some work, come back to the workbook running the code with

Me.Activate

do some more work, then try to go back to the other workbook with

wb.Activate

At this point I'm getting an "Object Variable or With Block..." error. Since wb was set and activated successfully in the IF THEN statement (I think), why won't it activate now?

Any help is greatly appreciated. Thanks.

 

[bubba100]

If your moving data from one file to another and you want to WATCH, then you don't need to "Activate".

 

[SkipVought]

hi,

I have the following variables declared at the module level, but just to make sure, I've also got them declared in this sub as well.

"Just to be sure" may get you in trouble, as a procedure-level declaration OVERRIDES the module-level variable and makes THAT variable "invisible" Only your procedure-level variable is being used!

Now for your code. BEFORE this code, you have already, it seems set the wb object. Is THIS object ALSO declared at the MODULE level??? If so, you are not seeing it!!!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[merlynsdad]

I don't want to watch, except when I'm stepping through it. Normally it's totally transparent to the user. They don't even know the underlying worksheets exist. The only ActiveSheet they see is the one running the code.

 

[SkipVought]

The only ActiveSheet they see is the one running the code.

Not when you use the Activate method to activate another workbook!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[merlynsdad]

Skip, OK I removed the procedure level declaration.

I'm working with data for either of two years, 2010 or 2011, but not both. What I'm trying to do is use the wb object to refer to whichever workbook, strName10 or strName11, is open at the time. At this point in the program only one of them should be open, but both are open initially. It was easier to open both and then, depending on which year's selection button is pushed, close the other one. That all happens before the user gets to this module, which works with whatever year's data is left.

The wb object has been set in a previous procedure via an IF THEN statement, and I'm trying to refer to that setting in this procedure. The wb object is dimensioned at the module level, but set at the procedure level. However, it's set in the prior procedure and in this procedure.

Am I making sense here???

 

[SkipVought]

Skip, OK I removed the procedure level declaration.

The wb object has been set in a previous procedure

Then the wb declared in the PREVIOUS procedure will not appear in THIS procedure, unless you pass wb as an argument.

You could have declared wb as a MODULE level variable ONLY, and it would work. Otherwise, you need to pass the variable to the appropriate procedure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

You should read up on Scope.


unknown

 

[SkipVought]

Is your Word application set up for macros to be disabled until verified, either by the user making a dicision or a digitally signed macro?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[merlynsdad]

Good catch on the Activate method. ActiveWindow is normally False as is ScreenUpdating when it's buttoned down, but Activate would mess that up.

 

[merlynsdad]

It's Excel, and it's not setup for macros to be disabled until verified.

 

[combo]

You need two extra variables to store workbooks found. In your code the 'For' loop clears the wb variable at the end. Try this code to see what's going on:

Dim wb As Workbook
For Each wb In Application.Workbooks
MsgBox wb.Name
Next wb
MsgBox wb.Name

combo

 

[merlynsdad]

I used the Watch box with the wb.name variable to see which workbooks showed up in the For Next loop and discovered that my wb variable did indeed die when the loop ended. I had a couple of SET commands downstream that weren't taking because of this, so moved them up into the loop so they took. Now it's running again. Thanks to everyone for your help on this. I'm relatively new at this, but I'm sure I'll have another question next week. Happy Thanksgiving.