Archive for the ‘PowerShell’ Category

Convert Multi Tab Excel to CSV

Posted: February 3, 2016 in PowerShell

Copy the below code and open Notepad++ past and then go to File —>Save As –>(Visual Basic file). After saving drag the excel file and drop it over.

Set lstArgs = WScript.Arguments
‘For I = 0 To lstArgs.Count – 1 ‘ Loop through each file

FullName = lstArgs(I)
Filename = Left(lstArgs(I), InStrRev(lstArgs(I), “.”))
PATH = Split(lstArgs(I),”.”)(0)
Desktop = LEFT(PATH,Len(PATH)- LEN(Right(PATH, Len(PATH) – InStrRev(PATH, “\”))))

Set Sh=WScript.CreateObject(“WScript.Shell”)
‘ Create Excel Objects
Set objWS = CreateObject(“Excel.application”)
Set objWB = objWS.Workbooks.Open(FullName)

objWS.Application.Visible = False
objWS.Application.DisplayAlerts = False

WScript.Echo “Excel to csv conversion started. It may take some time.”

for each sht In objWB.Sheets

‘MsgBox FileName
sht.SaveAs Desktop & sht.Name & “csv”, 23
Next
objWB.Close
objWS.Application.Quit
objWS.Quit

‘ Destroy Excel Objects
Set objWS = Nothing
Set objWB = Nothing

 

Advertisements