Custom Report form Excel data to text file ( Mail Merge )

[DingDang]

Hi,

I have xls data file from which i want to create report just like mail merge but in txt file template.

can any one provide macro to generate report.

text file should auto save with file name "report & date" into in same folder where xls file.

attached sample file for your ref.

https://dl.dropbox.com/u/66400357/custome%20report.xlsx ( data file )
https://dl.dropbox.com/u/66400357/Report.txt ( required report in txt file )

[abc0502]

Can Batch handle xlsx files ??

[miskox]

Here is your starting point (vbs part found somewhere on the net, made some modifications):

Code: Select all

for /f "tokens=1 delims=" %%a in ('dir /on /b *.xls*') do call :XLS2CSV "%%a" "%%a.csv"
goto :EOF

:XLS2CSV
Title Making .csv file %1.csv
>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

cscript /B excel2csv.vbs %1 %1.csv
if exist excel2csv.vbs del excel2csv.vbs
goto :EOF

It will export an Excel file to a .csv file with local delimiter (for example a semi colon if you are in Germany).
Now all you have to do is read this .csv file and make a report.

Hope this helps.

Saso

[abc0502]

is it always will have 5 lines or can be more ?

[DingDang]

Sir,

Thank u for your reply, but not getting as required. only file creates with .csv with no data.

Required just like mail merge in word. below is the report template only fill in the blanks need to copy data from xlsx file.


------------------------------------------------------------------
XYZ LTD
HALLAND [MH]
1ST FLOOR
ZIP 6666666
------------------------------------------------------------------
DESCRIPTION
------------------------------------------------------------------
ID NO. 8954 Amount Rs. 113820.00
CODE :- AA1 DATE. 01-01-2012
ADD : AA1, OFF : BBB
REASON: : CCC


XYZ LTD
01-01-2012
This is a system generated Advise.Does not require any signature.
------------------------------------------------------------------

[abc0502]

the batch miskox provided convert xlsx files to csv files so batch file can moify it and then we can generate the report file, and that batch converted the xlsx file successfully

[abc0502]

Edit
Huge mistake it can handle one file at a time if more it repeat the lines
just use it for one file only till i fix it

OK Fixed that, it work now fine

Hi, here is your batch, put it in the same folder where your XLSX file(s) is and it will convert all xlsx file(s) to csv and then the csv to txt reports

Code: Select all

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

setlocal EnableDelayedExpansion
:: This will Take the XLSX 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" will Take the CSV file and Generate the Report file from.
set "line=0"
For /F "tokens=*" %%@ in ('Dir /B /A:-D "*.csv"') Do (
      For /F "tokens=1,2,3,4,5,6,7,8 delims=," %%a in ('type "%%@"') Do (
         set /a line += 1
         IF !line! GEQ 2 (
            (
               Echo.
               Echo      XYZ LTD
               Echo      HALLAND [MH]
               Echo      1ST FLOOR
               Echo      ZIP 6666666
               Echo ------------------------------------------------------------------
               Echo                   DESCRIPTION
               Echo ------------------------------------------------------------------
               Echo ID NO. %%a      Amount Rs. %%b
               Echo CODE :- %%c      DATE. %%d
               Echo ADD : %%e      OFF : %%f
               Echo REASON: : %%g
               Echo.
               Echo.
               Echo XYZ LTD
               Echo %%h
               Echo This is a system generated Advise.Does not require any signature.
               Echo ------------------------------------------------------------------
            )>>"Report_[%%~n@]_[ !date:~7,2!-!date:~4,2!-!date:~10,4! ].txt
         )
      )
   set "line=0"
)

:: Remove CSV files
Del /F /S /Q "*.csv" >nul
Del /F /S /Q "*.vbs" >nul

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

Edit:
>Added the taskkill command "in the first for loop" to kill excel process after it convert each xlsx file to csv so it dosen't take all computer resources, or cause error when it convert many files.
>Added the comment about the TAB characters
>Added the delete commad for the vbs file

[Squashman]

Can Batch handle xlsx files ??

Powershell can.

[abc0502]

Can Batch handle xlsx files ??

Powershell can.

I alwys wanted to learn powershell but choosed python, i find it easier

[DingDang]

Sir,

Getting below error

The system cannot find the path specified.
The system cannot find the path specified.
The system cannot find the path specified.
The system cannot find the path specified.

All Done

and create file excel2csv.vbs file which showing msg :
--------------------------------------------------
Script : c:\Report\excel2csv.vbs
line: 2
char: 1
error: subsript out of range
code: 800A0009
Source: microsoft VBScript runtime error
----------------------------------------------------
Pls help

[abc0502]

I tested the code again and it works fine, no errors, all report files are made .
Hoy your run this batch and where?

Put all your xlsx files in one folder and put the batch with them then double click,
After it finish there will be only the xlsx files and txt files

and one more thing, fix the spaces in these lines, it's a 2 TAB characters, so delete all spaces then add 2 TAB characters instead.
spaces between the red and blue words

Echo ID NO. %%a Amount Rs. %%b
Echo CODE :- %%c DATE. %%d
Echo ADD : %%e OFF : %%f

and i modified the code to delete the vbs file after it done

[DingDang]

Sir,

Thanks for the reply.

but now vbs file deleted and i have made changes to delete space between red and blue words and add 2 tab between but no txt file created. in folder only xlsx and bat file is exist.
i don't understand what is going wrong at my end.

can u pls help

[abc0502]

can you tell me the steps you make when you run the batch and what is the location of your folder where the xlsx file is and what files exist?

[DingDang]

sir,

In "c:\Report" folder there is only custome report.xlsx file( data file ) and (below code).bat file exist. after double click on bat file its only creats pause with below error and after enter vbs file deleted but txt file not created.

Getting below error

The system cannot find the path specified.
The system cannot find the path specified.
The system cannot find the path specified.
The system cannot find the path specified.

All Done

[abc0502]

OK, Problem solved and for any one who wants to know what was the problem,It was as follows:
When he write this command in cmd window

Code: Select all

echo %date%

He gets this

Code: Select all

19/08/2012

But i get

Code: Select all

sun 08/19/2012

so the problem was in the txt file name, i made it to be created depending on my date formate and his should have been like this

Code: Select all

!date:~0,2!-!date:~3,2!-!date:~6,4!

instead of

Code: Select all

!date:~7,2!-!date:~4,2!-!date:~10,4!