Trying to redo my Export Button Function on my document

0 votes

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
Mar 30, 2023 in Others by Kithuzzz
• 38,020 points
410 views

1 answer to this question.

0 votes

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.

answered Mar 30, 2023 by narikkadan
• 63,720 points

Related Questions In Others

+1 vote
1 answer

Hi I am unable to see my registered course on my home page

Hey Rushikesh, If you have registered with Edureka, ...READ MORE

answered Mar 16, 2020 in Others by Sirajul
• 59,230 points
1,840 views
0 votes
0 answers
0 votes
1 answer

It shows black screen when trying to load Map on device with ionic 2 Google Map Native plugin

In order to answer your question, start ...READ MORE

answered Feb 8, 2022 in Others by Rahul
• 9,680 points
1,699 views
0 votes
1 answer

datatable remove column on export to pdf and excel

When we are using jquery datatable for ...READ MORE

answered Feb 17, 2022 in Others by gaurav
• 23,260 points
4,104 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,088 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,504 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
807 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
976 views
0 votes
1 answer

Excel MATCH function is not working on an array but works once directed to the matched value

According to the definition here: support.microsoft.com/en-us/office/… if you do ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,720 points
844 views
0 votes
1 answer

Excel: How to use LEFT function on a Date formatted cell?

Use this: =TEXT(E1,"hh:mm:ss") READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,720 points
606 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP