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)

Please rate this snippet

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)

Please rate this snippet

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

Please rate this snippet

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  

Please rate this snippet

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

Please rate this snippet

How to read a textfile in VBA

To read a textfile in VBA you can use the following snippet.
to read a textfile line by line see How to read a textfile line by line in VBA.

Sample VBA

Function ReadTextfileComplete(ByVal strFile As String) As String
Dim hFile As Long
    hFile = FreeFile
    Open strFile For Input As #hFile
    ReadTextfileComplete = Input$(LOF(hFile), hFile)
    Close #hFile
End Function

Please rate this snippet

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

Please rate this snippet

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

Please rate this snippet