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

Fetching data from notepad to excel

R

r@1234

Member
I have a notepad in notepad there is data
i want that data should be putted from notepad to excel
I have attached the notepad and sample file also plz have a look sir and help me out in solving the same
all files are located in different place & vba macro is placed in a seperate file maro.xlsm
 

Attachments

  • DF.txt
    908.9 KB · Views: 11
  • Sample1.xlsx
    768.5 KB · Views: 8
Code: 
Option Explicit
Sub CombineTextFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String
    
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    sDelimiter = "|"
    
    FilesToOpen = Application.GetOpenFilename _
    (FileFilter:="Text Files (*.txt), *.txt", _
    MultiSelect:=True, Title:="Text Files to Open")
    
    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If
    
    x = 1
    Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
    wkbTemp.Sheets(1).Copy
    Set wkbAll = ActiveWorkbook
    wkbTemp.Close (False)
    wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
    Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, Semicolon:=False, _
    Comma:=True, Space:=False, _
    Other:=True, OtherChar:=False
    x = x + 1
    
    While x <= UBound(FilesToOpen)
        Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
        With wkbAll
            wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
            .Worksheets(x).Columns("A:A").TextToColumns _
            Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Tab:=False, Semicolon:=False, _
            Comma:=False, Space:=False, _
            Other:=True, OtherChar:=sDelimiter
        End With
        x = x + 1
    Wend
    
ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub
 

Attachments

  • Text Files Import.xlsm
    17.9 KB · Views: 6
Using Power Query/Get and Transform
On the Data Tab of the Ribbon-->From Text/CSV-->Import-->Transform Data-->Close and Load
 
r@1234 said:
I have a notepad in notepad
Click to expand...
Notepad is a Windows application !​
You just have a text file you can already import / open directly in Excel without any code !​
If really a code is needed, activate the Macro Recorder and just operate and well answer to the Import Assistant, at kid level …​
​
Another kid level tip : just reading the VBA help of Workbooks.Open (or even OpenText) method​
as it needs only no more than a single codeline / statement to open the text file and parse data to columns ‼​
​
 
Code: 
Option Explicit
Sub CombineTextFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String
    
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    sDelimiter = "|"
    
    FilesToOpen = Application.GetOpenFilename _
    (FileFilter:="Text Files (*.txt), *.txt", _
    MultiSelect:=True, Title:="DF")
    
    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If
    
    x = 1
    Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
    wkbTemp.Sheets(1).Copy
    Set wkbAll = ActiveWorkbook
    wkbTemp.Close (False)
    wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
    Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=True, Semicolon:=False, _
    Comma:=True, Space:=False, _
    Other:=False, OtherChar:=False
    x = x + 1
    
    While x <= UBound(FilesToOpen)
        Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
        With wkbAll
            wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
            .Worksheets(x).Columns("A:A").TextToColumns _
            Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Tab:=True, Semicolon:=False, _
            Comma:=True, Space:=False, _
            Other:=False, OtherChar:=sDelimiter
        End With
        x = x + 1
    Wend
    
ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

No Doubt this code is perfect but i am curious to know that can we mentioned the path and file name(DF.txt) in the macro, So that it can't pop up and ask for the file to select (i don't want manual work, so if its possible then plz make a macro that i have to only run it and rest it will do )
 
Far than perfect as I yet wrote no more than an unique codeline / statement is necessary to open the file and parse data to columns !​
Future belongs to smart readers …​
​
 
" can we mentioned the path and file name(DF.txt) in the macro "

Will the location and the filename ALWAYS be the same ?
 
No, location can be anywhere & name can be anything Logit Sir
plz assume any location u want and assume any name u want (after getting the code i will mentioned the desired location and then i will think what to name file and then i will name it & i will edit the code according to the same)
 
.
Try this :

Code: 
Option Explicit

Sub Sample()
    Dim MyData As String
    Dim lineData() As String, strData() As String, myFile As String
    Dim i As Long, rng As Range

    On Error Resume Next
    
    ' This is the line to edit for the file location and name
    myFile = "C:\Users\gagli\Desktop\DF.txt"

    Open myFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    
    ' Split into wholes line
    lineData() = Split(MyData, vbNewLine)
    Set rng = Range("A2")
    
    ' For each line
    For i = 0 To UBound(lineData)
    
        ' Split the line
        strData = Split(lineData(i), "|")
        
        ' Write to the sheet
        rng.Offset(i, 0).Resize(1, UBound(strData) + 1) = strData
        
    Next
    
    Range("A:A").Select
    
    'Text To Columns applied to the pasted data
     Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), _
        TrailingMinusNumbers:=True
        
    'Adjust all of the columns so the data presentation is appealing
    Worksheets("Sheet1").Columns("A:Z").AutoFit
    
    'Select Cel A1
    Range("A1").Select
    
End Sub
 
This is also perfect But requires modification logit Sir as i mentioned vba macro will be placed in a seperate file (macro.xlsm)
and the file where we have to get the data is sample1.xlsx
there are two excel files one is macro.xlsm(in this macro code will be placed) & sample1.xlsx (where we have to get the data and paste it to sample1.xlsx)
 
Run a macro contained in another workbook
Note: The workbook which contains the macro must be open. If you need to open the workbook first, check out the code in this post.

To run a macro contained in another workbook, use the Application.Run command as follows:

Code: 
Sub CallAnotherMacro()
Application.Run "'Another Workbook.xlsm'!NameOfMacro"
End Sub
The single quotation marks are needed when the workbook name contains a space.




If there is no space, the single quotation marks are not required. The code will still run correctly if they are included, so I prefer to use them for consistency.

Code: 
Sub CallAnotherMacro()
Application.Run "AnotherWorkbook.xlsm!NameOfMacro"
End Sub
 
Last edited by a moderator:
ogit Sir as i mentioned vba macro will be placed in a seperate file (macro.xlsm)
and the file where we have to get the data is sample1.xlsx
there are two excel files one is macro.xlsm(in this macro code will be placed) & sample1.xlsx (where we have to get the data and paste it to sample1.xlsx)

i don't want any other way to do so
So plz have a relook and help me out for the same i am unable to do so i am geeting error in doing so
 
Problem Solved
Thnaks Alot Logit Sir for giving ur precious time and great support in solving this problem Sir
 
You must log in or register to reply here.
Back
Top