• 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.

Macro to Paste the data from Notepad to Excel

webmax

Member
Hi

I have a folder which contains many note pad (.txt files). I want to open all notepad files one by one and copy and paste in the One excel sheet.

I required the macro for the same.

regards
Shahul
 

Smallman

Excel Ninja
Hi Webmax

Without seeing your notpad format something like the following might work for you. Look closely at the file path and the worksheet code object to make sure they match.

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
Take care

Smallman
 

webmax

Member
k thanks. i want the macro code to select the path name automatically (Differ from time to time ie C Drive, D Drive etc). So every time i have to change the path name in the code.
 

Smallman

Excel Ninja
How is the code going to know where to pick the text files up from unless you tell it? You can put the path in a cell so it is out of vba but the code will still need to be told the path to look in.

Take care

Smallman
 
Last edited:

Smallman

Excel Ninja
Ah I really dislike those macros. Excel has a built in File Open method GetOpenFileName, here is an example

sPath = Application.GetOpenFilename(, , "File Path")

where sPath is dimensioned as a string

However XL does not provide a method to browse for a folder. It will require a bit more coding. Actually quite a bit more.

http://www.cpearson.com/excel/browsefolder.aspx

I have always prefered stating in a cell where the path should be.

Take care

Smallman
 
Top