sorting the whole excel sheet.

The sort button in the excel tool bar will sort all the data until there is a column break. once it encounters the blank column it will not sort all data which is after that blank column

Taggings:

Submitted by Ajay Kumar Korupola on Wed, 12/10/2014 - 21:23
2 answers

Sort macro

created a macro which solves the problem.
this macro helps a lot while we are comparing and analysing huge data.

Macro:
Sort Macro
Sub SortData() 'Sort all the data in this book based on this column in ascending order.
'once sorted, go to the first cell of data in this column.

iDepth = iDepth + 1
iDepth = 1
Call FinalizeMacro
Call InitializeMacro 'Suspend screen updating & autocalculation & save current statusbar setting.
Call LogUsage("Conversions", "SortData") ' Log the use of this macro

Range("A1", ActiveSheet.Cells.SpecialCells(xlLastCell).Address()).Sort Key1:=Range(ActiveCell.Address), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveCell.EntireColumn().End(xlUp).Offset(1, 0).Select
Call FinalizeMacro 'Restore screen updating & autocalculation & reset previous statusbar setting.
iDepth = iDepth - 1
End Sub

Function FinalizeMacro()
If iDepth = 1 Then
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = bOldStatusBar
Application.ScreenUpdating = True
End If
End Function
Function InitializeMacro()
If iDepth = 1 Then
Application.Calculation = xlCalculationManual
bOldStatusBar = Application.DisplayStatusBar
Application.ScreenUpdating = False
End If
End Function
Public Sub LogUsage(sMacroSheet As String, sMacroName As String)
' Close #1: Open "c:\DataServMacroUsageReport.txt" For Append As #1: Print #1, Application.UserName & vbTab & macroSheet & vbTab & macroName & vbTab & Now: Close #1
End Sub

Submitted by Ajay Kumar Korupola on Wed, 12/10/2014 - 21:31

Taggings:

System Check

Solution: Used a simple VB code which uses ping command of c prompt and checks if all the systems are on or off then it would shoot a email to the systems which are online ( active ).
we have to give the ip address of all the systems on floor as the input and we can get the output of the status in an excel sheet or a notepad.then it shoots an email to the spoc (single point of contact) and the person who’s system is on via outlook.
sample code:

dim strInputPath, strOutputPath, strStatus
dim objFSO, objTextIn, objTextOut

strInputPath = "c:\serverlist.txt" '- location of input
strOutputPath = "c:\output.txt" '- location of output

set objFSO = CreateObject("Scripting.FileSystemObject")
set objTextIn = objFSO.OpenTextFile( strInputPath,1 )
set objTextOut = objFSO.CreateTextFile( strOutputPath )
objTextOut.WriteLine("computer,status")

Do until objTextIn.AtEndOfStream = True
strComputer = objTextIn.ReadLine
if fPingTest( strComputer ) then
strStatus = "On"
else
strStatus = "Off"
end if
objTextOut.WriteLine(strComputer & "," & strStatus)
loop

function fPingTest( strComputer )
dim objShell,objPing
dim strPingOut, flag
set objShell = CreateObject("Wscript.Shell")
set objPing = objShell.Exec("ping " & strComputer)
strPingOut = objPing.StdOut.ReadAll
if instr(LCase(strPingOut), "reply") then
flag = TRUE
else
flag = FALSE
end if
fPingTest = flag

end function

Submitted by Ajay Kumar Korupola on Wed, 12/10/2014 - 21:29

Taggings: