Need ur help to split one excel sheet to many sheets

[moh_salah8]

Need ur help to split an Excel sheet contains data to 21 Languages.

First column for language name.
Language name always starts with two or three characters as shown:

ar
fa
fil-ph
gu
he
hi
id
kok
ku-arab
mr
ms
ne-np
or-in
pa
pa-arab
prs-af
sd-arab
th
ug-arab
ur
vi

- I need to split the original sheet to 21 sheets according to the language name and the output sheet called by the language code name.

I hope to find anyone to help in this batch.

Thanks in advance.

[Squashman]

This has nothing to do with Batch files.

[abc0502]

Squashman is right,
but any way there was a topic similar to this , you will first convert the excel sheet to csv file with a vbscript , then use a batch to extract each line to a separate file and name that file with the first token in that line

i will try to find it.

[foxidrive]

Are there foreign language characters in the data of the spreadsheet?

It will make a difference on how to go about the task.

[abc0502]

I found the Topic: here
This should work for you, I didn't test it, so test it on a copy of your file:

Code: Select all

@echo off
cls & Mode 60,10 & Color 0C

setlocal EnableDelayedExpansion
:: This will Take the Excel file and Convert to CSV  [ miskox contribution ]
For /F "tokens=1 delims=" %%a in ('dir /on /b "*.xls*"') Do (
call :XLS2CSV "%%a" "%%~na.csv"
Taskkill /F /IM:EXCEL.exe >nul
)

:: This "For Loop" should Take the CSV file and split to many txt files.
set "line=0"
If not exist "XLS_Files" MD "XLS_Files" >nul
For /F "tokens=1* delims=," %%a in ('type "*.csv"') Do (
   set /a line+=1
   if !line! GTR 1 (
      echo %%a,%%b>>"XLS_Files\%%a.txt"
   )
)

:: Convert txt to csv
For /F "tokens=1 delims=" %%a in ('dir /on /b "XLS_Files\*.txt"') Do (
   Ren "XLS_Files\%%~na.txt" "XLS_Files\%%~na.csv" >nul
)

:: Convert csv to xls
For /F "tokens=1 delims=" %%a in ('dir /on /b "XLS_Files\*.csv"') Do (
   call :csv2xls "XLS_Files\%%a" "XLS_Files\%%~na.xls"
   Taskkill /F /IM:EXCEL.exe >nul
)

:: Remove vbs & csv files
Del /F /Q "*.vbs" >nul
Del /F /Q "*.csv" >nul
Del /F /Q "XLS_Files\*.csv" >nul

:: End
Endlocal
Echo.&Echo.&Echo.&Echo.
Echo                         All Done
pause>nul
Exit /B

:XLS2CSV
Title Converting .xlsx files to .csv files
 
IF not exist "excel2csv.vbs" (
(
echo Set objFSO = CreateObject^("Scripting.FileSystemObject"^)
echo src_file = objFSO.GetAbsolutePathName^(Wscript.Arguments.Item^(0^)^)
echo dest_file = objFSO.GetAbsolutePathName^(WScript.Arguments.Item^(1^)^)
echo Dim oExcel
echo Set oExcel = CreateObject^("Excel.Application"^)
echo oExcel.DisplayAlerts = FALSE
echo oExcel.Interactive = FALSE
echo Dim oBook
echo Set oBook = oExcel.Workbooks.Open^(src_file, 0 , TRUE^)
echo Set objWorksheet = oExcel.Worksheets^(1^)
echo objWorksheet.Activate
echo oBook.SaveAs dest_file, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, TRUE
echo oBook.Close False
echo Set objWorksheet = Nothing
echo set oBook = Nothing
echo oExcel.Quit
echo set oExcel = Nothing
)>>"excel2csv.vbs"
)
::IF exist "excel2csv.vbs" Del /F /Q "excel2csv.vbs" >nul
cscript /B excel2csv.vbs %1 %1.csv
goto :EOF

:csv2xls
Title Converting .csv files to .xls files
 
IF not exist "csv2xls.vbs" (
(
echo srccsvfile = %~1
echo srcxlsfile = %~2
echo.
echo Set objExcel = CreateObject^("Excel.Application"^)
echo objExcel.Visible = false
echo objExcel.displayalerts= false
echo.
echo Set objWorkbook = objExcel.Workbooks.open^(srccsvfile^)
echo Set objWorksheet1 = objWorkbook.Worksheets^(1^)
echo objWorksheet1.SaveAs srcxlsfile, 2
)>>"csv2xls.vbs"
)
::IF exist "csv2xls.vbs" Del /F /Q "csv2xls.vbs" >nul
cscript /B csv2xls.vbs %1 %1.csv
goto :EOF

[foxidrive]

If there foreign language characters in the data of the spreadsheet then batch will probably mangle them.

I'm curious - does excel need to be running for the batch to work? I note that taskkill is used to kill excel after the conversion.

I don't have excel...

[abc0502]

If there foreign language characters in the data of the spreadsheet then batch will probably mangle them.

I'm curious - does excel need to be running for the batch to work? I note that taskkill is used to kill excel after the conversion.

I don't have excel...

I didn't test it so i don't know about a forign languaged,

and it seems to be that excel run, i found that when i was making the previous batch , i was keep testing and i noticed that the pc become very slow so i opend the task manager and found a huge number of excel.exe process running so i had to add the taskkill in the for loop so it kill the process after every convert to prevent the slow in the computer if running on many files

test this to convert empty csv file to xls, it's a vbscript:

Code: Select all

srccsvfile = "C:\Documents and Settings\Admin-PC\desktop\a.csv"
srcxlsfile = "C:\Documents and Settings\Admin-PC\desktop\a.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = false
objExcel.displayalerts= false

Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet1 = objWorkbook.Worksheets(1)
objWorksheet1.SaveAs srcxlsfile, 2

[foxidrive]

It gave me an error and looks like it requires Excel.

a.vbs(4, 1) Microsoft VBScript runtime error: ActiveX component can't create object: 'Excel.Application'

[abc0502]

I guess vbscript use a feature in excel to convert, excel has a feature to import csv files and i think vbscript uses this to convert, like open the csv and then save it as xls

[moh_salah8]

Thanks and appreciate ur effort.

but it's not working there is an error message and i think this code will not split the general sheet to many sheets according the language name as mentioned in details.

Ooohh i really have a problem with this task.

Thanks again.....

[foxidrive]

What did the error message say? abc0502 will need to know if he can adjust the code.


Another method of splitting up the sheet is the use a program that crates keyboard and mouse macros.
You do one column in excel to record the macro and then when you press the hotkey it will repeat the process on the other columns, including saving to files.

Google for "macro recording keyboard mouse" and you should find a program.


And there is a scripting program called AUTOIT where you might be able to do it too.

[miskox]

Thanks and appreciate ur effort.

but it's not working there is an error message and i think this code will not split the general sheet to many sheets according the language name as mentioned in details.

Ooohh i really have a problem with this task.

Thanks again.....

The

Code: Select all

...
...Worksheets(1)
...

says that it should export sheet 1 (or it can contain a sheet name).

So you have two options:
1. name all your sheets when exporting (if they are the same all the time)
2. count from 1 to the number of sheets you have.

Hope this helps,
Saso