Henrik,
To answer your second question first: you cannot run an Excel macro from the
command line.
To give a little help for the first part of your request, the following
code, saved in a workbook will work its way through all .xls files in the
same folder with that workbook (except itself) and save each one to the same
folder as a tab delimited .txt file. So what you could do is put this file
and copies of all the files to be converted into a folder and then open the
workbook with this code and run the macro. To permit running it against .xls
files that are scattered about in multiple folders or to pick and choose from
ones in an existing folder would require different coding with more user
interaction. Hope this helps you some. To put this code into a workbook:
create a new workbook in Excel. Press [Alt]+[F11] to open the VB Editor and
choose Insert | Module from its menu. Copy and paste the code below into the
module that was inserted. Save the workbook (it must be saved before the
code will work). After saving it once, it will work properly in the future.
Now anytime you want to use it, just copy the .xls files to be converted into
the same folder with the workbook just created.
Sub SaveOtherWorkbooksAsDelimitedText()
'
' Saves all other workbooks in the same folder
' with this file as Tab Delimited .txt files
'
'
Dim any_xls_File As String
Dim rootPath As String
Dim myName As String
Dim newFileName As String
'get name of this workbook to prevent converting it
myName = ThisWorkbook.Name
'get location/folder to process
rootPath = Left(ThisWorkbook.FullName, _
InStrRev(ThisWorkbook.FullName, "\"))
any_xls_File = Dir$(rootPath & "*.xls")
'to speed things up, we don't show what's
'going on with opens/closes of the files
Application.ScreenUpdating = False
'begin looping through possible files
Do While any_xls_File <> ""
If any_xls_File <> myName Then
'change filename to .txt
'and create well formed path for it
newFileName = rootPath & _
Left(any_xls_File, Len(any_xls_File) - 3) & "txt"
'make sure we open proper file
any_xls_File = rootPath & any_xls_File
Application.DisplayAlerts = False ' no prompts
Workbooks.Open Filename:=any_xls_File
ActiveWorkbook.SaveAs Filename:=newFileName, _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
End If
'get next possible filename
any_xls_File = Dir$()
Loop
'work is completed
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "All .xls files have been saved as Tab Delimited .txt files."
End Sub
