View Single Post
  #1   Report Post  
Dennis
 
Posts: n/a
Default VBA Import of text file & Array parsing of that data

Using XL 2993 & 97

The array formula which parses the information below (from a text file) by
"trimming" elements of the "Directory" line of information is:


Workbooks.OpenText Filename:="C:\Test\Listing.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:= Array(Array(0, 1), Array(10, 1), Array(17,1), _ Array(21, 1),
Array(39, 1))



Specifically:

"Directory of C:\BOB AMY Estates\Bank Documents"
is being parsed: (because of data columns following it)

11/21/2004|of C:\|BOB|AMY Estates\Bank|Documents
0 | 10 |17 | 21 | 39

******** Portion of the Actual Text Document ****^**********

Directory of C:\BOB AMY Estates\Bank Documents

11/21/2004 03:02 PM 120,787 NCB Ck #101.jpg
11/21/2004 03:01 PM 149,374 NCB Ck #93.jpg
11/21/2004 02:45 PM 136,291 NCB Ck Template.jpg
........... ..... .. ....... ...................
........... ..... .. ....... ...................
************************************************** ********

If I concatenate the columns created by the XL import array formula, I end
up with:

C:\BOBAMY Estates\BankDocuments

not the actual path of

C:\BOB AMY Estates\Bank Documents

Is there a way to force the array formula above to capture the space after
"BOB" to get "BOB " and also after
"AMY Estates\Bank" to get "AMY Estates\Bank "?

Yes, I can on-the-fly fix the data in the opeing XL file.
But I may have to manually change each similar data line.

I do not understand Array formula VBA parameter setup and coding. But
saying that, it seems to me that the array formula above is trimming spaces
before and after the data in the array elements above.

Any help is welcomed!

TIA Dennis