Export textbox content as TXT (notepad) file

[radian89]

Hi all,

need your kind help ,
it sounds silly, but i can't find it anywhere. how to export textbox "NotePad" content in form called "Switchboard", as .TXT (notepad file) with the name & current date?


triggered by command button click

here's the code, i made, but end up error

Private Sub SaveNotePad_Click()

If IsNull(Me.NotePad) = True Then
    MsgBox "Please create a note first", vbOKOnly, "Can't save"
Else
    DoCmd.OutputTo acOutputForm, [forms]![Swichboard]![NotePad], acFormatTXT, "D:\My Document\Kerjaan\freelancer\Access\" & "Notepad " & Format(Date, "dd-mm-yyyy") & ".txt", True
End If


End Sub

thanks a lot,
warm regards,

Adrian

[CJ_London]

perhaps you should look at using the transfertext method

[radian89]

perhaps you should look at using the transfertext method

hi Ajax

how to write the code? but is it not possible if i still using .output?

sorry, I'm still newbie in access

thanks a lot for the reply & help

[Micron]

I believe that to use your method requires that the file exists (not 100% sure). If true, I'd consider the CreateObject("Scripting.FileSystemObject") method because you would be able to open exisiting to read OR append, OR create a new file if it doesn't exist. If your file does not exist at the moment the button is clicked that's one thing, but what if there is a second click of the button on the same day? What to do in either of these cases needs to be known before writing any code.

[Micron]

Had some time to waste (well, not really, but I like to keep my vba skills sharp). Here is a scenario that figures out if the file exists and asks the user what to do about it. You should be able to modify the code to suit your control names and folder path. The function was written to go into the form module since I don't see the rest of the db needing it. If you want to expose the check for a control being null or empty string, suggest you put a function in a standard module, pass the control to it and return a boolean of true or false. You'd call it from this or any other code that needs it. This is one of the few times I'd use a GoTo to control execution; it makes more sense than to repeat code to close or destroy variables in several places. You might want to embellish the error handler in the sub.
P.S. I think this works with the basic project references, but if I'm wrong you will get an error.

Private Sub Command15_Click()
Dim fs, f
Dim fPath As String, strMsg As String, strTxt As String
Dim result As Integer
Const ForReading = 1, ForWriting = 2, ForAppending = 8

On Error GoTo errHandler
If IsNull(Me.Text11) Or Me.Text11 = "" Then
    MsgBox "Please enter information to be written to file."
    Exit Sub
End If

strTxt = Me.Text11
fPath = "C:\Users\blahblah...\Notepad_" & Format(Date, "ddmmyyyy") & ".txt"
Set fs = CreateObject("Scripting.FileSystemObject")

If FileExists(fPath) Then
    strMsg = "File exists; " & vbCrLf & "click Yes to overwrite, No to append or Cancel"
    result = MsgBox(strMsg, vbYesNoCancel, "OVER-WRITE FILE?")
    If result = vbCancel Then GoTo exitHere
    If result = vbYes Then Set f = fs.OpenTextFile(fPath, 2) 'over-write
    If result = vbNo Then
        Set f = fs.OpenTextFile(fPath, 8) 'append
        strTxt = vbCrLf & strTxt 'insert line feed/carriage return before string
    End If
Else
    Set f = fs.CreateTextFile(fPath, 2)
End If

f.write strTxt
Application.FollowHyperlink fPath 'opens the file to display success/failure.

exitHere:
On Error Resume Next
f.Close
Set fs = Nothing
Set f = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

Function FileExists(fPath As String) As Boolean
Dim fs, f

On Error GoTo errHandler
FileExists = True
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(fPath, 8)
'Close f
Set f = Nothing
Set fs = Nothing
Exit Function

errHandler:
'more than 1 err.number can result if file doesn't exist, so just use >0
If Err.Number > 0 Then FileExists = False 'file does not exist
Err.Clear
End Function

[radian89]

Hi Micron,

glad for your reply again , sorry for late reply, mine is blue & trying to answer your question

I believe that to use your method requires that the file exists (not 100% sure). If true, I'd consider the CreateObject("Scripting.FileSystemObject") method because you would be able to open exisiting to read OR append, OR create a new file if it doesn't exist. If your file does not exist at the moment the button is clicked that's one thing, but what if there is a second click of the button on the same day? What to do in either of these cases needs to be known before writing any code.

this is my point of view, but I'm open to any suggestion & discussion.
In my first expectation is for the beginning of the day the file weren't exist at the time the button first clicked, so that it created .txt file. But, if the button click for the second, it will replacing the previous content. my logic based on this code, to export to excel, which will replace the current data if there's already file with the same name in it.

Private Sub ExportExcelByItem_Click()
DoCmd.OutputTo acOutputQuery, "DailyTransForReportingFilterByItem", acFormatXLSX, "D:\filepath\" & "Laporan Transaksi " & Format(Forms!DailyTransSummF.StartDate, "dd-mm-yyyy") & " s_d " & _
Format(Forms!DailyTransSummF.EndDate, "dd-mm-yyyy") & " by item" & ".xlsx", True


End Sub

Had some time to waste (well, not really, but I like to keep my vba skills sharp). Here is a scenario that figures out if the file exists and asks the user what to do about it. You should be able to modify the code to suit your control names and folder path. The function was written to go into the form module since I don't see the rest of the db needing it. If you want to expose the check for a control being null or empty string, suggest you put a function in a standard module, pass the control to it and return a boolean of true or false. You'd call it from this or any other code that needs it. This is one of the few times I'd use a GoTo to control execution; it makes more sense than to repeat code to close or destroy variables in several places. You might want to embellish the error handler in the sub.
P.S. I think this works with the basic project references, but if I'm wrong you will get an error.

Then I read, your second post, and apply your code,
it works like a Christmas tree's light , Thanks a lot for your help. you're already forecasting everything ahead, which I didn't even think about it, and didn't think that it will be much complicated than export to excel. I still need to learn a lot from you.
here's my screenshot, the textbox is under "catatan" label, the purpose i put it on the switchboard to work/act like post it-notes in windows.



warm regards,

Adrian

[Micron]

I'm glad you got it to work!