• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automating pasting data from .txt files to excel

dotprogrammer

New Member
As shown below I need to automate opening/saving/closing excel after pasting data from .txt files.

The files are actually .cty files but will be opened using notepad.

The first excel file listed corresponds to the first .cty file, and the second excel file to the second .cty file, etc.

The excel file contains 4 tabs but I need the data pasted into two of those tabs, in cell A2 for both tabs as well. The tabs are titled "M6RURSpdVMT" and "M6URBSpdVMT". After pasting into cell A2, the data needs Text to Columns applied to it. (For Text to Columns it needs to be "Fixed width" and then "Finish").

Afterwards, the excel file can be saved and closed and then move onto the next set of files.

Please let me know if you need more details, thanks!

78517
 

Attachments

  • 1650284469479.png
    1650284469479.png
    63 KB · Views: 2
As guessing can't be coding so all I can say is to start from activating the Macro Recorder and operating manually …​
 
As guessing can't be coding so all I can say is to start from activating the Macro Recorder and operating manually …​
I found a thread where this was somewhat solved previously https://chandoo.org/forum/threads/macro-to-paste-the-data-from-notepad-to-excel.16177/

Code:
Option Explicit
Sub OpentxtSheets()
    Const sPath = "D:\Tests\" 'Change to suit
    Dim sFil As String
    Dim owb As Workbook
    Dim sh As Worksheet

    Set sh = Sheet1 'Change to suit
    sFil = Dir(sPath & "*.txt") 'Note it opens txt format
 
    Do While sFil <> ""
        Set owb = Workbooks.Open(sPath & sFil)
        Range("A1").CurrentRegion.Copy sh.Range("A65536").End(xlUp)(2)
        owb.Close False 'Close don't save
        sFil = Dir
    Loop
End Sub

And then I did a recording of what I wanted done with an excel and notepad file already open and this is what it gave me based on the steps I did:

Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("M6RURSpdVMT").Select
    Range("A2").Select
    ActiveSheet.Paste
    Selection.TextToColumns Destination:=Range("A2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(4, 1), Array(12, 1), Array(20, 1), _
        Array(28, 1), Array(36, 1), Array(44, 1), Array(52, 1), Array(60, 1), Array(68, 1), Array( _
        76, 1), Array(84, 1), Array(92, 1), Array(100, 1), Array(108, 1)), TrailingMinusNumbers _
        :=True
    Sheets("M6URBSpdVMT").Select
    Range("A2").Select
    ActiveSheet.Paste
    Selection.TextToColumns Destination:=Range("A2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(4, 1), Array(12, 1), Array(20, 1), _
        Array(28, 1), Array(36, 1), Array(44, 1), Array(52, 1), Array(60, 1), Array(68, 1), Array( _
        76, 1), Array(84, 1), Array(92, 1), Array(100, 1), Array(108, 1)), TrailingMinusNumbers _
        :=True
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub
 
if you supply an actual sample file, then we can demonstrate a solution. Pictures are absolutely worthless in a scenario like this.
 
Back
Top