Solved

ion my script output i have multiple cvs files

Posted on 2016-08-09
3
30 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 53

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 53

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

The following is a collection of cases for strange behaviour when using advanced techniques in DOS batch files. You should have some basic experience in batch "programming", as I'm assuming some knowledge and not further explain the basics. For some…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

776 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