Workbooks.Open does not activate the workbook

[Felix Atagong]

I have a workbook containing macros that opens a second one (no macros) and does some 'data mining'. The following VBA is used for that (I use this code on dozen of files and it has always worked, except here):

Set WshShell = CreateObject("WScript.Shell")
ChDir (WshShell.SpecialFolders("MyDocuments"))
BladNaam = Application.GetOpenFilename("Excel File, *.xls", , "Excel")
Workbooks.Open FileName:=BladNaam
TabNaam = ActiveSheet.Name

The problem is that most of the time the newly opened workbook is NOT activated (i.e put on top) and thus the rest of the code is executed on the wrong workbook...

If I put in a Msgbox(ActiveWorkbook.Name) 9 out of 10 times the active workbook is the initial one and not the one that was opened by the code...

I tried different solutions as mentioned in the underneath thread
http://www.mrexcel.com/forum/showthread.php?t=348085
but all my attempts failed...

tried replacing Active.Workbook with wb (dim wb as workbook).
tried to wait-a-few-seconds in between code
tried renaming

The page simply refuses to get the macro's focus and it is driving me crazy

 

[Felix Atagong]

I thank you for the observation that this it impossible, but it happens, and always with this macro on this particular sheet. It doesn't happen however when you pass line by line with the F8 command - so there is nothing wrong with the code itself... but only when the macro runs (that is why I thought the macro ran too fast for the computer and I build in some pauses, but that didn't help either)...

I have used exactly the same code on at least 60 other Excel sheets (we use it for data transfer between companies) and they all work, except... on this one... and the error arrives on whatever computer the file opens (it is not related to one single PC)...

Perhaps the Excel sheet (Beta.xls) itself has got an unrecognised error somwhere, perhaps I should just create a new one and start coding again...

Thanks for your help... if I find a solution I will post it anyway...

 

[RoryA]

You didn't answer my question. Logically, the only way it can happen is that, since your variable is declared outside your routine, another routine is changing its value. Try moving the variable declarations inside the routine and see if the problem persists.

 

[Felix Atagong]

You didn't answer my question. Logically, the only way it can happen is that, since your variable is declared outside your routine, another routine is changing its value. Try moving the variable declarations inside the routine and see if the problem persists.

Done that finally, problem is still there.

Set wbk= Workbooks.Open(Filename:=Bladnaam)
wbk.Activate

This should activate the right workbook. But if I put as next line

Bladnaam = ActiveWorkbook.Name
Msgbox(Bladnaam)

...the box still gives the wrong workbook...