Every morning after running a certain macro I wrote, I can't open any Excel files by double-clicking on them. The only way to open them is to drag them into a current session of Excel or to use File, Open.
Any idea what's wrong with my macro?
Every morning after running a certain macro I wrote, I can't open any Excel files by double-clicking on them. The only way to open them is to drag them into a current session of Excel or to use File, Open.
Any idea what's wrong with my macro?
Could you post the macro.
Mangesh
The entire macro is 12 pages long, but this section seems to have the problem. I would be incredibly grateful if you find the problem.
Tim
Sub GetData()
'
' Macro1 Macro
' Macro recorded 5/25/2004 by twzabel
'
'
Application.DisplayAlerts = False
FileTime = Hour(Now) & Minute(Now)
MyName = "Backup_Log_"
For x = 1 To 25
' Open file from GSM
z = x
If x = 24 Or x = 25 Then
x = 0
If z = 25 Then MyName = "Log_"
ActiveCell = OrigDate
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
CheckDate = ActiveCell
If Mid(CheckDate, 2, 1) = "/" Then
CheckMonth = "0" & Left(CheckDate, 1)
Else
CheckMonth = Left(CheckDate, 2)
End If
If Left(Right(CheckDate, 7), 1) = "/" Then
CheckDay = "0" & Left(Right(CheckDate, 6), 1)
Else
CheckDay = Left(Right(CheckDate, 7), 2)
End If
CheckDate = CheckMonth & "-" & CheckDay & "-" & Right(CheckDate, 4)
ActiveCell = Empty
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell = Empty
End If
If x < 10 Then
NewX = "0" & x
Else
NewX = x
End If
If z < 10 Then
NewZ = "0" & z
Else
NewZ = z
End If
On Error Resume Next
If FileTime = Hour(FileDateTime("M:\Departments\Dept256\SMT\GSM_Log\" & MyName & CheckDate & "_" & NewX & "-00.htm")) & Minute(FileDateTime("M:\Departments\Dept256\SMT\GSM_Log\" & MyName & CheckDate & "_" & NewX & "-00.htm")) Then GoTo NoFile
Workbooks.OpenText Filename:="M:\Departments\Dept256\SMT\GSM_Log\" & MyName & CheckDate & "_" & NewX & "-00.htm", Origin:= _
437, StartRow:=19, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=True, OtherChar:=">", FieldInfo:=Array(Array(1, 9 _
), Array(2, 9), Array(3, 1), Array(4, 9), Array(5, 1), Array(6, 9), Array(7, 9), Array(8, 9), _
Array(9, 1), Array(10, 9), Array(11, 1), Array(12, 9), Array(13, 9)), _
TrailingMinusNumbers:=True
On Error GoTo 0
If ActiveCell = 0 Then GoTo NoFile
FileTime = Hour(FileDateTime("M:\Departments\Dept256\SMT\GSM_Log\" & MyName & CheckDate & "_" & NewX & "-00.htm")) & Minute(FileDateTime("M:\Departments\Dept256\SMT\GSM_Log\" & MyName & CheckDate & "_" & NewX & "-00.htm"))
Columns("A:D").EntireColumn.AutoFit
Columns("D:D").Select
Selection.ColumnWidth = 100
Rows("1:1").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SplitRow = 1
ActiveWindow.FreezePanes = True
ActiveCell.FormulaR1C1 = "Date"
Range("B1").Activate
ActiveCell.FormulaR1C1 = "Time"
Range("C1").Activate
ActiveCell.FormulaR1C1 = "Code"
Range("D1").Activate
ActiveCell.FormulaR1C1 = "Text"
Columns("A:D").Select
Selection.Replace What:="</TD", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' Move worksheet to logging workbook
If z = 25 Then
Sheets(MyName & CheckDate & "_" & NewX & "-00").Copy Before:=Workbooks( _
"Log_" & OrigDate & ".xls").Sheets(1)
Sheets(MyName & CheckDate & "_" & NewX & "-00").Name = "Log_" & OrigDate & "_" & 24 & "-00"
Else
Sheets(MyName & CheckDate & "_" & NewX & "-00").Copy Before:=Workbooks("Log_" & OrigDate & ".xls").Sheets(1)
End If
Windows(MyName & CheckDate & "_" & NewX & "-00.htm").Activate
ActiveWindow.Close
If x = 0 Then
x = z
'NewX = "24"
End If
If z = 25 Then
Sheets(MyName & OrigDate & "_24-00").Name = x
Else
Sheets(MyName & CheckDate & "_" & NewX & "-00").Name = x
End If
' Move all data to Main_Log and strip out un-needed rows
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Main_Log").Select
ActiveSheet.Paste
Do Until ActiveCell = 0 And ActiveCell.Offset(1, 0) = 0 And ActiveCell.Offset(2, 0) = 0 And ActiveCell.Offset(3, 0) = 0 And ActiveCell.Offset(4, 0) = 0 And ActiveCell.Offset(5, 0) = 0 And ActiveCell.Offset(5, 0) = 0
If ActiveCell.Offset(0, 2) <> "Event - 30037" And ActiveCell.Offset(0, 2) <> "Event - 30044" Then
ActiveCell.Offset(0, 0).Range("A1:D1").Select
Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Range("A1").Select
y = y + 1
End If
Loop
NoFile:
On Error GoTo 0
If x = 0 Then x = z
Next
Application.DisplayAlerts = True
Range("A2:D" & y + 1).Sort Key1:=Range("C2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A2:D" & y + 1).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A2:D" & y + 1).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks