VBA - Copy data from Excel to .txt file , save txt file

JoeRooney

Board Regular
Joined
Nov 27, 2017
Messages
169
Office Version
  1. 365
Hi,

I have some code that copies data from excel to a .txt file . I can successfully copy the data into the .txt file but I am having two issues.

1. Is there a way that the code opens the .txt workbook in notepad ++ rather than notepad
2. I am trying to add a filename and save the .txt file but the code I have is not working, I copied some code that works for excel in naming and saving documents but it is not working for the txt file.

Any help is greatly appreciated.

Code below

VBA Code:
Sub CopytoNotepad()

Dim fName As String
Dim wbDest As Workbook

Sheets("POL").Select

    Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

Shell "notepad.exe", vbNormalFocus

SendKeys "^V"

SendKeys "^{HOME}"

fName = "EXL4.YT.LR.YTJAPAPF.F025.A.D" & Format(Date, "yymmdd") & ".P.F" & ".txt"

fName = Application.GetSaveAsFilename(FileFilter:="*.txt (*.txt), *.txt", Title:="Save As")
If fName = "" Then Exit Sub

End Sub
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try another approach to save data in a txt:

VBA Code:
Sub CopyToTxtFile()
  Dim sh As Worksheet, fName As String, nFileNum As Integer, c As Range
  Set sh = Sheets("POL")
  fName = "EXL4.YT.LR.YTJAPAPF.F025.A.D" & Format(Date, "yymmdd") & ".P.F" & ".txt"
  '
  nFileNum = FreeFile
  Open ThisWorkbook.Path & "\" & fName For Output As #nFileNum
    For Each c In sh.Range("A4:A" & sh.Range("A" & Rows.Count).End(xlUp).Row)
      Print #nFileNum, c.Value
    Next
  Close #nFileNum
  MsgBox "End"
End Sub
 
Upvote 0
Hi DanteAmor,

Thank you for your suggestion but the above code is not creating a txt or coping the data.

When I run the full code I am getting the message but looks like nothing else has happened.
 
Upvote 0
Another method:
VBA Code:
Public Sub Save_As_Text()
    With Workbooks.Add(xlWBATWorksheet)
        Range(ThisWorkbook.Worksheets("POL").Range("A4"), ThisWorkbook.Worksheets("POL").Range("A4").End(xlDown)).Copy .Worksheets(1).Range("A1")
        Application.DisplayAlerts = False
        .SaveAs ThisWorkbook.Path & "\EXL4.YT.LR.YTJAPAPF.F025.A.D" & Format(Date, "yymmdd") & ".P.F" & ".txt", FileFormat:=xlTextWindows
        Application.DisplayAlerts = True
        .Close False
    End With
End Sub
PS - you have to open the .txt file (saved in same folder as the workbook) yourself in Notepad to see its contents.
 
Upvote 0
You must have data in the "POL" sheet and in column A from the A4 range and down.
Check in the folder where you have the file with the macro, there you will create the txt file
 
Upvote 0
Thanks John for your suggestion , I have tried this method previously and when I paste it into the other excel sheet for some reason it messes the formatting in some rows , the data held in each cell is large with lots of blank spaces in between data so I don't think that helps.

As my original code is doing what I need it to bar the saving piece I would like to keep this and add a saveas piece to this code if it is possible.
 
Upvote 0
As my original code is doing what I need it to bar the saving piece I would like to keep this and add a saveas piece to this code if it is possible.

You tried to try my code again.

You must have data in the "POL" sheet and in column A from the A4 range and down.

Did you check your folder for the file?
 
Upvote 0
Hi DanteAmor,

I have tried your code again but it doesn't seem to do anything. I think the problem is it is not selecting any data to copy
 
Upvote 0
it is not selecting any data to copy
Did you modify the macro?

The macro does not select anything.
The macro works with the data in column A from cell A4 and to the last cell with data in column A on the "pol" sheet.

I attached my file of test so you can review.
The result is a "test.txt" file in the same folder where you put the file with the macro.

 
Upvote 0
@DanteAmor - found this thread and your code worked well for me. However, how do I tweak it so the .txt file stays open?

i.e. i want it to save in the folder, but it would be good if the file is opened and I can read the copied data in the newly created txt file.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top