Question: I use your product almost daily and was wondering what or how something could be done. Can I use your product to dump the Oracle query results into Excel to FTP to a customer? I know I can manually do this through Excel and SQL*XL...but was wondering about a way to automate it.

Answer: Yes you can automate this. You need macro recording to record the running of the query. You save the results to a xls file and execute an ftp script.

Let us first look at the ftp script. We need to make two new files. The first file is ftp_it.bat to run the ftp process. We will call this batch file from the macro. The second file is ftp_it.txt. This file contains the commands to send the FTP file.

Create a new text file. Give it the name ftp_it.bat. Open the file in notepad and add the following text: Code:

ftp -s:ftp_it.txt www.mydomain.com

Now create the second text file: ftp_it.txt. Open the file in notepad and add the following text. Replace these commands with the ftp commands that are specific to your FTP job.: Code:

Now we have the FTP files all setup let us look at executing the query. Open Excel and start the macro recorder with Tools|Macro|Record New Macro Use RunIt as macro name and choose to save the macros in "This Workbook"

Use SQL*XL to connect to your database. Then start the SQL dialog, type your SQL statement and run it. Stop the macro recorder. The code should look something like this: Code:

Do not forget to save your xls file with the macro before you run it!. If you save your macro workbook as myjob.xls you now need to open this file and run the macro manually. You can automate this step by adding a call to your macro RunIt to the Workbook_Open event Code:

Copyright (C) 1995-2007 Linker IT
Software BV. All Rights Reserved. Oracle is a registered trademark of
Oracle corporation. Excel and Office are registered trademarks of
Microsoft corporation. Other names appearing on the site may be trademarks
of their respective owners. Software,
files, documents, articles and other material are provided
"as is" and without warranties as to performance or mechantability or
any other warranties whether expressed or implied. No
warranty of fitness for a particular purpose is offered.
sitemap