Gina,
I am getting a Compile Error saying Expecting Function or variable when I
hit the enter key. Here is the module coding - the only thing that I changed
was the strTarget = FolderPath line - added the file path to where the export
should go to, and this is what is in the Immediate Window:
?ExportChunks("CA_F4311","C:\Documents and Settings\btyamaj\Desktop","Test
Analysis")
Thank You,
Sub ExportChunks(Source As String, _
FolderPath As String, BaseName As String)
Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim fldF As DAO.Field
Dim lngFN As Long
Dim j As Long
Dim lngChunk As Long
Dim lngChunkCount As Long
Dim strTarget As String
Dim strLine As String
Const MAX_CHUNK = 65536
Const STD_CHUNK = 50000
Const DELIM = Chr(9) 'tab-delimited; change
' argument checking and error trapping omitted
'Get ready
Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset(Source, dbOpenForwardOnly)
rsR.MoveLast 'ensure we get full record count
If rsR.RecordCount = 0 Then
MsgBox "No records to export", vbOKOnly + vbInformation
Exit Sub
ElseIf rsR.RecordCount <= MAX_CHUNK Then
lngChunk = MAX_CHUNK
Else
lngChunk = STD_CHUNK
End If
rsR.MoveFirst
lngChunkCount = 0
Do Until rsR.EOF 'Outer loop: once per file
'Open output file
j = 0
lngChunkCount = lngChunkCount + 1
strTarget = FolderPath & "C:\Docuements and Settings\btyamaj\Desktop\Test
Analysis" & BaseName _
& Format(lngChunkCount, "00") & ".xls"
lngFN = FreeFile()
Open strTarget For Output As #lngFN
Do Until (j = lngChunk) Or rsR.EOF
'inner loop: once per record
'assemble fields into string
strLine = ""
For Each fldF In rsR.Fields
strLine = strLine & CStr(Nz(fldF.Value, "")) _
& DELIM
Next
Print #lngFN, strLine
j = j + 1
Loop 'inner
Close #lngFN
Loop 'outer
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
End Sub