Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Is there any way to change the default delimiter of tab when opening a text file with the text inport wizard.
Thanks
Tom
0 
Taxi, I am unable to locate the 'Convert data'???
Tom,
If you use a Macro to open your text file then you can set it as Fixed Width.
Sub FixedWidth()
'
' FixedWidth Macro
' Macro recorded 06/09/2004 by Bryco
''
myFile = Application.GetOpenFilename("Text Files,*.txt")
Workbooks.OpenText FileName:=myFile, _
StartRow:=1, DataType:=xlFixedWidth
End SubPaste the above into the VBA Editor and save the New workbook as 'FixedWidth' or whatever you like. Assign a Shortcut to it for ease of use. In Sheet1 of the workbook, in any cell, note which keys activate the shortcut.
Save it again. If you save it in the location of your .txt files then it is faster.When you want to import a Text file just open this workbook and activate the macro to get your .txt file.
If your text files are always the same and you format them the same then use the VBA recorder to record the entire process and then just replace the first line up to and including StartRow:=1 with:
myFile = Application.GetOpenFilename("Text Files,*.txt")
Workbooks.OpenText FileName:=myFile, _
StartRow:=1Of course retain your actual Start row.
When you record the macro it will record the actual file name that you opened during the recording. The above line replaces that and will open the Open files dialog box where you select the text file to be opened. If that folder is the same folder that you stored the macro worksheet then it will open to that folder too.
Following the FixedWidth entry will be the FieldInfo: in Arrays.
After that you can add the line:
Sheets.Move Before:=Workbooks("xxxxxx.xls").Sheets(1)Replace the xxxxxx.xls with a name you want it to be.
and it will move the data to a new workbook instead of your workbook containing your macro.
The formatting on this forum does not look pretty but it should format correctly within the VBA editor.
The above seems like a lot but it is not. It just takes alot of verbiage to explain it.
HTH
Bryan
0
Taxi, I did not see the convert data either.
Bryan
Thank you very much. This solved by problem. I did not even think of a macro (I am not sure why). I just recored it and it worked fine.
Tom
0
it's in the tools menu . In french version it's call "convert" and located in the 7th position starting from top in this menu. Sorry, i'vnt got a US version...So guys, u'r talkin cross the oceans, don't forget it! see u next
0
Taxi, I certainly do not intend to take anything away from what you are saying but I still don't see it in my version of Excel 8 of Office 97.
As you can see at Tools Menu .swf
Best Regards,
Bryan
0
hoops, it's the "data" menu...Hips, i was a little bit "walking beside my shoes" as we said here...Sorry Brian.Ur animation is cool, what do u use to have it..?
0
Taxi, I don't find it under Data either. I had checked all the menus. Is it an addon? Perhaps I elected not to install it years ago when I installed Excel on this machine.
I used CamStudio V2.
The newer version that is still available does not have the capability to convert the movies to .swf files like the one I used. They are considerably smaller in size then the .avi files being the other option.I do have the installer package for it if interested. It is 1,334kb in size. The newer version (2.1) is over 9MB in size.
A while ago I went on a search for this version and found it on like page 7 of a Google search and I can not believe that I did not book mark it.
Let me know,
Bryan
0
yes, please send it. In response i'll give u a screenshot of my excel (i haven't got ur mail)
0
Right click and select "Save Target As" on the following link:
CamStudio V2. 1,334kbLet me know as soon as you have it so I can take it off of my webspace.
It is a very simple Screen capture program. I went into the preferences to set it to convert the movies into .swf format. I always use the Region caprure versus the full screen. Maybe a faster processor or video card could handle the full screen better.
This program has been bought out by Macromedia and they sell it now under another name.
Regards,
Bryan
0
I am using the following syntax to open a .txt file:
Workbooks.OpenText Filename:= _
"C:\fname01.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(0, 2), TrailingMinusNumbers:=TrueI want to be able to open several files one at a time with names from fname01.txt to fname50.txt
Can you help?
0
Sam, I tried my code but it will only allow the selection of a single .txt file at a time.
I suggest you post another thread asking the same question or post it on the OzGrid Excel/VBA forum board.
There are some folks there that have such knowledge that it makes me feel like I know nothing about Excel or VBA. They are good. If it can be done then they will know how to code it.
I have gotten a lot of help from them.I did search their boards but found nothing. Also, they reformatted their site and so many of the past posts are unable to display the code correctly.
Regards,
Bryan
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
