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

 

[RoryA]

Looking at that other thread, I suspect you may have missed the point of the wb suggestion. If you specifically used wb in your processing code, it would not matter at all which workbook was active. At any rate, you could use:

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

 

[Felix Atagong]

Just tried it. Doesn't work. It is still the first workbook that gets the attention, not the second.

 

[RoryA]

Where is that code located and what is the processing code? I would suggest that you need to amend the processing code so that you pass it a reference to the specific workbook to use and then amend the code to specify that workbook, but without seeing the code in question, it's impossible to be more specific.

 

[Felix Atagong]

The fourth line is in fact
BladNaam = Application.GetOpenFilename("Beta File, *.xls", , "Beta")
AND
the first Excel page, containing the macro and opening the second file, has got exactly the same name
Beta.xls...

Could it be that?

 

[RoryA]

You can't have two files open with the same name so I'm not sure I follow you.

 

[Felix Atagong]

No, no... both files have different names, but the caption in the getopenfilename line just mentions the name.

Beginning of the code (that is where it crashes)...

Option Explicit
Dim y, LaatsteRij, Lengte, RAy, Ltest, Lijn, Landentest As Integer
Dim x As Long
Dim ww(0 To 27), mm(0 To 45), Zoek, WAITtIME
Dim ESp, ENu, Wisselwaarde, BladNaam, TabNaam As String
Dim newHour, newMinute, newSecond As Integer
Dim WshShell As Object
Dim wbk As Workbook
 
Sub Beta()

Set WshShell = CreateObject("WScript.Shell")
ChDir (WshShell.SpecialFolders("MyDocuments"))

BladNaam = Application.GetOpenFilename("Beta File, *.xls", , "Beta")

If BladNaam = "False" Then
Exit Sub
End If

Set wbk = Workbooks.Open(FileName:=BladNaam)
wbk.Activate

MsgBox (BladNaam) 'it gets wrong here already, the msgbox shows the wrong file!

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="BetaBRTemp.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=True
Application.DisplayAlerts = True
        
BladNaam = ActiveWorkbook.Name
TabNaam = ActiveSheet.Name

Cells.Replace What:=Chr(34), Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False
Sheets.Add
ActiveSheet.Name = "Felix"
Sheets(TabNaam).Activate
    
LaatsteRij = Range("A33333").End(xlUp).Row
If Range("B33333").End(xlUp).Row > LaatsteRij Then
LaatsteRij = Range("B33333").End(xlUp).Row
End If

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="BetaTemp.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=True
Application.DisplayAlerts = True

 

[RoryA]

Which workbook do you select here:

BladNaam = Application.GetOpenFilename("Beta File, *.xls", , "Beta")

and what does the Msgbox BladNaam line show?

 

[Felix Atagong]

It shows
Beta.xls
what is the first Excel file that contains the macro and not (for instance)
Definitive Listing 2008 11 04.xls
the file that we opened with the workbooks.open command...

 

[RoryA]

I don't see how that is possible since you just opened a workbook using that variable, unless your code is in the ThisWorkbook module or a worksheet module and you have an event procedure that is changing the value of BladNaam. Have you tried moving the Dim statements inside the subroutine?