Request for help on being able to compile data from several workbooks. I've seen an example where the files can be compiled by converting them into CSV, but was wondering if it can be done on xls and xlsx version of excel 2007 using command prompt.

I need to compile data by tab from about 100's of similar xls or xlsx reports. They have 5 tabs each and it needs to be compiled by tab. The headers for each tab is consistent and needs to be only included once in the cumulative tab of the final product.

Please assist.

Thanks!This is UNTESTED - it comes from two sources and uses Excel on the machine to convert the *.xls* to *.csv.

Run it in a folder with copies of the *.xls* and if it works then provide a sample from a few .csv files with more information about what you need.
You can PM the information, if it is sensitive, if you like.



Code: [Select]@echo off
>excel2csv.vbs echo Set objFSO = CreateObject("Scripting.FileSystemObject")
>>excel2csv.vbs echo src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
>>excel2csv.vbs echo dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))
>>excel2csv.vbs echo Dim oExcel
>>excel2csv.vbs echo Set oExcel = CreateObject("Excel.Application")
>>excel2csv.vbs echo oExcel.DisplayAlerts = FALSE
>>excel2csv.vbs echo oExcel.Interactive = FALSE
>>excel2csv.vbs echo Dim oBook
>>excel2csv.vbs echo Set oBook = oExcel.Workbooks.Open(src_file, 0 , TRUE)
>>excel2csv.vbs echo Set objWorksheet = oExcel.Worksheets(1)
>>excel2csv.vbs echo objWorksheet.Activate
>>excel2csv.vbs echo oBook.SaveAs dest_file, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, TRUE
>>excel2csv.vbs echo oBook.Close False
>>excel2csv.vbs echo Set objWorksheet = Nothing
>>excel2csv.vbs echo set oBook = Nothing
>>excel2csv.vbs echo oExcel.Quit
>>excel2csv.vbs echo set oExcel = Nothing

for /f "delims=" %%a in ('dir /on /b *.xls*') do (
Title Making "%%a.csv"
echo Making "%%a.csv"
cscript /nologo excel2csv.vbs "%%a" "%%a.csv"
)
Title Done!
if EXIST excel2csv.vbs del excel2csv.vbs
echo Done!
pauseSuperlike! I'll try this out and comeback shortly:)

Much appreciated! Phenomenal! Thank you so much...

Sorry about the delay! Here’s what I need as the end product:

Once the CSV files have been created. I would like to compile them into a workbook with data from each tab under the following conditions:

1. My files have 3-5 tabs – Each tab to be compiled in one workbook from all the individual CSVs
2. The headers for all the tabs is always consistent, and I would like them to be only available in the consolidated files once
3. In any one column preferably "Column A" populate the name of the file name and in "Column B" the tab name.

Attached is a sample file presenting my report format. These sometimes have a Password too The password is "openbook1"

Thanks again… You are a rock star!

Wishing you a very Merry Christmas and a Happy New year in advance!

[year+ old attachment deleted by admin]For starters, I don't think the xls to cvs script can handle passwords.

Did it create the CSV files? That is what we'd need to write something to help you.Got it. I've removed the password and have attached the the CSV version.

Thanks a lot!




[year+ old attachment deleted by admin]Open the CSV file in Open Office and explain what you want done. The data below the line with all the field names is what you need to be manipulated.
I am unsure how the first THREE lines in the CSV file are used (open it in notepad too see).


Additionally I don't think a CSV file contains the TAB information, or I don't understand how it does so.About two years I had learned that Powershell can easily read and write to Excel files. Thanks Squashman. I would assume it also needs Excel to be installed to do so, as the VB script does.

Can it also write to native XLS files?I too am interested if Powershell can write to native XLS files, as for I know that CSV's are easy to edit, create etc.Although Powershell can write and read .csv files, as can MANY scripting and programming tools, there isn't anything in Powershell (or any of the others) to natively read or write Excel format files, that is, files which would have an .xls or .xlsx extension. By 'natively' I mean, when Microsoft Excel is not also installed. When Microsoft Office applications are installed, (it gets a bit technical here!) they expose something called a "COM object MODEL" that programmers and scripters can get at and manipulate to do various things. Powershell uses the same COM object (Excel.Application) as VBScript. I found this in the snippet closet. It converts a single workbook into txt, csv, or html format. It could be tweaked to convert an entire folder of workbooks by adding an outer loop to iterate all the workboooks in a folder.

The VBScript version can check if the worksheet in empty, but I could not get the equivalent instruction to work in Powershell. In order to save each worksheet in text format, the script must loop thru the worksheet collection. If not, only the active worksheet gets saved correctly.

Code: [Select]param (
[System.IO.FileInfo] $xlBookPath = $( Read-Host -Prompt "Enter Spreadsheet Path" ),
[System.IO.DirectoryInfo] $txPath = $( Read-Host -Prompt "Enter Output Folder"),
[string] $filetype = $( Read-Host -Prompt "Enter Output Filetype" )
)

function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}

function GetOutputFileFormat {
Param([string]$formatname)

$passback = 0
switch($formatname) {
"csv" { $passback = 6 }
"txt" { $passback = 20 }
"html" { $passback = 44 }
default {Write-Host -ForegroundColor Yellow "Invalid File Format"; Exit}
}
return $passback
}

$xlApp = New-Object -ComObject Excel.Application
$xlApp.Visible = $false
$xlApp.DisplayAlerts = $true
$xlBook = $xlApp.Workbooks.Open($xlBookPath)

$OutputFileFormat = GetOutputFileFormat($filetype)
$xlSheet = $xlBook.Worksheets.item(1)

foreach($xlSheet in $xlBook.Worksheets) {
$TextFileName = (Join-Path -Path $txPath -ChildPath $xlBookPath.basename) + "~" + $xlSheet.Name + "." + $filetype
$xlSheet.SaveAs($TextFileName, $OutputFileFormat)
}

Release-Ref($xlSheet) | Out-Null
Release-Ref($xlBook) | Out-Null
Release-Ref($xlApp) | Out-Null

Stop-Process -Name Excel

Notes:

Tested on Win7 x64
The Stop-Process cmdlet is used to flush Excel from the system; Excel likes to hang out past it's shelf life.
The output file names are formatted: workbookname~sheetname.extension; The file format and the extension are the same. (Prompted at runtime)
I'm big fan of prompts. Saves opening in an editor for each run and don't have to remember any command line arguments

Maybe somebody can use this

Quote from: foxidrive on December 21, 2012, 08:10:18 PM

Thanks Squashman. I would assume it also needs Excel to be installed to do so, as the VB script does.

Can it also write to native XLS files?