Welcome!

Thank you visiting ICB - Iceberg Code Blog
This is the place, where I'd like to share ideas and some useful code (VB, Excel, T-SQL, JavaScript, ...)
Table of Content

Tuesday, June 27, 2006

Split & Merge

This is part one of two post.
When you work on your own, you don't care to divide you big workbook in a multitude
of smallest files to deploy to tens of users that must see only they own piece.
... and Excel become a nightmare.
How do you split a 10 thousands row worksheet into 200 X 50 workbook?
Or how do you split a workbook with 5 different sheets?
I worked out this solution:
SplitExcelFile.xls

You just need to set:
1) Which worksheet split, and the name of the field that must be split
2) the list of value to split and
3) the parametric name of all the output workbook.

Enjoy it!

Monday, June 19, 2006

TOC & JS

I have posted few things and now it's time to add a TOC (Table of Content) to search faster all the contents.
So I added some JavaScript to enjoy sorting. To get the code you can just "view Source"...

Click to view TOC

Thursday, June 15, 2006

If You like mp3 you gonna love this excel

After updating my previous Excel List File, I'm trying to specialized it.
So lets try this useful MP3_Info.xls excel that just find any mp3 on your PC and ...

... get all TAGs info about any single file: Amazing!


Enjoy it!

Tuesday, June 06, 2006

Most Useful Access Code

Maybe I'm a little bit too enthusiastic about this module but I use it so often that I consider it the most useful ...

The main idea is to store all the queries action in a table and run them from it.
With a table that contain all kind of queries, you just need to sort and flag them as runable.

Here the TR (track record) of the "SQL_List" table:
SQL_ID (counter)
SP_NAME (the name of the Stored Procedure)
Run_it (a flag to run or not them)
OrderSQL (the order sorting)
What_is_doing (description for portability)
SQL (The SQL of the action query)

What is an Action Query?
The one that does not just show records and does something like update, delete, append, create, ...

What is a SP (Stored Procedure)?
When you start using SQL-server you can really understand it. In short is a list of action, a program to get the result you want

Back to the SQL_List, here an example of how to query it:
SELECT SQL FROM SQL_List WHERE SP_NAME="Test" AND Run_it=1 ORDER BY OrderSQL;

To better use it you should use a Macro, that contain just one Action:
RunCode [ExecListOfQueries ( "SELECT SQL FROM SQL_List WHERE SP_NAME='Test' AND Run_it=1 ORDER BY OrderSQL")]

And here the core of the entire trick you need a module with the code:
ModuleName: UsefullAccessCode
The code is very simple:


Function ExecListOfQueries(MySQLList)
' stop warning
DoCmd.SetWarnings False
Dim dbs As DAO.Database, rst As DAO.Recordset, TempSQL
Set dbs = CurrentDb

' The list of queries
Set rst = dbs.OpenRecordset(MySQLList)
' if there are some records loop the result
If rst.EOF = False And rst.BOF = False Then
     rst.MoveFirst
     Do While rst.EOF = False
         ' loop to Execute all the queries
         ' replace double quote with single one to avoid error
         TempSQL = Replace(Replace(rst.Fields(0).Value, """", "'"), vbCrLf, " ")
         'Debug.Print TempSQL
         DoCmd.RunSQL TempSQL
     rst.MoveNext
     Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function


In this example Run_SQL_List.mdb you get all you need to test the code

The thing I most appreciate in this trick is the portability and flexibility.
... You can store all the queries in a single DB and use linked table to the "SQL_List" table, so it's easier to reuse queries from a project to another
... Or in ASP project you can change queries without changing the code.
... Most of all, the queries stay in a single place!

Download it Now!

Enjoy it!