Solved

ion my script output i have multiple cvs files

Posted on 2016-08-09
3
22 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 51

Accepted Solution

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

~bp
0
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
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
Comment Utility
Thank you very much!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 …
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

18 Experts available now in Live!

Get 1:1 Help Now