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
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!
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
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!
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:
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!
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!