Linker IT Software
Google
Web www.oraxcel.com
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 6881 times)
alanc
YaBB Newbies
*




Posts: 1
SCHEDULE and FTP REPORTS
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
Re: SCHEDULE and FTP REPORTS
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 www.oraxcel.com
 
The contents of the cmd.txt file:
username
password
ftp command 1
ftp command 2
...
bye
 
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:
job.xls
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