Excel VBA:Dealing with two opened workbooks

[Mrall]

I have written a module that opens another workbook. No problem. I have another module that switches the active window and copies some cells from it and than returns control to the previous window. This works just as it is, but will error out on a line if I save it as an addin or if I compile it. I set the following variable to remember the current workbook.

curwbname = ThisWorkbook.Name

At the end of the process I want to return control back the the workbook using the follow code.

Windows(curwbname).Activate \\This is where the error occurs
Sheets("Edit").Select

Why is this erroring out when it is saved as a Addin or if I compile the excel file.

 

[SkipVought]

hi,

What is the VALUE of curwbname when this error occurs?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

ThisWorkbook is a workbook where the code that calls it exist. If you save a workbook with the code as an add-in, ThisWorkbook refers to it. The add-in has no window, so the error.

combo

 

[PHV]

You have to understand the difference between ThisWorkbook and ActiveWorkbook ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Mrall]

The error that comes up is subscript out of range for the following line.

Windows(curwbname).Activate

 

[SkipVought]

Helllllooooo???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mrall]

The value of curwbname is 0 in the compiled version, however it is "excelfile.xsm" in the spread sheet before compiling it and it run just fine.

 

[SkipVought]

THAT is your problem!

You need to debug your code to determine why curwbName is not assigned properly.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

As suggested in post timestamped 14 Jul 12 16:27 did you try this ?
curwbname = ActiveWorkbook.Name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Mrall]

Sorry about my response time, I travel alot with my work and don't alway have time to check this update.

I tried changing to:

curwbname = ActiveWorkbook.Name

But I get the same error.


As "Combo" stated
ThisWorkbook is a workbook where the code that calls it exist. If you save a workbook with the code as an add-in, ThisWorkbook refers to it. The add-in has no window, so the error.

Is there some sort of work around? Is there a way to maybe create a window that can be accessed to get to the complied spreadsheet?

 

[SkipVought]

Why use the Activate & Select method, when it's an add-in? It should be a rare occasion to have to use these methods.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

As Skip stated, if you just need to access data in the add-in and by the code compiled in it, use:
[tt]x=thisworkbook.worksheets("Edit").Range("A1")[/tt]

combo

 

[Mrall]

I was able to resolve the issue by simply getting rid of the curwbname variable. The value always remains the same so no variable is nessary.

Windows("mywbtool.exe").Activate

Thanks so much