I have just been upgraded to 2013 and my new machine is an IMB Surface Pro 3
Programmed spreadsheets that have worked in previous versions (2010) have stopped working with multiple errors.
I went through the active x problem and removed all buttons and replaced them with form buttons
ChDir doesn't work.
I cannot set a dimmed variable as a workbook to a name which throws out a subscript out of range error
I cannot select a named sheet again with a subscript out of range error (this affects the sheet with axtive x buttons now removed)
when I attempt an open workbooks it just spins with no response
I'm very disappointed. Is anyone experiencing a similar issue?
my code is below and has been working faultlessly for 4 years.
Sub OpenReportFileDetail() '***Opens a selected SAP derived Excel file and '***Copies data to the "Data" sheet of this workbook '***Calls the processing routine to range name sections Dim DataFile As Variant Dim Ans As String Dim a As String Dim m As Workbook Dim EndRowDat As Single 'Set the current path and workbook names Application.ScreenUpdating = False ChDir ThisWorkbook.Path Mainfile = ActiveWorkbook.Name Filter1 = "Excel Files (*.xlsx),*.xlsx, (*.xls),*.xls" FilterIndex1 = 1 Title = "Select XML File for input" ' Get the input XML detail report DataFile = Application.GetOpenFilename(Filter1, FilterIndex1, Title) If DataFile = False Then MsgBox "No DataFile was selected", vbInformation, "Nothing to Open" Exit Sub End If Ans = MsgBox("You selected " & _ Chr(13) & Chr(9) & DataFile & _ Chr(13) & Chr(13) & "Is this correct?" _ , vbYesNo, "Check correct File is selected") If Ans = vbNo Then Exit Sub 'If file is open then close it a = InStr(StrReverse(DataFile), Application.PathSeparator) DataFileNm = Right(DataFile, a - 1) On Error Resume Next Set m = Workbooks(DataFileNm) If Err.Number = 0 Then ' Close Excel file Application.DisplayAlerts = False Windows(DataFileNm).Close Application.DisplayAlerts = True End If On Error GoTo 0 'reset error trap 'Open the report and get the data Workbooks.Open Filename:=DataFile 'Application.ScreenUpdating = False Application.StatusBar = "Retrieving data from " & DataFile Windows(Mainfile).Activate Sheets("Data").Select Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True Sheets.Add ActiveSheet.Name = "Data" Range("a1").Select Cells.Clear On Error Resume Next Workbooks.Open Filename:=DataFile DataFileNm = ActiveWorkbook.Name On Error GoTo Endit 'Copy new data from new file Application.StatusBar = "Copying Data" Cells.Select Selection.Copy Windows(Mainfile).Activate ActiveSheet.Paste Selection.ClearOutline Application.StatusBar = "Data pasted" 'Close XML file Application.DisplayAlerts = False Windows(DataFileNm).Close Application.DisplayAlerts = True Application.ScreenUpdating = True Application.StatusBar = "Datafile closed, copy successful" 'Format the values as number, negative, commer, two decimal Sheets("Data").Select EndRowDat = LastRow(ActiveSheet) Range("F1:F" & EndRowDat).Select Selection.NumberFormat = "#,##0.00_ ;[Red](#,##0.00) " Range("A1").Select 'Change the colours TranColumn = 5 TranLetter = "F" Call FixMyColour 'Extra step to adjust data Columns("D:D").Select With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With 'Subroutine to create the range names for COst Centre in the current report Call BuildTranRanges 'Finish by recording when this step occurred Application.StatusBar = False Application.ScreenUpdating = True Sheets("Controll").Activate Range("Msg_RepDet").Select ActiveCell.Value = " The period transaction report import was last run at " & Now() Range("a1").Select Exit Sub Endit: MsgBox "An error occurred." & _ Chr(13) & "Close all files and try again." & _ Chr(13) & Chr(13) & "Close all files and try again." _ , vbCritical, "Process Failure" End Sub