Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010 Windows.Activate name issue
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010 Windows.Activate name issue
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010 Windows.Activate name issue
On Mar 14, 11:09*am, stainless wrote:
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.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010 Windows.Activate name issue
On Mar 14, 1:09*pm, stainless wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010 Windows.Activate name issue
On Wednesday, March 14, 2012 12:37:59 PM UTC+2, stainless wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart issue--works in 2007, not in 2010 | Charts and Charting in Excel | |||
Excel 2003 with Windows Seven printing issue | Excel Discussion (Misc queries) | |||
how can Activate Microsoft Office 2010? | Excel Discussion (Misc queries) | |||
Windows().Activate vs Workbooks().Activate | Excel Programming | |||
Windows.Activate | Excel Programming |