Private Sub cmdExportStudyData_Click()
On Error GoTo Err_cmdExportStudyData_Click
Dim strStudy, strDate, strFileName, strExportFile, strDoc As String
strDoc = "qryExportStudyData"
strStudy = cmbStudies
strDate = Format(Date, "yyyymmmdd")
strFileName = strStudy & " sero (" & strDate & ").txt"
strExportFile = CurrentProject.Path & "\ExportResults\" & strFileName
If IsNull(cmbStudies) Then
MsgBox "Please select a study first"
Else
DoCmd.TransferText acExportDelim[COLOR=Red], ,[/COLOR] strDoc, strExportFile, False
MsgBox ("Your data file is saved as: " & Chr(13) & strFileName & Chr(13) & Chr(13) & "Here: " & Chr(13) & strExportFile)
End If
Exit_cmdExportStudyData_Click:
Exit Sub
Err_cmdExportStudyData_Click:
Msg = "Error # " & str(Err.Number) & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
Resume Exit_cmdExportStudyData_Click
End Sub
...
DoCmd.TransferText acExportDelim, [COLOR=Red]eqryExportStudyData[/COLOR], strDoc, strExportFile, False
...
strSpec = "eqryExportStudyData"
...
DoCmd.TransferText acExportDelim, strSpec, strDoc, strExportFile, False
Public Function ExportToCSV_D(strSource As String, _
strFileName As String, _
Optional strColumnDelimiter As String = ",", _
Optional blHeaders As Boolean) As Byte
'Exports a table or query or SQL statemtent to a text file. If a SQL is passed
'as the source, enclose it in Parenthesis.
Dim intChannel As Integer
Dim strSQL As String
Dim strCSV As String
Dim x As Integer
'Close any open files
For intChannel = 1 To 511
Close #intChannel
Next intChannel
'Open a channel to communicate with your TEMP file and
intChannel = FreeFile
Open strFileName For Output Access Write As #intChannel
'Write the contents of the table to the file
'Open the source
strSQL = "SELECT * FROM " & strSource & " As vTbl"
With CurrentDb.OpenRecordset(strSQL, 4) 'dbOpenSnapshot = 4
'Write the headers if appropriate
If blHeaders = True Then
For x = 0 To .Fields.Count - 1
strCSV = strCSV & strColumnDelimiter & .Fields(x).Name
Next x
Print #intChannel, Mid(strCSV, Len(strColumnDelimiter) + 1)
End If
'Write the CSV
Do Until .EOF
strCSV = ""
For x = 0 To .Fields.Count - 1
strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "<NULL>")
Next x
Print #intChannel, Mid(strCSV, Len(strColumnDelimiter) + 1)
.MoveNext
Loop
End With
'Close all the files
Close #intChannel
End Function
wikelndt
without trying it myself in A2007, the procedure you describe to manage import/export specs (ie - using the advanced option) seems like its the same mechanism as in other access versions. Are you seeing some differences?
In the query you export, instead of specifying a format property on the date column. Create an expression for the date column that looks something like this:
UCase(Format([YourDateField],"ddmmmyyyy"))
Like "*" & [forms]![frmSpecimens].[cmbDateExport] & "*"
Eval("[forms]![frmSpecimens].[cmbDateExport]")
Eval("*" & "[forms]![frmSpecimens].[cmbDateExport]" & "*")
and
Eval(Like "*" & "[forms]![frmSpecimens].[cmbDateExport]" & "*")
I export to CSV with my own procedure ..
Code:Public Function ExportToCSV_D(strSource As String, _ strFileName As String, _ Optional strColumnDelimiter As String = ",", _ Optional blHeaders As Boolean) As Byte 'Exports a table or query or SQL statemtent to a text file. If a SQL is passed 'as the source, enclose it in Parenthesis. Dim intChannel As Integer Dim strSQL As String Dim strCSV As String Dim x As Integer 'Close any open files For intChannel = 1 To 511 Close #intChannel Next intChannel 'Open a channel to communicate with your TEMP file and intChannel = FreeFile Open strFileName For Output Access Write As #intChannel 'Write the contents of the table to the file 'Open the source strSQL = "SELECT * FROM " & strSource & " As vTbl" With CurrentDb.OpenRecordset(strSQL, 4) 'dbOpenSnapshot = 4 'Write the headers if appropriate If blHeaders = True Then For x = 0 To .Fields.Count - 1 strCSV = strCSV & strColumnDelimiter & .Fields(x).Name Next x Print #intChannel, Mid(strCSV, Len(strColumnDelimiter) + 1) End If 'Write the CSV Do Until .EOF strCSV = "" For x = 0 To .Fields.Count - 1 strCSV = strCSV & strColumnDelimiter & Nz(.Fields(x), "<NULL>") Next x Print #intChannel, Mid(strCSV, Len(strColumnDelimiter) + 1) .MoveNext Loop End With 'Close all the files Close #intChannel End Function
I have an equivalent one for use with ADO as well ... if interested, I'd be glad to post it too ...
Public Function ExportToCSV_A(strSource As String, _
strFileName As String, _
Optional strColumnDelimiter As String = ",", _
Optional blHeaders As Boolean = False) As Byte
'Exports a table or query or SQL statement to a text file. If a SQL is passed
'as the source, enclose it in Parenthesis.
Dim intChannel As Integer
Dim strSQL As String
Dim strHeaders As String
Dim x As Integer
'Close any open files
For intChannel = 1 To 511
Close #intChannel
Next intChannel
'Open a channel to communicate with your TEMP file and
intChannel = FreeFile
Open strFileName For Output Access Write As #intChannel
'Write the contents of the table to the file
'Open the source
strSQL = "SELECT * FROM " & strSource & " As vTbl"
With CurrentProject.Connection.Execute(strSQL, , 1) 'adCmdText = 1
'Build Headers if appropriate
If blHeaders = True Then
For x = 0 To .Fields.Count - 1
strHeaders = strHeaders & strColumnDelimiter & .Fields(x).Name
Next
strHeaders = Mid(strHeaders, Len(strColumnDelimiter) + 1) & vbCrLf
End If
'Write to the CSV file
Print #intChannel, strHeaders & .GetString(2, , strColumnDelimiter, vbCrLf, "<NULL>") 'adClipString = 2
End With
'Close all the files
Close #intChannel
End Function