VBA, Access; , "" VBA - .
, , . :
- VBA ;
- , VBA ,
, , csv
;
- ... -
line-by-line, , ,
- .
VBA , : , VBA, , ( : http://www.aivosto.com/vbtips/stringopt2.html#huge) - join, split replace myString = MyString MoreString
RecordSet.GetRows() : , "" , .
, :
Public Function RecordsetToCSV (ByRef ADODB.Recordset, _ ByRef OutputFile As String, _ ByRef FieldList , _ ByVal CoerceText As Boolean = True, _ ByVal CleanupText As Boolean = True _ )
' csv .
'
" : pun. - " "" 2.
' ANSI 2- . ,
" " "ANSI " "
': "" .
Dim strHeader As String
Dim arrHeader()
,
rst.State <> 1 Then Exit Function
strPath =" " strPath = TempSQLFolder
Right (strPath, 1) <>" \ "Then strPath = strPath " \ "
strExtn = FileExtension (strFile) strExtn =" " strExtn =".csv" strFile = strFile strExtn
OutputFile = strPath strFile
"Then Len (VBA.FileSystem.Dir(OutputFile, vbNormal)) <> 0 Then
Err.Clear VBA.FileSystem.Kill OutputFile ' Err.Number = 70 : OutputFile = FileStripExtension (OutputFile) " _" FileStripExtension (FSO.GetTempName) FileExtension (OutputFile)
ReDim FetchArray ( 0 rst.Fields.Count, 0 0)
j = LBound (FetchArray, 1) To UBound (FetchArray, 1) - 1 1 FetchArray (j, i_UBound) = rst.Fields(j).Name j
varField In FieldList j_UBound = j_UBound + 1 varField
ReDim arrTemp2 (j_LBound To j_UBound) varField In FieldList FetchArray (j, i_UBound) = CStr (varField) j = j + 1 varField
i_LBound = LBound (FetchArray, 2) i_UBound = UBound (FetchArray, 2)
UBound (arrTemp1) <> i_UBound + 1 Then ReDim arrTemp1 (i_LBound To i_UBound + 1) arrTemp1 (i_UBound + 1) = vbNullString '' Join ' - End If ' ( )
UBound (arrTemp2) <> j_UBound Then ReDim arrTemp2 (j_LBound To j_UBound)
' . , VBA String
= i_LBound To i_UBound 1
" ... FetchArray (i, j)? , j ? 'FetchArray RecordSet.GetRows(), TRANSPOSED: j ' , (i) (j) .
j = j_LBound To j_UBound
IsNull (FetchArray (j, i)) arrTemp2 (j) =" " arrTemp2 (j) = FetchArray (j, i) '? .
CleanupText Or (i_UBound = 0) Then '(i_UBound = 0):
arrBytes = arrTemp2 (j) ' , 'this: VBA < BR/> k = LBound (arrBytes) UBound (arrBytes) 2 < BR/> arrBytes (k) 10, 13, 9, 160 arrBytes (k + 1) = 0 arrBytes (k) = 32 ' CR, LF, Tab End If ' ANSI 44 Not CoerceText Then arrBytes (k + 1) = 0 arrBytes (k) = 32 ' ANSI 34 arrBytes (k + 1) = 0 arrBytes (k) = 39 ' End Select < BR/> k < BR/> arrTemp2 (j) = arrTemp2 (j) < BR/> End If 'cleanup < BR/> < BR/> CoerceText Then ' , < BR/> arrTemp3 (1) = arrTemp2 (j) arrTemp2 (j) = $(arrTemp3, vbNullString) < BR/> ElseIf (i = 0) (i = i_UBound) " < BR/> arrTemp3 (1) = arrTemp2 (j) arrTemp2 (j) = $(arrTemp3, vbNullString) < BR/> Else ' , : ' * , ADODB: " , "" . < BR/> < BR/> arrBytes = arrTemp2 (j) < BR/> boolNumeric = True < BR/> k = LBound (arrBytes) UBound (arrBytes) 2 arrBytes (k) 43 arrBytes (k) > 57 < BR/> arrBytes (k) <> 69 boolNumeric = False k > UBound (arrBytes) - 5 boolNumeric = False ElseIf arrBytes (k + 2) = 45 "" 1.234E-05 " ElseIf arrBytes (k + 2) = 43 "" 1.234E + 05" boolNumeric = False End If < BR/> k < BR/> boolNumeric Then k = 1 + LBound (arrBytes) UBound (arrBytes) 2 arrBytes (k) <> 0 boolNumeric = False k End If < BR/> arrBytes = vbNullString < BR/> boolNumeric Then ' , arrTemp3 (1) = arrTemp2 (j) arrTemp2 (j) = Join (arrTemp3, vbNullString) End If < BR/> End If 'CoerceText < BR/> j < BR/> arrTemp1 (i) = Join (arrTemp2, COMMA) < BR/>
< BR/>
iRowCount = iRowCount + - 2 < BR/> < BR/>
'**** " VBA STRING VARIABLE **** ****
'
'Put #hndFile, StrConv (Join (arrTemp1, EOROW), vbUnicode)
'Put #hndFile, Join (arrTemp1, EOROW)
'
" Unicode, Wide UTF-16 PUT, " Unicode Byte Order" ,
' Microsoft JET ODBC ACE-OLEDB SQL
'( ,
' . PUT as-is.
'
'**** **** **** **** **** **** **** **** **** **** **** **** * *** **** **** < BR/> < BR/>
arrBytes = Join $(arrTemp1, vbCrLf) < BR/> < BR/>
hndFile = 0 Then < BR/> i_Offset = 1 Len (Dir (OutputFile)) > 0 VBA.FileSystem.Kill OutputFile End If < BR/> WaitForFileDeletion OutputFile < BR/> hndFile = FreeFile OutputFile # hndFile < BR/>
End If < BR/> < BR/>
#hndFile, i_Offset, arrBytes
i_Offset = i_Offset + 1 + UBound (arrBytes)
Erase arrBytes < BR/> < BR/>
rst.EOF Then FetchArray FetchArray =
IsMissing (FieldList) Then FetchArray = rst.GetRows(FETCH_ROWS) FetchArray = rst.GetRows(FETCH_ROWS, FieldList)
End If < BR/>
>
Loop ' , (FetchArray)
iRowCount < 1 ' iRowCount = 0 '
hndFile <> 0 Then #hndFile
ErrSub:
FilePath (Path As String) As String
' ,
' char
Dim strPath As String
Dim arrPath() As String
' :
", ,
Dim strPath As String
Dim arrPath() As String
" : " , ".
' , : ".txt", "txt"
" , FileExtension
Dim strFile As String
Dim arrFile() As String
Const DOT_EXT As String * 1 = "."
0 Then FileExtension = DOT_EXT FileExtension
" : " , "." , .
Dim strFile As String
Dim arrFile() As String
Const DOT_EXT As String * 1 = "."
strFile = _ ()
" ", :
- _()
- FilePath()
- FileStripExtension()
: - , , -. , , ; , .
, , .