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
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