One of the issues I frequently come across at work is how to manipulate/change large deimited text files. Importing into excel often casues issues with formatting, and most text editors struggle to manage the large files. By using a simple VBA progam you can simply chosse the fiel, edit the relavant fields and re-output the file in a simple way.
Firstly you will need to create a file dialog to store the file you are opening, and then use a second to save the file you are outputting. This can be done by using the activeX file dialog control, but this can casue issues if you want to share this program with other users. A better approach is to use the microsoft DLL: comdlg32.dll
Start by creating the function, along with a custom type to hold the parameters:
Public Declare Function GetOpenFileName Lib “comdlg32.dll” Alias _
“GetOpenFileNameA” (pOpenFileName As OPENFILENAME) As Long
Public Const OFN_ALLOWMULTISELECT As Long = &H200
Public Const OFN_EXPLORER As Long = &H80000
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
pvReserved As Long
dwReserved As Long
FlagsEx As Long
End Type
I then created 2 further functions in order to populate the open dialog settings, and to manipulate the string if multiple files are selected:
Private Function getfiles() As Collection
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim buff As String
Dim coll1 As New Collection
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = 0
sFilter = “Html Files (*.html)” & Chr(0) & “*.HTML” & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) – 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = “C:\”
OpenFile.lpstrTitle = “Select HTML file(s) from digital flow (CTRL to multiselect)”
OpenFile.flags = OFN_ALLOWMULTISELECT Or OFN_EXPLORER
OpenFile.FlagsEx = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox “A file was not selected!”, vbInformation, _
“Select a file using the Common Dialog DLL”
Else
‘Ammended for multiselect
‘
‘getfiles = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) – 1))
buff = Trim$(Left$(OpenFile.lpstrFile, Len(OpenFile.lpstrFile) – 2))
Do While Len(buff) > 3 And Left(buff, 1) <> vbNullChar
coll1.Add item:=StripDelimitedItem(buff, vbNullChar)
Loop
End If
Set getfiles = coll1
End FunctionPrivate Function StripDelimitedItem(startStrg As String, _
delimiter As String) As String‘take a string separated by nulls,
‘split off 1 item, and shorten the string
‘so the next item is ready for removal.
Dim pos As Long
Dim item As String
pos = InStr(1, startStrg, delimiter)
If pos ThenStripDelimitedItem = Mid$(startStrg, 1, pos – 1)
startStrg = Mid$(startStrg, pos + 1, Len(startStrg))
End IfEnd Function
The final step is to call the get files function. You will also note the select case to capture the number of the files selected:
Dim myfiles As New Collection
Set myfiles = getfiles()
Select Case myfiles.Count
Case Is < 1
Exit Sub
Case Is = 1
ofile = myfiles(1)
Open ofile for input as #1
Case Is > 1
For Each myfile In myfiles
If myfile = myfiles(1) Then
fdir = myfile
Else
If myfile <> “” Then
ofile = fdir & “\” & myfile
”Call a command to open a file and proess
Else
Exit For
End If
End If
Next myfile
End Select
