Merging excel values to text file

BobBridges · #12 10-22-2013, 11:13 PM

Ah, ok; let's talk about arrays for a bit.

An array is a sort of multiple variable. Instead of having one variable named (say) Price, you could make Price an array of 10 variables, each numbered Price(1) through Price(10). You could do it in a program like this:

Code:

Dim Price(1 to 10)
For jp = 1 to 10
  Price(jp) = jp * 2
  Next jp

That very simple-minded example creates 10 Prices, where Price(1)=2, Price(2)=4, Price(3)=6 and so on until Price(10)=20. If you want do, you can later put those prices into a spreadsheet:

Code:

Cells(MyRow, 1).Value = "Prices:"
For jp = 1 to 10
  Cells(MyRow, jp + 1).Value = Price(jp)
  Next jp

That's is just an example of how arrays work, you understand. But I mentioned arrays last time because there's a Split function that will conveniently take a character string and cut it up into pieces, each one in one member of an array. Suppose you have in A1 the following string, which I copied by hand (so it may have some mistakes) from your first example:

Code:

TY16#....NO AD....^\r\n250|499|15.51^\r\n  |  ^\r\n....WITH AD....^\r\n500|999|13.22^\r\n1000|2499|9.94^\r\n2500|4999|7.13^\r\n#0##

Now let's let VBA break that up into its component pieces:

Code:

data = Range("A1").Value
arr1 = Split(data, "#")
  'arr1(0): "TY16"
  'arr1(1): "....NO AD....^\r\n250|499|15.51^\r\n  |  ^\r\n....WITH AD....^\r\n500|999|13.22^\r\n1000|2499|9.94^\r\n2500|4999|7.13^\r\n"
  'arr1(2): "0"
  'arr1(3): ""
  'arr1(4): ""
arr2 = Split(arr1(1),"^\r\n"
  'arr2(0): "....NO AD...."
  'arr2(1): "250|499|15.51"
  'arr2(2): "  |  "
  'arr2(3): "....WITH AD...."
  'arr2(4): "500|999|13.22"
  'arr2(5): "1000|2499|9.94"
  'arr2(6): "2500|4999|7.13"

See how Split works? Next we would run through each of the pieces of arr2 and figure out which of those to further split using "|".

Does that help?

Two remaining questions from my post below:
1) When you created that first program and ran it, you pasted the code somewhere. Where did you put it?
2) Is my description of the data correct as far as it went?

I was going to ask a new one, too, but I just reread your original post and I'm thinking of something new. New post coming up.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Loss of formulae values when mail merging excel to word	MellowMel	Mail Merge	6	05-09-2014 09:08 AM
How to convert a text file to an Excel file with the data format automatically?	mradmin	Excel	6	10-16-2013 10:34 AM
Handle Text / Numeric values in SSRS while Export To Excel	achuki	Excel	5	02-07-2012 02:14 PM
How Do I Put Text Values Into A CSV That Excel Will Leave Along?	eBob.com	Excel	2	05-04-2011 07:01 AM
Merging two Word files with one file having a text box	Timothy2001	Word	3	01-23-2011 07:01 PM