Excel 2010 Windows.Activate name issue

  • Thread starter Thread starter stainless
  • Start date Start date
S

stainless

I am desperate fo an answer to an issue I have discovered in Excel
2010. We are doing rapid fixes to Excel 2003 VBA macros for a number
of reports to make these compatible with Excel 2010. All was going
well until a real customer (rather than we developers/testers) managed
to make my code fail! The issue is rare and seems to only happen for a
handful of customers but we cannot explain why it occurs. We were
replacing "Move" sheets code that always failed in 2010.

The symptoms are as follows:

My code is below:

Sub MoveRawDataSheet(ByVal strEnvironment As String, _
ByVal strFileName As String, _
ByVal boolCopyAfter As Boolean, _
ByVal intDestSheetNo As Integer, _
ByVal boolXlsFile As Boolean)

' Notes on use:
' strEnvironment - this is one of Development/ModelOffice/Training/
Production
' strFileName - this is the sheet name being copied from
' boolCopyAfter - this defines whether the sheet is copied After
the destination sheet
' If false, the default will be Before
' intDestSheetNo - this is the number of the destination sheet to
be used in the Copy Before/After
' boolXlsFile - this defines whether the macro sheet is an xls
file as these behave slightly
' differently to xlt files.

' All above must be populated - no defaults will be used

Dim strRawDataName As String
Dim strRawDataSheetName As String
Dim wbook As Workbook
Dim actualWorkbookName As String

' Behaving slightly differently for .xls file macro
Select Case strEnvironment
Case "Development"
strRawDataName = strFileName
Case Else
strRawDataName = "getfile.aspx"
End Select

Windows(strRawDataName).Activate
Cells.Select
Sheets(1).Select
strRawDataSheetName = Sheets(1).Name
Cells.Select
Selection.Copy
Windows(ThisWorkbook.Name).Activate
Sheets(intDestSheetNo).Select
Select Case boolCopyAfter
Case True
Sheets.Add After:=Sheets(intDestSheetNo)
Case False
Sheets.Add
End Select
Cells.Select
ActiveSheet.Paste
ActiveSheet.Name = strRawDataSheetName
ClearClipboard
Windows(strRawDataName).Close

End Sub


The Windows(ThisWorkbook.Name).Activate should activate the
current sheet that contains the macro. In 99% of cases, this has
worked perfectly. We piloted one report in our production environment
and a few customers reported that this failed in Excel 2003.

On connecting to their machines, it looked to me that, on failure, the
name above the actual workbook (i.e. the sheets) did not match the
name above the macro. For example, the macro had the title
WalkReport.xls whereas, when I viewed the worksheets, the name above
these was WalkReport without the .xls. I ran this on my machine, and
mine appeared to have the .xls suffix on my worksheet view.

This caused the Windows(ThisWorkbook.Name).Activate statement to fail
for these few customers.

The theory above was simply based on viewing this when it failed on
their machine. It, could of course have been the other way around
(i.e. although the macro showed WalkReport.xls, actually internally it
was being held as WalkReport so that ThisWorkbook.Name had the value
without the .xls - maybe the worksheet view itself was not displaying
a true reflection of the name).

The one thing that is certainly true is that ThisWorkbook.Name is not
working for a handful of customers.

I attempted some code to get around this but, as we are unable to
create the situation ourselves, we could only ask a real customer who
had the failure to try this during their busy working day. Debugging
these is near enough impossible. My replacement code for the Activate
statement is below:


'Need to deal with issue regarding some customers reports having
'workbook name opened that does not match the .xls name that
'the macro appears to run under
Set wbook = Workbooks(ThisWorkbook.Name)
If wbook Is Nothing Then
actualWorkbookName = Replace(ThisWorkbook.Name, ".xls", "")
'assume that this can be the only other name option but am I
sure?
Else
actualWorkbookName = ThisWorkbook.Name
End If
Windows(actualWorkbookName).Activate

I am afraid this failed for them, even though I did test on my own
machine and mine was fine as wbook was NOT nothing. It was found,
thus suggesting that his report will have satisfied the "wbook is
Nothing" (may be a leap to state this but looks likely to me).

It may be a setting in their installation of Excel 2003, in which
case, we could simply get settings changed for the handful of
customers but currently, have no idea if this is the case.

Any suggestions will be gratefully accepted as I am really up aginst
it on this one. The code has to work for all Excel 2003 and 2010
customers. We cannot rewrite all our code and only have time to deal
with amending this code.

Cheers

Mark
 
Correction:

The issue shows itself within Excel 2003 currently. It may happen in
some instances of Excel 2010 but this is yet to be rolled out for our
customers and thus, we are unsure. Any solution must be compatible for
both
 
As extra background into why we had to put in replacement code, we had
several reports where the sheet name was moved with code such as this
below:

ActiveWorkbook.Sheets(1).Select
ActiveWorkbook.Sheets(1).Move
After:=ThisWorkbook.Sheets(numFile + 1)

This would fail in Excel 2010 with the following message (when we
captured it correctly):

Excel cannot insert the sheets into the destination workbook because
it contains fewer rows and columns than the source workbook


I found that someone else had had a similar issue in one of the online
groups and thus, used a variation of their solution as my code. And as
intimated earlier, I thought this had cracked it....
 
Correction:

The issue shows itself within Excel 2003 currently. It may happen in
some instances of Excel 2010 but this is yet to be rolled out for our
customers and thus, we are unsure. Any solution must be compatible for
both

you can always use ThisWorkbook.Activate
you don't need to reference the window since you have the workbook
object
 
I am desperate fo an answer to an issue I have discovered in Excel
2010. We are doing rapid fixes to Excel 2003 VBA macros for a number
of reports to make these compatible with Excel 2010. All was going
well until a real customer (rather than we developers/testers) managed
to make my code fail! The issue is rare and seems to only happen for a
handful of customers but we cannot explain why it occurs. We were
replacing "Move" sheets code that always failed in 2010.

The symptoms are as follows:

My code is below:

Sub MoveRawDataSheet(ByVal strEnvironment As String, _
ByVal strFileName As String, _
ByVal boolCopyAfter As Boolean, _
ByVal intDestSheetNo As Integer, _
ByVal boolXlsFile As Boolean)

' Notes on use:
' strEnvironment - this is one of Development/ModelOffice/Training/
Production
' strFileName - this is the sheet name being copied from
' boolCopyAfter - this defines whether the sheet is copied After
the destination sheet
' If false, the default will be Before
' intDestSheetNo - this is the number of the destination sheet to
be used in the Copy Before/After
' boolXlsFile - this defines whether the macro sheet is an xls
file as these behave slightly
' differently to xlt files.

' All above must be populated - no defaults will be used

Dim strRawDataName As String
Dim strRawDataSheetName As String
Dim wbook As Workbook
Dim actualWorkbookName As String

' Behaving slightly differently for .xls file macro
Select Case strEnvironment
Case "Development"
strRawDataName = strFileName
Case Else
strRawDataName = "getfile.aspx"
End Select

Windows(strRawDataName).Activate
Cells.Select
Sheets(1).Select
strRawDataSheetName = Sheets(1).Name
Cells.Select
Selection.Copy
Windows(ThisWorkbook.Name).Activate
Sheets(intDestSheetNo).Select
Select Case boolCopyAfter
Case True
Sheets.Add After:=Sheets(intDestSheetNo)
Case False
Sheets.Add
End Select
Cells.Select
ActiveSheet.Paste
ActiveSheet.Name = strRawDataSheetName
ClearClipboard
Windows(strRawDataName).Close

End Sub


The Windows(ThisWorkbook.Name).Activate should activate the
current sheet that contains the macro. In 99% of cases, this has
worked perfectly. We piloted one report in our production environment
and a few customers reported that this failed in Excel 2003.

On connecting to their machines, it looked to me that, on failure, the
name above the actual workbook (i.e. the sheets) did not match the
name above the macro. For example, the macro had the title
WalkReport.xls whereas, when I viewed the worksheets, the name above
these was WalkReport without the .xls. I ran this on my machine, and
mine appeared to have the .xls suffix on my worksheet view.

This caused the Windows(ThisWorkbook.Name).Activate statement to fail
for these few customers.

The theory above was simply based on viewing this when it failed on
their machine. It, could of course have been the other way around
(i.e. although the macro showed WalkReport.xls, actually internally it
was being held as WalkReport so that ThisWorkbook.Name had the value
without the .xls - maybe the worksheet view itself was not displaying
a true reflection of the name).

The one thing that is certainly true is that ThisWorkbook.Name is not
working for a handful of customers.

I attempted some code to get around this but, as we are unable to
create the situation ourselves, we could only ask a real customer who
had the failure to try this during their busy working day. Debugging
these is near enough impossible. My replacement code for the Activate
statement is below:


'Need to deal with issue regarding some customers reports having
'workbook name opened that does not match the .xls name that
'the macro appears to run under
Set wbook = Workbooks(ThisWorkbook.Name)
If wbook Is Nothing Then
actualWorkbookName = Replace(ThisWorkbook.Name, ".xls", "")
'assume that this can be the only other name option but am I
sure?
Else
actualWorkbookName = ThisWorkbook.Name
End If
Windows(actualWorkbookName).Activate

I am afraid this failed for them, even though I did test on my own
machine and mine was fine as wbook was NOT nothing. It was found,
thus suggesting that his report will have satisfied the "wbook is
Nothing" (may be a leap to state this but looks likely to me).

It may be a setting in their installation of Excel 2003, in which
case, we could simply get settings changed for the handful of
customers but currently, have no idea if this is the case.

Any suggestions will be gratefully accepted as I am really up aginst
it on this one. The code has to work for all Excel 2003 and 2010
customers. We cannot rewrite all our code and only have time to deal
with amending this code.

Cheers

Mark

You can always use ThisWorkbook.Activate
no need to reference the window when you have the Workbook object
 
Back
Top