Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import txt file with fixed length from VBA
Hi guys,
If anyone could give me some pointers on how to go about this I'd appreciate it very much. situation: I have to import various textfiles using VBA. I select the filename and path which comes from a listview and a textbox on a userform. So far so good. The textfile however is setup as follows: xxx xxxxx xxxx xxxxx xxxxx The data has fixed length for example the first field is 10chars, the second 20chars, the third 8 chars etc. (got about 150 rows of data) every row has the same fieldspecs. How would I go about it to import this data into a designated sheet from VBA? Using the wizard will open the file and place it on a new workbook and that's not what I want.. Thanks in advance for any pointers. -- Maurice |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import txt file with fixed length from VBA
You could use Data|Import External Data|Import Data (xl2003 menus).
And at the last (or close to the last) dialog of that wizard, you'll be asked where the data should go. Personally, I'd just use exactly what you used, but add a couple of more steps. One step to copy the data to the location where I wanted it and the second to close without saving the text file that I just opened. Option Explicit Sub Testme() Dim DestCell As Range Dim TxtWks As Worksheet 'assign the destination cell -- I have no idea what you want. Set DestCell = Worksheets("Sheet1").Range("x99") 'your code to open the file Workbooks.OpenText Filename:=.... Set TxtWks = ActiveSheet With TxtWks .UsedRange.Copy _ Destination:=DestCell .Parent.Close savechanges:=False End With End Sub Maurice wrote: Hi guys, If anyone could give me some pointers on how to go about this I'd appreciate it very much. situation: I have to import various textfiles using VBA. I select the filename and path which comes from a listview and a textbox on a userform. So far so good. The textfile however is setup as follows: xxx xxxxx xxxx xxxxx xxxxx The data has fixed length for example the first field is 10chars, the second 20chars, the third 8 chars etc. (got about 150 rows of data) every row has the same fieldspecs. How would I go about it to import this data into a designated sheet from VBA? Using the wizard will open the file and place it on a new workbook and that's not what I want.. Thanks in advance for any pointers. -- Maurice -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import txt file with fixed length from VBA
Here some sites to learn about this:
http://www.trixar.com/~makai/minidb1.htm http://msdn.microsoft.com/en-us/libr...71(VS.60).aspx http://babek.info/libertybasicfiles/.../nl120/raf.htm http://www.vb-helper.com/howto_random_access.html http://support.microsoft.com/kb/150700 http://www.profsr.com/vb/vbless08.htm RBS "Maurice" wrote in message ... Hi guys, If anyone could give me some pointers on how to go about this I'd appreciate it very much. situation: I have to import various textfiles using VBA. I select the filename and path which comes from a listview and a textbox on a userform. So far so good. The textfile however is setup as follows: xxx xxxxx xxxx xxxxx xxxxx The data has fixed length for example the first field is 10chars, the second 20chars, the third 8 chars etc. (got about 150 rows of data) every row has the same fieldspecs. How would I go about it to import this data into a designated sheet from VBA? Using the wizard will open the file and place it on a new workbook and that's not what I want.. Thanks in advance for any pointers. -- Maurice |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import txt file with fixed length from VBA
Dave,
It's those last steps i should take into consideration. Thanks for replying ;-) -- Maurice Ausum "Dave Peterson" wrote: You could use Data|Import External Data|Import Data (xl2003 menus). And at the last (or close to the last) dialog of that wizard, you'll be asked where the data should go. Personally, I'd just use exactly what you used, but add a couple of more steps. One step to copy the data to the location where I wanted it and the second to close without saving the text file that I just opened. Option Explicit Sub Testme() Dim DestCell As Range Dim TxtWks As Worksheet 'assign the destination cell -- I have no idea what you want. Set DestCell = Worksheets("Sheet1").Range("x99") 'your code to open the file Workbooks.OpenText Filename:=.... Set TxtWks = ActiveSheet With TxtWks .UsedRange.Copy _ Destination:=DestCell .Parent.Close savechanges:=False End With End Sub Maurice wrote: Hi guys, If anyone could give me some pointers on how to go about this I'd appreciate it very much. situation: I have to import various textfiles using VBA. I select the filename and path which comes from a listview and a textbox on a userform. So far so good. The textfile however is setup as follows: xxx xxxxx xxxx xxxxx xxxxx The data has fixed length for example the first field is 10chars, the second 20chars, the third 8 chars etc. (got about 150 rows of data) every row has the same fieldspecs. How would I go about it to import this data into a designated sheet from VBA? Using the wizard will open the file and place it on a new workbook and that's not what I want.. Thanks in advance for any pointers. -- Maurice -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fixed length file | Excel Discussion (Misc queries) | |||
fixed length file to excel? VBA macro possible? | Excel Programming | |||
Fixed length text file | Excel Discussion (Misc queries) | |||
Fixed length file import?? | Excel Programming | |||
2 Qs: grab net page, and import as fixed length format | Excel Programming |