# The question:
Q: Is it anything special about XL-Dennis since he has his own forum here at OzGrid?
A: No, I'm a simple man with simple needs and who likes to share my knowledge and findings.
# File Management in MS Windows
When Windows 95 was introduced one of the big advantage was the file management
in a combination of the GUI-interface (Graphic User Interface).
Since then Microsoft has released several version of Windows. For every new version I have wished for a better filemanagement-tool but so far we still have to work with the original tools (although it has gone through some minor changes during the years).
Nowdays it is quite common that we have 40 / 80 / 120 GB hard drives and the lack of a professional and a sophisticated filemanagement-tool has becomed painfuly obvious.
For instance, searching for a source/file with search engines such as Google etc on the Internet is much easier and much faster then searching for a file on the
local hard drive.
When will Microsoft provide us with a powerful filemanagement-tool?
Meanwhile please send me an U2U-message if you regulary use a filemanagement-tool that
you really like and find to be very useful. I will look at it and perhaps mention it in the next post.
# Some few simple tips about filemanagement:
* Organize your folder in a hierarchical structure where you have a topfolder and then several subfolders below it. You can add several levels of subfolders depending on Your needs.
*Be loyal to your created folderstructure. It means that you save files regularly in the right folders.
*The benefit of these approaches is two-fold:
- Files and projects are easier to locate on the local harddrive.
- Since we should have regular backups You can easily backup the whole folder-structure in a snap.
# Long raw SQL-statements & The limitation of the VB-editor
Recently I discovered that the VB-editorn has a limitation of how many characters we can place on every line via VBA-code.
Well, it's not surprising that it exists per se but it was the first time I was forced to deal with it.
The VB-editorn can approximate and accept 1000 characters per line and if we need to work with longer strings/expressions then we need to split them up on several lines.
The test-solution I came up with and after some revision implemented is the following:
Step 1:
Option Explicit
Sub Add_Data()
Dim rn As Range
Dim i As Long
Set rn = Cells(1, 1)
For i = 1 To 2000
rn.Value = rn.Value & "A"
Next i
End Sub
Display More
Step 2:
Sub Split_Text_VBE()
Dim stTest As String, stTemp As String
Dim lnPos As Long
Dim lnLen As Long
stTest = Cells(1, 1).Value
stTest = Replace(Replace(stTest, Chr(13), ""), Chr(10), " ")
lnLen = Len(stTest)
For lnPos = 1 To lnLen
If lnPos Mod 600 = 0 Then
stTemp = stTemp & """" & " _ " & vbCrLf
stTemp = stTemp & "& " & """" & Mid(stTest, lnPos, 1)
Else
stTemp = stTemp & Mid(stTest, lnPos, 1)
End If
Next
Debug.Print stTemp
End Sub
Display More
It also reminded me how painful it is to work with string-expressions in VBA.
# Replace Access with MSDE 2000?
The Access-database has become a very popular so called desktop database and it serves its purpose well.
However, sometimes the lack of a real databaserver, as with Access, the limit of the number of users that can access it at the same time and the limit of how much data it can store be one, or more, bottlenecks to deal with.
If we face one, or more, of the mentioned limitations then we need to make a decision about moving the data to a new database-platform.
One alternative we may consider to use is the Microsoft SQL Server Desktop Engine 2000 (MSDE 2000).
While you thinking of upgrading the Access-database also look at the following databases
provided by MySQL:
MySQL
MaxDB
MySQL provides some good GUI-based tools for administration of databases.
All three databases are good options and may fill your needs. Good support exists for all the mentioned databases.
# Office Automation with VB.Net
If You are a owner of the Visual Studio.Net 2003 or VB.Net 2003 and would like to learn more about automating MS Office with VB.Net then you should read Mike R's excellent tutorial.
At present it is the best source available to learn how to automate MS Excel with VB.Net.
(Unfortunately Microsoft have published several MSDN-articles that tend to be more confusing then clarifying which we can only regret.)
Automating Office Programs with VB.Net / COM Interop
Mike have also put together an excellent FAQ for Automating MS Office with VB.Net
And yes, I admit I gave Mike a helping hand with the tutorial
# End
Life is too short so enjoy the life and turn off the computer!
Dennis