How to import txt file to a specific sheets in the samw workbook?

[ChanL]

Hi there , I had write the following code to import txt file to excel without opening it.
However, the code bring the data to a new workbook which is not something I want.
What I want is to import the txt file into a sheet name "copy from text" within the same workbook "Data compilation"
Do advise how can I modify my VBA code. Thanks!

Sub import()

dim fname as string

fname= Sheets("main").cells(2,3).value + "\" + "data.txt"

workbook.opentext filename:=fname, origin:=437,startrow:=1, datatype:=xlfixedwidth, textqualifier:=xldoublequote, _
consecutivedelimiter:= false, tab:=false, semicolon:=false, comma:= true, space:=false, other:= false, _
fieldinfo:= array(array(0,1),array(5,1),array(23,1),array(42,1),array(54,1),array(69,1), array(79,1), array(97,1), array(109,1), array(123,1)), _
trailingminusnumbers:=true

end sub 

/CODE]

 

[Marc L]

Hi,​

​

from your actual code you can add a Range.Copy statement …​

​

For a direct import different ways exists.​

You can activate the Macro Recorder and use the Excel feature Import from file in the Data tab …​

 

[ChanL]

Hi,​

​

from your actual code you can add a Range.Copy statement …​

​

For a direct import different ways exists.​

You can activate the Macro Recorder and use the Excel feature Import from file in the Data tab …​

thanks!