Help me in creating a batch

[broadband]

friends i need help in creating a batch file. can someone please create a batch file for my below requirment...


i have a excel sheet from where i need to copy column G, Column A and auto create a new excel file in the same folder. the new excel file should only contains content of column G and column A. This task needs to be done via batch file.

please help...

[Squashman]

Batch files cannot directly read, edit, or create excel files.

[ShadowThief]

You want an excel vba macro.

Alternatively, if these are xlsx files and you have archival software (WinZip, WinRAR, 7-Zip, etc) installed, they're technically archive files with XML inside of folders. But you really, really, really don't want to create xlsx files that way. It's just a giant headache, and it probably won't even work.

You want an excel vba macro because that will do what you want and you already have the software for that installed.

[Samir]

The only possible way you can do this in batch is to convert the excel files to csv and then manipulate the csv files in batch and then reimport them into excel.

[Grobu]

You could recycle the VBS code found on http://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line to convert your Excel spreadsheet into a CSV, in conjunction with the following batch file :

Code: Select all

    @echo off
    
    set book=book1.xls
    set tempFile=_tmpcsv_.tmp
    set output=columnsAG.csv
    
    prompt>%output%
    
    for %%a in (%tempFile%,%output%) do if exist %%a del %%a
    
    wscript save_as_csv.vbs %book% %tempFile%
    
    for /f "tokens=* skip=1" %%a in (%tempFile%) do call :getFields %%a
    del %tempFile%
    
    goto end
    
:getFields
    echo "%~1","%~7">>%output%
    
    
:end

code suggestion for VBS file (called "save_as_csv.vbs" in aforementioned batch file) :

Code: Select all

    dim CSV_FORMAT, fso, srcFile, dstFile, excel, book
    
    CSV_FORMAT = 6
    
    Set fso = CreateObject("Scripting.FileSystemObject")

    srcFile = fso.GetAbsolutePathName(Wscript.Arguments.Item(0))
    dstFile = fso.GetAbsolutePathName(WScript.Arguments.Item(1))
    
    Set excel = CreateObject("Excel.Application")
    Set book = excel.Workbooks.Open(srcFile)
    book.SaveAs dstFile, CSV_FORMAT
    book.Close False
    
    excel.Quit

[Grobu]

You could use a VBS script in conjunction with a batch file to :
- save your Excel sheet into a CSV file (VBS)
- then extract columns A and G from each line and write it to an output file (batch)

Code suggestion for the VBS file :

Code: Select all

    dim CSV_FORMAT, fso, srcFile, dstFile, excel, book
    
    CSV_FORMAT = 6
    
    Set fso = CreateObject("Scripting.FileSystemObject")

    srcFile = fso.GetAbsolutePathName(Wscript.Arguments.Item(0))
    dstFile = fso.GetAbsolutePathName(WScript.Arguments.Item(1))
    
    Set excel = CreateObject("Excel.Application")
    Set book = excel.Workbooks.Open(srcFile)
    book.SaveAs dstFile, CSV_FORMAT
    book.Close False
    
    excel.Quit

Code suggestion for the batch :

Code: Select all

    @echo off
    
    set book=book1.xls
    set tempFile=_tmpcsv_.tmp
    set output=columnsAG.csv
    
    prompt>%output%
    
    for %%a in (%tempFile%,%output%) do if exist %%a del %%a
    
    wscript save_as_csv.vbs %book% %tempFile%
    
    for /f "tokens=* skip=1" %%a in (%tempFile%) do call :getFields %%a
    del %tempFile%
    
    goto end
    
:getFields
    echo "%~1","%~7">>%output%
    
:end