Merging excel values to text file

BobBridges · #1 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.

BobBridges · #2 10-22-2013, 11:18 PM

Daymaker, while I was rereading your post I suddenly got the sickening feeling that I've been doing all this based on exactly the wrong supposition. I've been thinking that you want to import those text strings into Excel and have them appear in a spreadsheet conveniently cut up into their proper pieces for display. But now I'm noting that you said you want to update the web page from the spreadsheet:

Quote:

In order to update pricing on our website....

—so now I'm thinking you keep your main data in Excel and want an easy way to export it to the text file that will be used in the web page. Is that right?

If so, I don't think you need to worry about VBA; as I said before, turning the spreadsheet values into the character string is easy, much easier than the other way around. Which is it you want? Or if you would like them both, which is the main function you're after? Which do you want to accomplish first?

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