Hi All, I got this link http://spreadsheetpage.com/index.php...ess_indicator/ which would work great, but now I don't know how to incorporate with below code:
[VBA]Option Explicit
Public glb_origCalculationMode As Integer
Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub
Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub
Sub GetMyData()
Dim pFolder As String, fileList As Variant, f As Variant
Dim cr As Long, cs As Worksheet, ws As Worksheet
Dim slaveWB As Workbook, slaveCols() As Variant, masterCols() As Variant
Dim i As Integer, lr As Long
Dim StatusBarMsg As String
On Error Goto TheEnd
StatusBarMsg = "Running macro..."
SpeedOn
'Set the parent folder of slave workbooks to process.
pFolder = "C:\Documents and Settings\Shums\Desktop\BSE\Desktop\Updation" & "\" '<-------- Change as needed.
' Set the column names for the slave and master workbooks with 1-1 match.
' Both arrays must have the same number of elements.
masterCols() = Array("C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N")
slaveCols() = Array("B", "C", "D", "E", "F", "G", "H", "U", "AJ", "AW", "BE", "BS")
'Add a new sheet and name it with today's date:
Set cs = Worksheets.Add(After:=ActiveSheet, Count:=1)
cs.Name = Format(Date, "dd-MMM-yy")
' Add header:
Range("A1").Value = "Sr. No."
Range("B1").Value = "Scrip Name"
Range("C1").Value = "Open"
Range("D1").Value = "High"
Range("E1").Value = "Low"
Range("F1").Value = "Close"
Range("G1").Value = "Volume"
Range("H1").Value = "Changes Value"
Range("I1").Value = "Changes %"
Range("J1").Value = "EMA13"
Range("K1").Value = "RSI"
Range("L1").Value = "Remarks"
Range("M1").Value = "SMA 200"
Range("N1").Value = "Ultimate Oscillator"
Range("A2").Select
ActiveWindow.FreezePanes = True
' Open each workbook except thisworkbook and get the data.
cr = 1
fileList = GetFileList(pFolder & "*.xl*")
For Each f In fileList
If ThisWorkbook.Name = f Then Goto Nextf
'Do your thing from here to Nextf.
Set slaveWB = Workbooks.Open(pFolder & f)
'change message in StatusBar to show curent workbook name
Application.StatusBar = StatusBarMsg
StatusBarMsg = "Processing....." & slaveWB.Name
'Add the data from slave to master.
For Each ws In slaveWB.Worksheets
cr = cr + 1
cs.Range("A" & cr).Value = cr - 1
cs.Range("B" & cr).Value = ws.Name
lr = ws.Range("A1").End(xlDown).Row
For i = LBound(slaveCols) To UBound(slaveCols)
cs.Range(masterCols(i) & cr).Value = ws.Range(slaveCols(i) & lr).Value
cs.Range(masterCols(i) & cr).NumberFormat = ws.Range(slaveCols(i) & lr).NumberFormat
Next i
Next ws
slaveWB.Close False
Nextf:
Next f
'Autofit the columns.
cs.UsedRange.Columns.AutoFit
TheEnd:
SpeedOff
End Sub
Function GetFileList(FileSpec As String) As Variant
' Returns an array of filenames that match FileSpec
' If no matching files are found, it returns False
Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String
On Error Goto NoFilesFound
FileCount = 0
FileName = Dir(FileSpec)
If FileName = "" Then Goto NoFilesFound
' Loop until no more matching files are found
Do While FileName <> ""
FileCount = FileCount + 1
Redim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Exit Function
' Error handler
NoFilesFound:
GetFileList = False
End Function [/VBA]
Or I would prefer something like this at the status bar.
Please help.....