bsharath
asked on
Combine many files into one
Hi All,
Script that works perfect need small changes/Additions
I want the totals of each colum
If GB then GB total and if % the percentage total.
Also need to understand whats the (C Diff D Diff) 2 headers are created but no content populated within it.
Also a 2nd txt file that can record all machines that were not able to scan with specific reason.
regards
Script that works perfect need small changes/Additions
I want the totals of each colum
If GB then GB total and if % the percentage total.
Also need to understand whats the (C Diff D Diff) 2 headers are created but no content populated within it.
Also a 2nd txt file that can record all machines that were not able to scan with specific reason.
regards
'=============
Set objExcel = CreateObject("Excel.Application")
Const xlToLeft = -4159
Const xlUp = -4162
Const xlPasteValues = -4163
intNewWBSheets = objExcel.Application.SheetsInNewWorkbook
objExcel.Application.SheetsInNewWorkbook = 1
Set NewWB = objExcel.Workbooks.Add
objExcel.Visible = True
'objExcel.ScreenUpdating = False
strCSVFilesFolder = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each objFile In objFSO.GetFolder(strCSVFilesFolder).Files
If Right(LCase(objFile.Name), 4) = LCase(".csv") Then
Set CSVFile = objExcel.Workbooks.Open(objFile.Path)
intLastRow = CSVFile.Sheets(1).Cells(65536, "A").End(xlUp).Row
intLastCol = CSVFile.Sheets(1).Cells(1, 256).End(xlToLeft).Column
If Trim(NewWB.Sheets(NewWB.Sheets.Count).Range("A1").Value) = "" Then
CSVFile.Sheets(1).Range("A1", CSVFile.Sheets(1).Cells(1, intLastCol)).Copy NewWB.Sheets(NewWB.Sheets.Count).Range("A1")
End If
CSVFile.Sheets(1).Range("A2", CSVFile.Sheets(1).Cells(intLastRow, intLastCol)).Copy NewWB.Sheets(NewWB.Sheets.Count).Range("A" & NewWB.Sheets(NewWB.Sheets.Count).Cells(65536, "A").End(xlUp).Row + 1)
CSVFile.Close
NewWB.Sheets(NewWB.Sheets.Count).Name = "Results"
End If
Next
objExcel.Application.SheetsInNewWorkbook = intNewWBSheets
objExcel.ScreenUpdating = True
MsgBox "Done"
'=============
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rob works perfect
Can i get the total HDD space in a colum as well
Also for C and D drive can i have the % for occupied space as well
Now we get % for free space
Can i get for Occupied as well
So i can later know whats total space in % Used and free
Can i get the total HDD space in a colum as well
Also for C and D drive can i have the % for occupied space as well
Now we get % for free space
Can i get for Occupied as well
So i can later know whats total space in % Used and free
OK, those modifications would all happen in the first script. This one just puts it all together. We'll have to add total HDD space, and % occupied columns, (which is just 100 - free space).
Rob.
Rob.
ASKER
Hi Rob... Sorry to bother you too much today
https://www.experts-exchange.com/questions/27464159/HTA-file-to-capture-software-needs.html
need urgent help on this please
https://www.experts-exchange.com/questions/27464159/HTA-file-to-capture-software-needs.html
need urgent help on this please
I will post the addition of the percentage used in this question:
https://www.experts-exchange.com/questions/27464099/Script-to-collect-diskspace.html
I think this question is complete.
Rob.
https://www.experts-exchange.com/questions/27464099/Script-to-collect-diskspace.html
I think this question is complete.
Rob.
ASKER
Thanks Rob
To address the added column in the other question, use this:
Regards,
Rob.
Regards,
Rob.
Set objExcel = CreateObject("Excel.Application")
Const xlToLeft = -4159
Const xlUp = -4162
Const xlPasteValues = -4163
intNewWBSheets = objExcel.Application.SheetsInNewWorkbook
objExcel.Application.SheetsInNewWorkbook = 1
Set NewWB = objExcel.Workbooks.Add
objExcel.Visible = True
'objExcel.ScreenUpdating = False
strCSVFilesFolder = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each objFile In objFSO.GetFolder(strCSVFilesFolder).Files
If Right(LCase(objFile.Name), 4) = LCase(".csv") Then
Set CSVFile = objExcel.Workbooks.Open(objFile.Path)
intLastRow = CSVFile.Sheets(1).Cells(65536, "A").End(xlUp).Row
intLastCol = CSVFile.Sheets(1).Cells(1, 256).End(xlToLeft).Column
If Trim(NewWB.Sheets(NewWB.Sheets.Count).Range("A1").Value) = "" Then
CSVFile.Sheets(1).Range("A1", CSVFile.Sheets(1).Cells(1, intLastCol)).Copy NewWB.Sheets(NewWB.Sheets.Count).Range("A1")
End If
CSVFile.Sheets(1).Range("A2", CSVFile.Sheets(1).Cells(intLastRow, intLastCol)).Copy NewWB.Sheets(NewWB.Sheets.Count).Range("A" & NewWB.Sheets(NewWB.Sheets.Count).Cells(65536, "A").End(xlUp).Row + 1)
CSVFile.Close
NewWB.Sheets(NewWB.Sheets.Count).Name = "Results"
End If
Next
intLastRow = NewWB.Sheets(1).Cells(65536, "A").End(xlUp).Row
intLastCol = 1
For intRow = 1 To intLastRow
intCol = NewWB.Sheets(1).Cells(intRow, 256).End(xlToLeft).Column
If intCol > intLastCol Then intLastCol = intCol
Next
For intCol = 3 To intLastCol
strRange = "R[" & -(intLastRow - 1) & "]C[0]:R[-1]C[0]"
NewWB.Sheets(1).Cells(intLastRow + 1, intCol).FormulaR1C1 = "=SUM(" & strRange & ")"
Next
NewWB.Sheets(1).Rows("1:1").Font.Bold = True
NewWB.Sheets(1).Rows(intLastRow + 1 & ":" & intLastRow + 1).Font.Bold = True
objExcel.Application.SheetsInNewWorkbook = intNewWBSheets
objExcel.ScreenUpdating = True
MsgBox "Done"
ASKER
Thanks Rob
ASKER
check this script
Can you help with this
https://www.experts-exchange.com/questions/27464099/Script-to-collect-diskspace.html
its for logging success & failures