Since function printToTextFile uses some non-built VBA functions, they also must be included in your code for the function to work properly.
Otherwise the following error will occur: Compile error: Sub or Function not defined.
Required functions are listed below. You can get to each function's source code by clicking its name:
When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
'**********************************************************************************************************
' Name: printToTextFile
' Author: mielk | 2012-12-06
'
' Comment: Function to print the given content (String or array) into the specified textfile.
'
' Parameters:
' content Content to be printed out in a textfile.
' filepath The path of a textfile in which the given content should be printed.
' override Optional parameter.
' Determines if the given content should be appended to the existing content of this
' textfile or if it should override this content.
'
' Returns:
' Boolean True - if the given content has been successfully printed.
' False - if any exception has been thrown when trying to print the content into the
' specified filetext.
'
' Exceptions:
' ObjectException Thrown if the [content] parameter is an object.
'
' DimensionsException Thrown if the given array has not given dimensions yet or its number of
' dimensions is greater than 2.
'
' NoAccessToPathException Thrown if the user cannot write into the specified filepath (i.e. he
' has no write-access to this path or it doesn't exist).
'
' FolderCreatingException Thrown if it is impossible to create the parent folder for the
' specified filepath.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-12-06 mielk Function created.
'**********************************************************************************************************
Public Function printToTextFile(content As Variant, filepath As String, _
Optional override As Boolean = False) As Boolean
Const METHOD_NAME As String = "printToTextFile"
'------------------------------------------------------------------------------------------------------
'Checks if [content] parameter is not an object, since objects cannot be printed.
If VBA.IsObject(content) Then GoTo ObjectException
'Check if the current user has the write access for the specified path.
If Not isTextfileWriteable(filepath) Then GoTo NoAccessToPathException
'Create parent folder for the textfile if it is not created yet. If it cannot be created,
'FolderCreatingException is thrown.
If createFolder(getParentFolder(filepath)) Is Nothing Then GoTo FolderCreatingException
'If the parameter [override] is set to True, method deletes the current textfile
'(if this textfile exists). This textfile will be created from scratch later on.
If override Then Call deleteFile(filepath)
'Printing methods are different for arrays and primitive values. ------------------------------------|
If IsArray(content) Then '|
'|
'There are different printing subroutines for 1D arrays and 2D arrays. ----------------------| '|
'The [Select Case] statement below checks how many dimensions the given array has '| '|
'and invokes the appropriate subroutine based on that value. '| '|
Select Case countDimensions(content) '| '|
Case 1: Call printToTextFile_1DArray(content, filepath) '| '|
Case 2: Call printToTextFile_2DArray(content, filepath) '| '|
Case Else '| '|
'It is impossible to print an array if it is not defined yet or if it '| '|
'has more than two dimensions. In this case DimensionsException is thrown. '| '|
GoTo DimensionsException '| '|
End Select '| '|
'--------------------------------------------------------------------------------------------| '|
'|
Else '|
'|
'If the given content is not an array nor object, it must be primitive. '|
'In this case printToTextFile_primitiveValues subroutine is invoked. '|
Call printToTextFile_primitiveValues(content, filepath) '|
'|
End If '|
'----------------------------------------------------------------------------------------------------|
'The given content has been successfully printed.
printToTextFile = True
'==========================================================================================================
ExitPoint:
Exit Function
'----------------------------------------------------------------------------------------------------------
ObjectException:
'(...)
'Put your own error handling here for a case if the given parameter is object and cannot be printed.
GoTo ExitPoint
DimensionsException:
'(...)
'Put your own error handling here for a case if the given parameter is an array but it is not defined
'yet or it has more than two dimensions ...
GoTo ExitPoint
NoAccessToPathException:
'(...)
'Put your own error handling here for a case when the user has no write access for the destination
'path given as a [filepath] parameter ...
GoTo ExitPoint
FolderCreatingException:
'(...)
'Put your own error handling here for a case when the parent folder of the specified filepath could
'have not been created ...
GoTo ExitPoint
End Function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
'**********************************************************************************************************
' Name: printToTextFile_primitiveValues
'
' Description: Submethod to print primitive values to a textfile.
'**********************************************************************************************************
Private Sub printToTextFile_primitiveValues(content As Variant, filepath As String)
Const METHOD_NAME As String = "printToTextFile_primitiveValues"
'------------------------------------------------------------------------------------------------------
Dim intFile As Integer
'------------------------------------------------------------------------------------------------------
intFile = VBA.FreeFile()
Open filepath For Append As #intFile
Print #intFile, content
Close intFile
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
'**********************************************************************************************************
' Name: printToTextFile_1DArray
'
' Description: Submethod to print one-dimensional array.
'**********************************************************************************************************
Private Sub printToTextFile_1DArray(content As Variant, filepath As String)
Const METHOD_NAME As String = "printToTextFile_1DArray"
'------------------------------------------------------------------------------------------------------
Dim intFile As Integer
Dim lngRow As Long
'------------------------------------------------------------------------------------------------------
intFile = VBA.FreeFile()
Open filepath For Append As #intFile
For lngRow = LBound(content, 1) To UBound(content, 1)
Print #intFile, content(lngRow)
Next lngRow
Close intFile
End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
'**********************************************************************************************************
' Name: printToTextFile_2DArray
'
' Description: Submethod to print two-dimensional array to a textfile.
'**********************************************************************************************************
Private Sub printToTextFile_2DArray(content As Variant, filepath As String)
Const METHOD_NAME As String = "printToTextFile_2DArray"
'Constant to define a separator for columns in the array being printed.
Const SEPARATOR As String = ";"
'------------------------------------------------------------------------------------------------------
Dim intFile As Integer
Dim lngRow As Long
Dim lngCol As Long
Dim strCol As String
'------------------------------------------------------------------------------------------------------
intFile = VBA.FreeFile()
Open filepath For Append As #intFile
For lngRow = LBound(content, 1) To UBound(content, 1)
'Value of the parameter [sCol] is cleared in every iteration
'in order not to store a content from the previous array rows.
strCol = ""
'The loop below appends the value of each cell in the particular
'row to [sCol] parameter, separating them from each other with
'a character defined in [SEPARATOR] constant.
For lngCol = LBound(content, 2) To UBound(content, 2)
strCol = strCol & content(lngRow, lngCol) & SEPARATOR
Next lngCol
'Separator appended after the last item is being cut ...
If VBA.Len(strCol) Then
strCol = VBA.Left$(strCol, VBA.Len(strCol) - 1)
End If
'... and ultimately the string stored in [sCol] parameter
'is being printed to a textfile.
Print #intFile, strCol
Next lngRow
Close intFile
End Sub