Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
SCHEDULE and FTP REPORTS (Read 8740 times)
YaBB Newbies

Posts: 1
16.02.06 at 18:11:41
First of all, I love SQL*XL - it's one of three killer applications I use to run my business.
I need to send reports to clients at scheduled times.  Currently I can do this from MySql via email using SQLYog - which uses the MS Scheduler.  Some clients prefer spreadsheets, and to have them sent via FTP.
So here is the ultimate application :  can SQL*XL be used to (a) schedule a range of sql reports, (b) output them to a specified directory, and (c) FTP them to a specified recipient?
I'm sure much of this can be done using macros (I'm not clever enough to write them) or by chaining together several utilities : but it would be good to control all this from one point.  It would give Crystal Reports something to worry about!
Alan Crabb
Integer Group Ltd
New Romney, Kent, UK
Back to top
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Reply #1 - 17.02.06 at 08:22:12
Hi Alan,
Clever idea. I will see what I can do.
For now (current release SQL*XL 4.0.39) you can achieve this manually by doing the following:
Create a workbook e.g. called jobs.xls
When this workbook is open start the macro recorder.
Login to the database and create a new output workbook. Call it e.g. output1.xls
Run the query to populate output1.xls and save the file
Open another workbook for new output. Call it e.g. output2.xls
Run the query to populate output2.xls; save the file
and so on...
Finally disconnect from the database.
Edit the macro.  
Add a final statement to close Excel (Application.Exit)
Add a line to start the macro when the workbook is openened. (Workbook_Open event)
Now you have a selfcontained solution in jobs.xls.
Opening it will run the macros and create the output spreadsheets
To schedule it, use the MS Scheduler
To ftp it use the technique to call the DOS ftp command with a command file.
Example call: ftp -s:cmd.txt
The contents of the cmd.txt file:
ftp command 1
ftp command 2
To have the job.xls and ftp job started together you can create a batch file to include both tasks. A batch file is just a text file you create with notepad. Call it job.bat for example. Edit it and add the lines:
ftp -s:cmd.txt destination_machine
I know this is all a bit long winded but at least it gives you a recipe to create the feature you like with the current software.
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1