Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
save as macro
hello
i need a macro to open a few .xls files , save as tab delimited .txt files.it must be done in one macro and the names of files are variable.how i can do this?? another question: is there any way to run macro from cmd command line. this line will take files names to be converted as argument. Thanks for your help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
save as macro
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 "henrik" wrote: hello i need a macro to open a few .xls files , save as tab delimited .txt files.it must be done in one macro and the names of files are variable.how i can do this?? another question: is there any way to run macro from cmd command line. this line will take files names to be converted as argument. Thanks for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save As Macro | Excel Discussion (Misc queries) | |||
Macro to Save without the Save Message | Excel Discussion (Misc queries) | |||
Save as Macro | Excel Discussion (Misc queries) | |||
Macro Save As | Excel Discussion (Misc queries) | |||
Save a Macro | Excel Discussion (Misc queries) |