Macro to Paste the data from Notepad to Excel

[webmax]

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]

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.

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]

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]

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

 

[webmax]

if i run the macro i automattically ask for the path

 

[Smallman]

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