Here is some 'bare bones' code to show how opening all .txt files in a particular folder could be done. But as Hans V has indicated - what do you want to do with them once you get them opened? This code sets up to read from the files one line at a time. But your other code is trying to open them as a Workbook - with uncertain results? But that could also be done. If your Workbooks.OpenText code would work for all of them, let us know - along with what to really do with them after opening them - do you want to simply save them out as a .csv or perhaps even a .xls or .xlsx file?
Sub HandleTextFiles()
Dim folderName As String
Dim textFileName As String
Dim fileBuffer As Integer
folderName = BrowseFolder('Select Folder to Use')
If folderName = ' Then
Exit Sub ' user cancelled the browsing
End If
If Right(folderName, 1) <> Application.PathSeparator Then
folderName = folderName & Application.PathSeparator
End If
textFileName = Dir$(folderName & '*.txt')
Do While textFileName <> '
fileBuffer = FreeFile()
Open folderName & textFileName For Input As #fileBuffer
'the file is now open as a text file and can be read
'one line at a time if you wish
'although you could open it as a workbook if you think
'it will do any good.
'
'once you have finished working with the text file
'close it and go on to the next
Close #fileBuffer
textFileName = Dir$() '
Loop
MsgBox 'All .txt Files Processed', vbOKOnly, 'Task Completed'
End Sub
Exemplo de como importar arquivo texto '*.txt' delimitado para o Excel, usando macro VBA. Link do Projeto: https://drive.google.com/folderview?id=0BxkDTZvaGl. Bloomberg Refresh With Macros March 23rd, 2014, 03:03 I have an excel spreadsheet that will populate the data from bloomberg if I enter the dates manually, I want to create a loop so that I can just enter a month instead, I have tried all different types of application pauses, but no matter what I do it will not work.
Function BrowseFolder(Title As String, _
Optional InitialFolder As String = vbNullString, _
Optional InitialView As Office.MsoFileDialogView = _
msoFileDialogViewList) As String
'courtesy of Chip Pearson:
'http://www.cpearson.com/excel/browsefolder.aspx
Dim V As Variant
Dim InitFolder As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = Title
.InitialView = InitialView
If Len(InitialFolder) > 0 Then
If Dir(InitialFolder, vbDirectory) <> vbNullString Then
InitFolder = InitialFolder
If Right(InitFolder, 1) <> ' Then
InitFolder = InitFolder & '
End If
.InitialFileName = InitFolder
End If
End If
.Show
On Error Resume Next
Err.Clear
V = .SelectedItems(1)
If Err.Number <> 0 Then
V = vbNullString
End If
End With
BrowseFolder = CStr(V)
End Function
The Website for everything ProShow Producer related. Download Free Slide Styles, Transitions, Templates and Tutorials. Watch videos from Producer users and share your own creativity. 
I need to edit a text file i.e add and update the data using the macro. I have a text file having thousands of records, a separate excel sheet which gives the mapping of data in text file. For example, a record in text file looks like:
The mapping excel sheet for this is as below:
Mar 12, 2010 Re: Import.txt File with VBA Problem You don't a specification for a delimited file, and that's what the OP has I think. If posting code please use code tags. Criar macro no Vba Excel ou Vba Access para gerar Txt do Cnab 240 Itau (pagamentos Sispag). Facebook → YouTube → Instagram → Twitter. Re: VBA Macro to save Excel file like '.txt UTF-8' Someone knows how to create a macro to save an excel file like.txt utf8? I need this format, i can do that manually (n it works fine) but i need the automatic way.
Which means that the 1st four characters in record give the name, next 10 give city and next 10 give the country of a particular person and so on. Now, I have to edit a record (one or two fields) for a particular name and save it in same file. Also I have add a new record in the above specified format and then save the file.
The excel sheet just gives the mapping of the fields in the text file. It tells only the field lengths. As u can see in my question, the text is continuos without any delimiter in it. hence the excel sheet is used to identify the variuos fields in the text file. sample record is like:
In the late 2000's he reappeared as a solo performer that including a solo acoustic tour that renewed interest in both Dando and his former band. Ben kweller 2006 rare. Then following the band's tour in 1997, something happened (drug use perhaps?), and the band and Dando seemed to disappear from the public eye for several years.
ABCDDelhiIndia1100019876543210
and the mapping is:
Name 4City 5Country 5PinCode 6PhnNo 10
The mapping is same for all records in a file. Its just gives a way how the records can be read from a file.
Any Ideas on how can I do this in simplest way?
2 Answers
You can have some delimiter between Name, City and Country for example : ABCD:New Delhi:India.Having delimiter can allow you to separate name, city and country. Now you can easily determine the length of each strings. Now based on your requirements you can easily edit the data and write to the file. You can refer the below code for reference : Text File : exceltestabcd:new delhi:indiatest:NY:USA
Macro Code :
Now based on your requirements you can search for the name and edit the data and write to a file. You can then delete the original file and save the temp file as the original file.
PS : Tried on Excel 2007. You need to add reference 'Microsoft Scripting Runtime'.
Como Criar Um Arquivo Txt
What you have described is fixed width data. This is straight forward to import the data as a text file in excel using the 'fixed width' option in the import dialog box. You can then select the start and end for each field. The trickier part is to export it to the same format. The only way I know of to achieve this is via VBA. It seems like it would be straight forward routine to write though, it has probably been written many, many times.