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
Automatically FTP Excel query results (Read 7534 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Automatically FTP Excel query results
17.05.06 at 14:35:53
 
How to automatically FTP Excel query results
 
SQL*XL user Hank from the USA asked the following question.
 
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:
your_ftp_username
your_ftp_password
cd public_html
cd software
binary
put myfile.xls
ascii
put myfile.txt
bye
 


 
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:
Sub RunIt()
  SQLXL.InitialiseSQLXL
  SQLXL.Database.ConnectionType = litOO4O
  SQLXL.Database.Connect UserName:="scott", PassWord:="tiger", DBAlias:="ora817", ConnectionString:="", AllowTransactions:=True
  SQLXL.Sql.setText "select * from emp where rownum < 10"
  Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
  SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
  With Targets(litExcel)
    .AutoFilter = False
    .AutoFit = True
    .Headings = True
    .Sort = False
    .StartFromCell = "$A$1"
    .Transpose = False
    .SQLInNote = True
    .FormatData = True
    .FreezePanes = False
    .PasteInsert = False
  End With
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  SQLXL.Sql.Statements(1).Execute
End Sub 


 
Now edit the code a little to save the output in a new file. Add the following lines at the top of the macro to create a new workbook: Code:
  Dim wkb As Workbook
  Set wkb = Workbooks.Add 


 
Now add a file lines at the bottom of the macro to save and close the file: Code:
wkb.Close saveChanges:=True,FileName:="c:\myfile.xls"
 


 
Now execute the FTP script and close Excel. Add the following code to the bottom of the macro: Code:
  Shell "c:\ftp_it.bat"
  Application.Quit
 


 
The full macro may look like this: Code:
Sub RunIt4()
  Dim wkb As Workbook
  Set wkb = Workbooks.Add

  SQLXL.InitialiseSQLXL
  SQLXL.Database.ConnectionType = litOO4O
  SQLXL.Database.Connect UserName:="scott", PassWord:="tiger", DBAlias:="ora817", ConnectionString:="", AllowTransactions:=True
  SQLXL.Sql.setText "select * from emp where rownum < 10"
  Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
  SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
  With Targets(litExcel)
    .AutoFilter = False
    .AutoFit = True
    .Headings = True
    .Sort = False
    .StartFromCell = "$A$1"    .Transpose = False
    .SQLInNote = True
    .FormatData = True
    .FreezePanes = False
    .PasteInsert = False
  End With
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  SQLXL.Sql.Statements(1).Execute
  
  wkb.Close saveChanges:=True, Filename:="c:\myfile.xls"
  
  Shell "c:\ftp_it.bat"
  Application.Quit
End Sub
 


 
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:
Private Sub Workbook_Open()
  RunIt
End Sub
 


 
References:
SQL*XL software: www.oraxcel.com/projects/sqlxl
VBA SQL*XL API: www.oraxcel.com/projects/sqlxl/help/vba
Back to top
 
« Last Edit: 25.09.06 at 12:49:42 by Gerrit-Jan Linker »  

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