VBA SCRIPT to copy data from note pad and paste in sheet1 of the macro excel file

[Chanduraj]

Hi There,

The query that i have is i have to copy data from a text file which is from C:drive and the name of which is brazil.txt

I want to copy the data from this text file and paste in sheet1 of the excel macro sheet from which i run the macro.

Can you please assist me on this?

Thanks in advance!
Chandra Shekar. Y

 

[SirJB7]

Hi, Chanduraj!

Welcome to Chandoo forums!
Checked this yet?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

About your question, have you searched in this site for similar problems? I think that this topic has been treated many times. Despite of this, if you use the built-in macro recorder you'll get this code, which does the job:

Option Explicit

Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Brazil.txt", _
        Destination:=Range("$A$1"))
        .Name = "Brazil"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Regards!

 

[Chanduraj]

Hi, Chanduraj!

Welcome to Chandoo forums!
Checked this yet?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

About your question, have you searched in this site for similar problems? I think that this topic has been treated many times. Despite of this, if you use the built-in macro recorder you'll get this code, which does the job:

Option Explicit

Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Brazil.txt", _
        Destination:=Range("$A$1"))
        .Name = "Brazil"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Regards!

Hi There,

Thanks a lot for the reply!

I tried with the code given where i endup getting an error stating




Even after the file is saved in C-drive




Request you to please provide your inputs to fix this bug.

If you can attach the spread sheet with the macro will help me a lot!

Thanking you in advance!
Chandra Shekar. Y

 

[SirJB7]

Hi, Chanduraj!

In the last image you can see that the file "Brazil.txt" isn't of file type text but a RET file (https://fileinfo.com/extension/ret) of Business Objects from SAP, so I presume that the full file name is "C:\Brazil.txt.ret".

Why? Windows OS installations set hiding filename extensions for known filetypes (i.e., for file types which are recognized/handled by installed software, .txt files by Notepad, .xl* files by Excel, etc.). For enabling this open Windows Explorer, Folder Options (under File or Tools, depending on your Windows version), Search & Folder Options, View, Advanced Setup, uncheck Hide File Extensions for Know File Types (or something alike).

Then go back to the root folder of drive C and check the full name. Adjust it accordingly in the VBA code.

Regards!

 

[Chanduraj]

Thanks a lot Sir!

Your previous inputs have served the need and I am able to get the details as i need

Once again thanks for a very quick and accurate response!

 

[SirJB7]

Hi, Chanduraj!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards