range name as file name

[Eddy Stan]

hi
xl filename and txt file name are names trimed from value in range a1 as
below, so excel will have open excel file &

dim txt_filename, filepath as string
filepath = "C:\Documents and Settings\Administrator\Desktop\"
txt_filename = filepath & range("a1").value & ".xls"

unable to open text file ? code is below. cant we use string in a1 cell as
text file name, ofcourse with prefix path & suffix ".txt", please correct the
code below

Workbooks.OpenText Filename:=txt_filename, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, 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), Array(11, 1), Array(12, 1), Array(13, 1),
Array(14, 1), Array(15 _
, 1), Array(16, 1)), TrailingMinusNumbers:=True

' why i am not able to activate file with variable name

Workbooks(xl_filename).Activate
any help on this please

 

[JLGWhiz]

It looks like you are trying to open an .xls file as a .txt file. I believe
the OpenText method is for files that have the .txt estension.

 

[Eddy Stan]

Hi
I want to keep macro in test1.xls and run macro, cell a1 has name of text
file.
The macro has to open test1.txt file in excel, add a workbook named as
test1.xls, copy from test1.txt and paste as values in test1.xls
excel file created
sub extract()
call add1 ' to create an excel file
open text file -- error is coming
copy from xldown to xlright - hope no error as it is created by excel
open excel file ' jack.xls --- error is coming
then paste from cell a2
save & close excel file
but dont save txt file in excel
all this i have put in button
thank for quick response. hope to get my issue resolved .
eddy stan


end sub
sub add1()
Workbooks.Add
ChDir xl_path
ActiveWorkbook.SaveAs Filename:=xl_file
end sub

 

[JLGWhiz]

What I was trying to point out is that when you assign the filename variable
to a file with a .xls estension like this:

txt_filename = filepath & range("a1").value & ".xls"

And then try to open it as a text file like this:

Workbooks.OpenText Filename:=txt_filename

It won't work. The extension has to be .txt to open a text file.