open textfile in local drive and save it as a delimited file

[newbieabc]

Dim strLoad As String
Dim bk As Workbook
Dim sPath As String
sPath = "C:\data\"
strLoad = Dir("C:\data\*.txt")
Do While strLoad > vbNullString
      'code goes here to import into excel
 
    
Workbooks.OpenText Filename:= _
strLoad, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
Space:=True, 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))
Set bk = ActiveWorkbook
bk.SaveAs sPath & Left(bk.Name, Len(bk.Name) - 4) _
 & ".xls", FileFormat:=xlWorkbookNormal
bk.Close Savechanges:=False
      
      strLoad = Dir
  
Loop

Hi I try to use the above code to open all the textfiles in c:\data. I need the script to open the textfiles in excel as save it as a delimited file. If i do it manually, i will have to perform the following steps:

In excel click open-->choose textfile-->delimited-->check space and tab-->next-->finish-->save the file in its original location as a delimited file-->close the file.

I encounter runtime error 1004 when i try to run the code. I am using excel 2003 and the visual basic editor in excel itself. Hope you can help me

Rgds

[westconn1]

strload does not contain the path, only the file name, you need
spath & strload for opening

which line gives error?

[newbieabc]

Hi, runtime error is encountered when i reaches the line of

Workbooks.OpenText Filename:= _
strLoad, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
Space:=True, 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))

[westconn1]

check it is finding the file as you are only passing the filename with no path, see my comment above