Code modfication for notepad to excel

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Code modfication for notepad to excel

Post by zyxw1234 »

I have a code & i need some modication of the same So plz have a look and help me in solving the same Sir
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 from notepad and paste it to sample1.xlsx)
all files are located in different path

Files

https://drive.google.com/open?id=1nqetv ... 0Nf3woOGjA
https://drive.google.com/open?id=1I0fh0 ... xv5xQAda3y


Code: Select all

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
    
    
    myFile = "C:UserswsDesktopDF.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
    
    
     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
        
    
    Worksheets("Sheet1").Columns("A:Z").AutoFit
    
    
    Range("A1").Select
    
End Sub

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code modfication for notepad to excel

Post by HansV »

What is the code supposed to do?
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Code modfication for notepad to excel

Post by zyxw1234 »

I have a notepad, in notepad there is data (file name DF.tXt)
i want that data should be putted from notepad to sample1.xlsx in first sheet (sheet name can be anything )

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code modfication for notepad to excel

Post by HansV »

The macro has two sections. The first part reads the text file line by line, then splits the lines. The second part uses TextToColumns to split the lines. You don't need both of these, only one.
Your macro uses "|" as delimiter, but the text file uses "," as delimiter, so you should change that.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Code modfication for notepad to excel

Post by zyxw1234 »

This macro works perfect for me i need a little change HansV Sir,
this macro works when macro is placed in sample1.xlsx(but i will place the macro in macro.xlsm)
so i need that change in the given macro
the file in which macro is placd is different (macro.xlsm in this vba code will be placed) & the data to pasted is in sample1.xlsx
so i need that change in this macro

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code modfication for notepad to excel

Post by HansV »

It is weird that the macro works for you, for
(1) The path/filename of the text file is not valid
(2) The delimiter is a comma, not "|"
(3) The second part that uses TextToColumns is not necessary.

But you can add code at the beginning of the macro that opens Sample1.xlsx. If you don't know how to do that, you can record a macro that opens this workbook. Then copy the code that opens the workbook into your macro.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Code modfication for notepad to excel

Post by zyxw1234 »

I am getting Error that's y i asked for the help HansV Sir


ERROR-C:UserswsDesktopDF.txt
I know this error u r talking about but when i put( ) in this forum (it disappeared )

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code modfication for notepad to excel

Post by HansV »

C:UserswsDesktopDF.txt is obviously not a valid path and filename, so you should correct this to use the correct path of your file.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Code modfication for notepad to excel

Post by zyxw1234 »

it is correctly mentioned in my excel file but when i paste it to the forum it diappears

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Code modfication for notepad to excel

Post by zyxw1234 »

I am getting error when i try to edit this vba code so plz help me for the same

User avatar
Leif
Administrator
Posts: 7193
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Code modfication for notepad to excel

Post by Leif »

zyxw1234 wrote:
19 May 2020, 11:57
it is correctly mentioned in my excel file but when i paste it to the forum it diappears
Which character is not appearing? The backslash ("\")?

Can you confirm the path you are attempting to display is "C:\Users\ws\Desktop\DF.txt" ?
Leif

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Code modfication for notepad to excel

Post by zyxw1234 »

Yes the backlash is not appearing, when i am pasting the code with backlash in this forum

yes the path is perfect "C:UserswsDesktopDF.txt"

User avatar
Leif
Administrator
Posts: 7193
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Code modfication for notepad to excel

Post by Leif »

I can think of no reason why the backslash shouldn't appear.

Code: Select all

C:\Users\ws\Desktop\DF.txt
Can you copy this text:
C:\Users\ws\Desktop\DF.txt
and paste it into a new post?
Leif

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Code modfication for notepad to excel

Post by zyxw1234 »

C:\Users\ws\Desktop\DF.txt

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Code modfication for notepad to excel

Post by zyxw1234 »

Now i got it what happens
I copied this code from a forum and when i directly pasted this to ur forum then that backslash disappeared

User avatar
Leif
Administrator
Posts: 7193
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Code modfication for notepad to excel

Post by Leif »

Then you may find you have similar problems with other parts of your code...
Leif

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Code modfication for notepad to excel

Post by zyxw1234 »

I am not having any issue with this code
This code is perfect for me
i need a little modifcaton in it as i have mentioned

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Code modfication for notepad to excel

Post by zyxw1234 »

instaed of this myFile = "C:\Users\ws\Desktop\DF.txt" we have to put this Set w1 = Workbooks.Open("C:\Users\ws\Desktop\DF.txt"")
we have to use Dim w1 As Workbook & then
Dim Ws1 As Worksheet & Set Ws1 = w1.Worksheets.Item(1) and then the rest process will be same


need for modification is bcoz
vba macro will be placed in a seperate file (macro.xlsm)
and the file where we have to get the data is sample1.xlsx(this is the file where we put the data from notepad to excel)
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)

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code modfication for notepad to excel

Post by HansV »

No, you shouldn't open DF.txt as a workbook. You should open Sample1.xlsx as a workbook.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Code modfication for notepad to excel

Post by zyxw1234 »

Sorry but HansV Sir i am unable to wirte the code for the same, I tried but i am getting error so plz have a look sir and help me in solving this problem Sir