These are our Top 10 reads of July 2015.
thanks to all visitors, bookmark us, share us, talk about us, Follow us!, Like us! let’s grow bigger! You can even participate in our Github Projects!
vba
How to show and select a file in explorer using VBA
To show and select a file in explorer using VBA you can use the following snippet.
This snippet should also work in VB6.
Sample VBA
Dim path As String
Dim explorerPath As String
Dim cmd As String
explorerPath = Environ$("WINDIR") + "\Explorer.exe "
path = "C:\Users\Codesnippets\Desktop\test\Test.xml"
cmd = explorerPath + " /select , " + Chr(34) + path + Chr(34)
Call Shell(cmd, vbNormalFocus)
How to open a file or folder using VBA
To open a file or folder using VBA you can sue the follwing snippet.
This snippet should also work in VB6.
Sample VBA
Dim path As String
Dim explorerPath As String
explorerPath = Environ$("WINDIR") + "\Explorer.exe "
path = "C:\Users\Codesnippets\Desktop\test"
Call Shell(explorerPath + Chr(34) + path + Chr(34), vbNormalFocus)
How to make a multicolumn listbox in VBA
To make a multicolumn listbox in VBA you can use the following snippet.
This snippet should also work in VB6.
Sample VBA
Option Compare Database
Option Explicit
Private Sub Form_Load()
FillListbox
End Sub
Public Sub FillListbox()
Dim i As Integer
Listbox1.Clear
Listbox1.ColumnCount = 4
For i = 0 To 9
Listbox1.AddItem "Row" + CStr(i) + "; Column1"
Listbox1.List(i, 1) = "Row" + CStr(i) + "; Column2"
Listbox1.List(i, 2) = "Row" + CStr(i) + "; Column3"
Listbox1.List(i, 3) = "Row" + CStr(i) + "; Column4"
Next
End Sub
How to create and write a textfile in VBA
To create and write a textfile in VBA you can use the following snippet.
This should also work when using VB6.
Sample VBA
Dim fso as Object
Dim oFile as Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFile = FSO.CreateTextFile("C:\Users\Codesnippets\Desktop\Test.txt")
oFile.WriteLine "This is a sample line!"
oFile.Close
Set fso = Nothing
Set oFile = Nothing
How to split a string into collection in VBA
To split a string into collection in VBA you can use the following snippet.
Sample VBA
Public Function SplitStringToCollection(sInput As String, Optional sSplitter As String = ";") As Collection
Dim lCounter As Long
Dim sTemp() As String
On Error GoTo errHandler
Set SplitStringToCollection = New Collection
sTemp = Split(sInput, sSplitter)
For lCounter = 0 To UBound(sTemp)
If (CStr(Replace(sTemp(lCounter), sSplitter, vbNullString)) <> vbNullString) Then
SplitStringToCollection.Add CStr(Replace(sTemp(lCounter), sSplitter, vbNullString))
End If
Next lCounter
Exit Function
errHandler:
Set SplitStringToCollection = New Collection
'handle the exception your way
End Function
How to sort a collection in VBA
To sort a collection in VBA you can use the following snippet.
This snippet is using bubblesort.
Sample VBA
Public Function SortCollection(colInput As Collection) As Collection
Dim iCounter As Integer
Dim iCounter2 As Integer
Dim temp As Variant
Set SortCollection = New Collection
For iCounter = 1 To colInput.Count - 1
For iCounter2 = iCounter + 1 To colInput.Count
If colInput(iCounter) > colInput(iCounter2) Then
temp = colInput(iCounter2)
colInput.Remove iCounter2
colInput.Add temp, temp, iCounter
End If
Next iCounter2
Next iCounter
Set SortCollection = colInput
End Function
for more informations see Bubble sort
How to get the Temp folder in VBA
to get the temp folder in VBA you can use the following snippet.
Sample VBA
Public Function GetTempFolder() As String
GetTempFolder = Environ("Temp")
End Function
How to read a textfile line by line in VBA
To read a textfile line by line in VBA you can use the following snippet.
To read a textfile complete into a string you can use How to read a textfile in VBA.
Sample VBA
public sub ReadTextFileLinebyLine()
Dim sFileName As String
Dim iFile As Integer
Dim sLine As String
on error goto errorhandler
sFileName = "C:\Users\Codesnippets\Desktop\Test.txt"
If Len(Dir$(sFileName)) = 0 Then
Exit Sub
End If
iFile = FreeFile()
Open sFileName For Input As iFile
Do While Not EOF(iFile)
Line Input #iFile, sLine
Debug.Print sLine
Loop
Close iFile
exit sub
errorhandler:
'handle the error your way
End Sub