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!
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!
To show and select a file in explorer using VBA you can use the following snippet.
This snippet should also work in VB6.
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)
To open a file or folder using VBA you can sue the follwing snippet.
This snippet should also work in VB6.
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)
To make a multicolumn listbox in VBA you can use the following snippet.
This snippet should also work in VB6.
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
To create and write a textfile in VBA you can use the following snippet.
This should also work when using VB6.
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
To split a string into collection in VBA you can use the following snippet.
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
To sort a collection in VBA you can use the following snippet.
This snippet is using bubblesort.
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
to get the temp folder in VBA you can use the following snippet.
Public Function GetTempFolder() As String GetTempFolder = Environ("Temp") End Function
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.
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