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
SQL*XL VBA Macros and VBA Reference List (Read 7967 times)
glennes
YaBB Newbies
*


I Love SQL*XL

Posts: 21
SQL*XL VBA Macros and VBA Reference List
19.06.08 at 00:12:24
 
Two parts:
1. My SQL script executes perfectly with SQL*XL. However, when run the script while recording a macro, I have a problem. The script has an ACCEPT statement in it. The user enters the named variable when prompted, then the script executes, using the entered variable later in the script. But, when I look at the macro which was recorded, it has coded the variable entered for the ACCEPT statement as a literal where it is used later in the script. Then, the next time the script is run, it uses the literal that was previously recorded and not the new variable the user entered when prompted.How can I make the recorded macro not do this, or alter the SQL script so that the macro will not save the literal in the VBA code?
 
2. Each time I run my VBA code which uses the result of the SQL script above, I have to go to the Tools -->References menu in the VBA editor and check the SQL*XL reference check block. Is there a way to keep this reference checked by default or to accomplish the same thing as checking the reference check block within my VBA code? (I don't save the workbook when I'm done; I create a new workbook each time. This is necessary due to the process using the resulting workbook.)
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQL*XL VBA Macros and VBA Reference List
Reply #1 - 19.06.08 at 13:05:37
 
Quote:
The script has an ACCEPT statement in it. The user enters the named variable when prompted, then the script executes, using the entered variable later in the script.
...
Then, the next time the script is run, it uses the literal that was previously recorded and not the new variable the user entered when prompted.How can I make the recorded macro not do this, or alter the SQL script so that the macro will not save the literal in the VBA code?

 
This is indeed the case. I have made an example. I wish to query the authors table taking (part of) the name of the author as a variable. The following code is recorded:
Code:
Sub Macro2()
    SQLXL.Sql.setText "accept author prompt 'Author:';  select * from authors  where author like '%&author%'"
    SQLXL.Sql.Statements(1).Execute
    Set SQLXL.Sql.Statements(2).Target = Targets(litExcel)
    SQLXL.Sql.Statements(2).OptimiseForLargeQuery = False
    With Targets(litExcel)
	  .AutoFilter = False
	  .AutoFit = True
	  .Headings = True
	  .Sort = False
	  .StartFromCell = "[Book1]Sheet1!A2"
	  .Transpose = False
	  .SQLInNote = True
	  .ShowNote = True
	  .FormatData = True
	  .FreezePanes = False
	  .PasteInsert = False
    End With
    SQLXL.Sql.Statements(2).SubstVariables.Add "author"
    With SQLXL.Sql.Statements(2)
	  .ShowParametersDlg = False
	  .ShowResultsetDlg = False
    End With
    SQLXL.Sql.Statements(2).SubstVariables("author").Value = "Jeff"
    SQLXL.Sql.Statements(2).Execute
End Sub
 


 
For safety SQL*XL records the value entered for the substitution variable "author". It just sees that value being supplied and it records it as a statement. It is easily removed as I have shown below. I have removed the setting of the value for the substitution variable and instead allowed SQL*XL to show the parametersdialog: ShowParametersDlg = True.
 
Code:
Sub Macro2()
    SQLXL.Sql.setText "accept author prompt 'Author:';  select * from authors  where author like '%&author%'"
    SQLXL.Sql.Statements(1).Execute
    Set SQLXL.Sql.Statements(2).Target = Targets(litExcel)
    SQLXL.Sql.Statements(2).OptimiseForLargeQuery = False
    With Targets(litExcel)
	  .AutoFilter = False
	  .AutoFit = True
	  .Headings = True
	  .Sort = False
	  .StartFromCell = "[Book1]Sheet1!A2"
	  .Transpose = False
	  .SQLInNote = True
	  .ShowNote = True
	  .FormatData = True
	  .FreezePanes = False
	  .PasteInsert = False
    End With
    With SQLXL.Sql.Statements(2)
	  .ShowParametersDlg = True
	  .ShowResultsetDlg = False
    End With
    SQLXL.Sql.Statements(2).Execute
End Sub
 


 
This code will now behave in the same way as when you recorded it. The only difference being that the SQL dialog and the Resultset dialogs do not show.
Back to top
 
« Last Edit: 19.06.08 at 13:10:13 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQL*XL VBA Macros and VBA Reference List
Reply #2 - 19.06.08 at 13:18:31
 
Quote:
Each time I run my VBA code which uses the result of the SQL script above, I have to go to the Tools -->References menu in the VBA editor and check the SQL*XL reference check block. Is there a way to keep this reference checked by default or to accomplish the same thing as checking the reference check block within my VBA code? (I don't save the workbook when I'm done; I create a new workbook each time. This is necessary due to the process using the resulting workbook.)

 
I am not 100% clear what you are doing. Clearly you lose the reference to SQL*XL somehow. If I read your scenario correctly you do not save the workbook but you create a new workbook each time. Do you add the code manually to the new workbook each time? In that case the reference doesn't exist yet. The best thing to do would be to copy the workbook inclusing the code and the reference blanking the results from the previous run.
 
You can also configure SQL*XL as an addin. Go to tools, addins and tickmark the SQL*XL addin. This will load SQL*XL each time you start Excel and you are always able to reference the SQL*XL addin.
 
To check that a saved workbook does not lose the reference I have just tested it. I recorded a macro. Recording a macro using SQL*XL always sets the reference to SQL*XL automatically.  
I saved the workbook and closed Excel.
I opened Excel again and opened the saved workbook. I just ran the macro and all was fine. The reference was therefore saved to the file as well. This is how it is supposed to work.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
glennes
YaBB Newbies
*


I Love SQL*XL

Posts: 21
Re: SQL*XL VBA Macros and VBA Reference List
Reply #3 - 19.06.08 at 14:57:21
 
Thanks for the help on my first question. I made the edit to the script and now it does what I want it to.
 
In regard to the question on the references:
 
What I do is start Excel and connect to the Oracle database. Then, I select one of my Favorites on the SQL*XL drop-down menu and let it run. Running it that way, which is normal routine, does not present the references problem.  But, when I'm in the VBA editor and import the saved macro of the favorite (the .bas program code file), I do have to manually select the SQL*XL reference from the Tools -->References menu. What I want is for that check block to remain checked by default -- all the time -- no matter if what I'm coding needs the SQL*XL reference or not.
 
Also, I've recorded a macro of the SQL*XL connection to the Oracle database into a Module.  When I run that module, it executes as it should and makes the connection.  However, if it is the first Sub() in the module and is then followed by a second Sub() that runs my SQL*XL Favorite, I get an error on the statement "Set SQLXL.Sql.Statements(2).Target = Targets(litExcel)" saying it can't find the target.  Yet, if I remove the connection Sub(), click the Disconnect button in Excel, click the Connect button in Excel and manually connect -- then execute the Favorite Sub(), the Favorite will run fine. I get the same error if I combine the two Sub()s into a single Sub(). Why does this happen? I'd like my Favorite Sub() to connect, execute, and disconnect.
Back to top
 
 
  IP Logged
Pages: 1