Felix Atagong
Active Member
- Joined
- Jun 27, 2003
- Messages
- 359
I have a workbook (Me.xls) that opens a second one and does some 'data mining'.
The following VBA is used for that:
The problem is that in some rare cases the newly opened workbook is NOT activated (i.e put on top) and thus the rest of the code is executed on the wrong active sheet from the wrong active workbook...
It is apparently not the PC that acts too slow because the same page gives the same error on different machines with different speeds (Excel 2000).
I put in a Msgbox(ActiveWorkbook.Name) and indeed in 9 out of 10 times the active workbook is the one that was first opened and not the second...
Is there a way around that?
The following VBA is used for that:
Code:
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 in some rare cases the newly opened workbook is NOT activated (i.e put on top) and thus the rest of the code is executed on the wrong active sheet from the wrong active workbook...
Code:
Cells.Replace What:=Chr(34), Replacement:=" ", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
Sheets.Add
ActiveSheet.Name = "xyzabc"
Sheets(TabNaam).Activate
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="MeTemp.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=True
Application.DisplayAlerts = True
' LOTS OF CODE AFTER THAT
It is apparently not the PC that acts too slow because the same page gives the same error on different machines with different speeds (Excel 2000).
I put in a Msgbox(ActiveWorkbook.Name) and indeed in 9 out of 10 times the active workbook is the one that was first opened and not the second...
Is there a way around that?