Thanks, rpeare:
When I try to run the code to export the file, I get the error "variable not defined"
and it highlights the below code (see in red)
Code:
Private Sub cmdExportUpdates_Click()
Dim sFileName
Dim sFinalFileName
Dim fs
Dim fIn, fOut
Dim sContents
Dim qdf As DAO.QueryDef
Dim FSO As Object
sFileName = "C:\Report Database\" & qdf.Name
sFinalFileName = Replace(sFileName, ".txt", "_Final.txt")
Set fs = CreateObject("scripting.filesystemobject")
Set fOut = fs.createtextfile(sFinalFileName)
Set fIn = fs.opentextfile(sFileName, 1)
sContents = fIn.readall
Set fIn = Nothing
fOut.writeline "HDX8" & Format(Now, yyyymmddhhmmss)
fOut.writeline sContents
fOut.writeline "TR" & Format(DCount("*", [UpdatedFile]), "0000000000000000000.00")
Set fOut = Nothing
Set fs = Nothing
MsgBox ("File has been exported.")
End Sub
I tried creating a module with the following
Code:
'format date to "yyyymmddhhmmss"
Public Function formatDateToString(Datetemp As Date) As String
Dim strMth As String, strDay As String, strHr As String
Dim strMin As String, strSec As String
If Len(Month(Datetemp)) = 1 Then
strMth = "0" & Month(Datetemp)
Else
strMth = Month(Datetemp)
End If
If Len(Day(Datetemp)) = 1 Then
strDay = "0" & Day(Datetemp)
Else
strDay = Day(Datetemp)
End If
If Len(Hour(FormatDateTime(Datetemp, vbLongTime))) = 1 Then
strHr = "0" & Hour(FormatDateTime(Datetemp, vbLongTime))
Else
strHr = Hour(FormatDateTime(Datetemp, vbLongTime))
End If
If Len(Minute(FormatDateTime(Datetemp, vbLongTime))) = 1 Then
strMin = "0" & Minute(FormatDateTime(Datetemp, vbLongTime))
Else
strMin = Minute(FormatDateTime(Datetemp, vbLongTime))
End If
If Len(Second(FormatDateTime(Datetemp, vbLongTime))) = 1 Then
strSec = "0" & Second(FormatDateTime(Datetemp, vbLongTime))
Else
strSec = Second(FormatDateTime(Datetemp, vbLongTime))
End If
Dim strTemp As String
strTemp = Year(Datetemp) & strMth & strDay & strHr & strMin & strSec
formatDateToString = strTemp
But I am not sure if that is even relevant and maybe I am going (way) down a dark path...
Anyone that can help, I appreciate it