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

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

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

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