Convert a Notepad file into xls

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

Convert a Notepad file into xls

Post by zyxw1234 »

Hi,
I have a notepad file
I have to convert that file into .xls
Plz see i uploaded the notepad file
notepad file is located in C:\Users\**I've been banned**\Desktop\save it

Code: Select all

Sub Test()
    
    myFile = Application.GetOpenFilename("Text Files, *.txt", , "abc def...")
    If myFile = False Then Exit Sub
    
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=Range("$A$1"))
        .Name = "pipe"
        .TextFilePlatform = 65001
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
End Sub


I need to remove get open filename(file path will be hardcoded in the macro & the data it has to paste is in another path & that path also should be hardcoded in the macro i.e C:\Users\**I've been banned**\Desktop\save it\book1.xls)
Plz note file path may be changed in future & it can be in different location & in different folder
You do not have the required permissions to view the files attached to this post.

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

Re: Convert a Notepad file into xls

Post by HansV »

Change the lines

Code: Select all

    myFile = Application.GetOpenFilename("Text Files, *.txt", , "abc def...")
    If myFile = False Then Exit Sub
to

Code: Select all

    myFile = "C:\Users\**I've been banned**\Desktop\3.txt"
Change the path and filename as needed.
Best wishes,
Hans

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

Re: Convert a Notepad file into xls

Post by zyxw1234 »

Code: Select all

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=Range("$A$1"))
This code will paste the data to Active sheet but we have to paste the data to another file which is not opened, file name is data.xls
and it can be located anywhere
assume this file is located in C:\Users\**I've been banned**\Desktop\New folder

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

Re: Convert a Notepad file into xls

Post by HansV »

You have received code in the past that shows how to open that workbook. Please modify the code yourself.
Best wishes,
Hans

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

Re: Convert a Notepad file into xls

Post by zyxw1234 »

Code: Select all

Sub Test()
Dim Wb1 As Workbook
Set Wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\Alert.xls")
Dim Ws1 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
    
     myFile = "C:\Users\**I've been banned**\Desktop\3.txt"
    
    With Ws1.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=Range("$A$1"))
        .Name = "pipe"
        .TextFilePlatform = 65001
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

I mentioned all this in the macro & i am getting output
If any change is required according to u HansV Sir then plz let me know
Thnx Alot for giving ur precious time to this post & helping me in solving the same
Have a Great Day

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

Re: Convert a Notepad file into xls

Post by HansV »

I would change

Code: Select all

    With Ws1.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=Range("$A$1"))
to

Code: Select all

    With Ws1.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=Ws1.Range("$A$1"))
Best wishes,
Hans

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

Re: Convert a Notepad file into xls

Post by zyxw1234 »

Ok Sir Done
I changed the Same
Thnx For the Help HansV Sir