Link to home
Start Free TrialLog in
Avatar of bsharath
bsharathFlag for India

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

'=============
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"
'=============

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bsharath

ASKER

Thanks Rob

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
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
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.
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
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.
Thanks Rob
To address the added column in the other question, use this:

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"

Open in new window

Thanks Rob