VBA macro to copy the data from opened text file and paste into excel

[Gaurangg]

Dear Friends,

I need a help in my coding to copy the data from text file and paste into an excel file. I try to explain the scenario below.

As I have already tried to importing the csv file in text format into excel and working fine however I am facing issue in some rows which are pasted into another next rows. However when I open csv file in notepad, copy the data and paste it into excel as text then I am getting the perfect data.

I have tried many codes and methods but I could open the csv file in notepad only but could not copy the data. If anyone please help me how to copy the data of opened csv file in notepad and paste it into excel. it will be great help. Below is my code to open csv file in notepad.

Sub MyTextFile()
Dim MyTxtFile
    MyTxtFile = Shell("C:\WINDOWS\notepad.exe C:\Users\Gaurangg\Desktop\Dump\21st.csv", 1)
    Selection.Copy
    
End Sub

 

[rlv01]

If your file

C:\Users\Gaurangg\Desktop\Dump\21st.csv​

is a comma separated values file, then excel should be able to open it directly.

Sub MyTextFile()
    Dim WB As Workbook
    Set WB = Workbooks.Open(Filename:="C:\Users\Gaurangg\Desktop\Dump\21st.csv")
    ActiveSheet.Columns.AutoFit
    MsgBox WB.Name
End Sub

Are you having problems with that method, and if so, what are the problems? Perhaps you can post something to show us what is going wrong.

 

[Gaurangg]

Dear Friend,

Thanks for your time. I have tried all the possible ways and also can open the file too.. I have tried to make data connection and import the data as text. But the problem is.. Csv file contains tge data of 54 columns and in first column i have 19 digit numerical unique ID of the data. Which is important for me for further calculations. Now when I import the csv file i can get the unique ID as text and all the data but some rows got broke into two rows of excel. And hence cannot calculate that data accurately.

Then I tried another way.. I opened csv file in notepad. Cooied the data & pasted into excel.. And with surprise I got all the data as i wanted. So I had written the code to open csv file in notepad but could not select its data and copy, so i can paste it in excel. I need help to select data of csv file which I had opened in notepad and want to copy.. Rest code i can manage then

 

[rlv01]

<< I have tried all the possible ways and also can open the file too..>>

Perhaps there is something you are not aware of, or some aspect you have not considered. There is a phenomenon called the The XY Problem which in essence means that people get so focused on their solution (in this case, using notepad) that they miss what may be a much better solution to their problem (getting your csv data into an excel worksheet). Using notepad to move data into excel is a very poor solution to your problem. There are better ways, and if you are willing to take the time to explain in detail what your data looks like and some examples of input data and output data I'm sure that someone here will be able to help you.

An example of what I mean by posting data:

Provide a few rows of raw data in C:\Users\Gaurangg\Desktop\Dump\21st.csv

Names,Food,Sports,Weather,Cars
John,Fries,Football,Hot,Mazda
Carl,Beets,Tennis,Cold,Mustang
Jagdish,"Chicken, Rice, Beans",Surfing,Snowy,Prius
Eva,Curry,Skiing,Rainy,Ford
Ramon,Apples;Pears;Grapes,Running,Mild,Chevy
Sarah,Wheat Toast,Baseball,Cloudy,Range Rover
Moon Unit,Eggs,Cricket,Sunnt,Tesla

Show what it looks like after you try to open the file with

Set WB = Workbooks.Open(Filename:="C:\Users\Gaurangg\Desktop\Dump\21st.csv")

Results:
[TABLE="class: grid, width: 413"]
<tbody>[TR]
[TD]Names[/TD]
[TD]Food[/TD]
[TD]Sports[/TD]
[TD]Weather[/TD]
[TD]Cars[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Fries[/TD]
[TD]Football[/TD]
[TD]Hot[/TD]
[TD]Mazda[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]Beets[/TD]
[TD]Tennis[/TD]
[TD]Cold[/TD]
[TD]Mustang[/TD]
[/TR]
[TR]
[TD]Jagdish[/TD]
[TD]Chicken, Rice, Beans[/TD]
[TD]Surfing[/TD]
[TD]Snowy[/TD]
[TD]Prius[/TD]
[/TR]
[TR]
[TD]Eva[/TD]
[TD]Curry[/TD]
[TD]Skiing[/TD]
[TD]Rainy[/TD]
[TD]Ford[/TD]
[/TR]
[TR]
[TD]Ramon[/TD]
[TD]Apples;Pears;Grapes[/TD]
[TD]Running[/TD]
[TD]Mild[/TD]
[TD]Chevy[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Wheat Toast[/TD]
[TD]Baseball[/TD]
[TD]Cloudy[/TD]
[TD]Range Rover[/TD]
[/TR]
[TR]
[TD]Moon Unit[/TD]
[TD]Eggs[/TD]
[TD]Cricket[/TD]
[TD]Sunnt[/TD]
[TD]Tesla[/TD]
[/TR]
</tbody>[/TABLE]