Trying to redo my Export Button Function on my document

Question

I was initially using Send Keys within my code, which works but isn't always fully functional; occasionally, while the code is executing, some steps will be completed more quickly than others, other times it won't copy at all and will just create a blank document, and finally, it will become stuck on a blank notepad opening. I'm looking for a more comprehensive export and save option, so any assistance is greatly valued. To further ensure that each step was completed, I included DoEvents, but it didn't help either.

ub export_one_column()

    Dim fdObj As Object
    Application.ScreenUpdating = False
    Set fdObj = CreateObject("Scripting.FileSystemObject")
    If fdObj.FolderExists("C:\Call_Log") Then
    Else
        fdObj.CreateFolder ("C:\Call_Log")
        MsgBox "Call Log Folder has been created in your C\ Folder.", vbInformation, "Creating Folder"
    End If
    Application.ScreenUpdating = True


  Sheets("PrintTXT").Select
  Columns(3).Select
  Selection.Copy
        Shell "notepad.exe", 3
        DoEvents
        SendKeys "^v"
        DoEvents
        SendKeys "^s"
        DoEvents
        SendKeys "C:\Call_Log" & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt"            '<<==== Change
        DoEvents
        SendKeys "{ENTER}"
        DoEvents
        SendKeys "%fx"
        DoEvents
        SendKeys "{NUMLOCK}", True
    
    Sheets("ENTRY FORM").Select
End Sub

Answer 1

You could directly publish the data to a text file with VBA rather than SendKeys. Here is an illustration of how you could change your code to accomplish this:

Sub export_one_column()
    Dim fdObj As Object
    Dim filePath As String
    Dim fileNum As Integer
    Dim cellValue As String
    Dim rowNum As Long

    Application.ScreenUpdating = False

    Set fdObj = CreateObject("Scripting.FileSystemObject")

    If Not fdObj.FolderExists("C:\Call_Log") Then
        fdObj.CreateFolder ("C:\Call_Log")
        MsgBox "Call Log Folder has been created in your C\ Folder.", vbInformation, "Creating Folder"
    End If

    filePath = "C:\Call_Log\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt"

    fileNum = FreeFile

    Open filePath For Output As #fileNum

    With Sheets("PrintTXT")
        For rowNum = 1 To .Cells(.Rows.Count, 3).End(xlUp).Row
            cellValue = .Cells(rowNum, 3).Value
            Print #fileNum, cellValue
        Next rowNum
    End With

    Close #fileNum

    Shell "notepad.exe " & filePath, 3

    ' Wait for Notepad to open
    Application.Wait Now + TimeValue("00:00:01")

    ' Send the Alt+F4 key combination to close Notepad
    SendKeys "%{F4}", True

    Sheets("ENTRY FORM").Select

    Application.ScreenUpdating = True 
End Sub

The Print statement is used in this modified code to loop through the data from column 3 of the "PrintTXT" sheet and write it to a text file. The Shell statement is then used to open the file in Notepad. As opposed to utilising SendKeys to enter the file path, the Shell statement now accepts it as an argument.