Solved

ion my script output i have multiple cvs files

Posted on 2016-08-09
3
29 Views
Last Modified: 2016-08-09
I have one script that generates about 5 cvs files. Is there a way i can open the main one and have it put the other cvs files in their own tabs?
0
Comment
Question by:lolaferrari
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
Bill Prew earned 500 total points
ID: 41748821
Would I be correct in assuming that you want the CSV files opened in Excel?

~bp
0
 
LVL 52

Expert Comment

by:Bill Prew
ID: 41748877
Here is a fairly simple VBS script (building off this previous question) that merges a specified list of CSV files into a single Excel workbook, as tabs.  Save as a VBS file, update the file paths as needed, and run from a BAT or command line as follows:

cscript EE28962338.vbs

' Some constants from Excel object model
Const cExcelXLS = 56
Const cExcelXLSX = 51

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Define paths to CSV and Excel file paths
sNewFile = objFSO.GetAbsolutePathname("all.xlsx")
aCsvFiles = Array(objFSO.GetAbsolutePathname("file1.csv"), _
                  objFSO.GetAbsolutePathname("file2.csv"), _
                  objFSO.GetAbsolutePathname("file3.csv"), _
                  objFSO.GetAbsolutePathname("file4.csv"), _
                  objFSO.GetAbsolutePathname("file5.csv"))

' Instantiate the Excel application, but don't show it
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oExcel.DisplayAlerts = False

' Add each CSV file as a sheet to a new Excel workbook
For i = 0 to UBound(aCsvFiles)
   If i = 0 Then
      Set oNew = oExcel.Workbooks.Open(aCsvFiles(i))
   Else
      Set oAdd = oExcel.Workbooks.Open(aCsvFiles(i))
      oAdd.Sheets(1).Move , oNew.Sheets(i)
   End If
Next

' Save merged result as an Excel file
oNew.SaveAs sNewFile, cExcelXLSX
oNew.Close

' Shut down Excel
oExcel.Quit

Open in new window

~bp
0
 

Author Closing Comment

by:lolaferrari
ID: 41748930
Thank you very much!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now