Our website has pricing tables per product that appear like this:





In order to update pricing on our website, we have to use an import/export text file that is in this format with a lot of extra characters that make it tedious to edit; this data is all in one cell when opened in Excel:



(Hopefully the image above can be clicked on to enlarge.)

We use this simple pricing spreadsheet to calculate our prices:



For instance, we want the '15.51' in the long string to be replaced with '16.95', etc.

Any suggestions on a way to update the import/export text file with the new values from our pricing spreadsheet?

Thanks for giving it any thought,

John

Exporting the Excel worksheet back out to the RTF format should be easy enough, using a formula that reads all the values in columns A through E and generates a character string in a separate worksheet which you then save. Or for that matter you can just cut-and-paste that export column of text to Notepad and save it with an RTF extension.

Importing the RTF to Excel would be a little more complicated, using formulae. Personally I use VBA at the drop of a hat, and you might find it easier too. But it can be done with worksheet functions, if you don't mind a lot of helping columns at the right. At least, I'm pretty sure it can. Do you have a preference as to which, formulae or VBA?

The formula method would probably involve you opening the RTF document, then cutting and pasting the text into an Excel column. (It might not, though; I'd want to start by asking you how you got that text into Excel.) The VBA method would be more complex to write, unless of course you like that sort of thing—but it would work faster, and would probably be able to go get the data from the source document itself.

Bob, thanks for wanting to help!

I'm up for trying the VBA approach if that's what you prefer; I should be able to do a little research and figure out how to make that function work.

Though I need to show you some other products' pricing table variations & differences in text strings before you get into it; I'd hate for you to not have a complete picture of the entire process. I'll get some screenshots up later today, hopefully in the next couple hours.

As for the text file, we export it from our website program as a .csv file and open it in Excel. For the pricing table, we enter base pricing at the first of the year and then derive later-in-year price increases via simple multiplication formulas. We can convert these cells to values if that is necessary.

Thanks again, and I'll post back shortly!

John

Daymaker, you're making me just a little bit nervous. I am indeed willing to help, but how? I'm firmly against the notion of my simply writing a VBA subroutine for you. Oh, I do that sort of thing professionally, and I've no objection to doing it on that basis; but this forum isn't for that, here we tell each other how for the love of the game.

I can do that—I will—but it would be by helping you understand how to write the VBA module yourself. I don't think it would be very complicated, but you must be the one who writes it because you're the one who has to live with it afterward—explain it if necessary, fix it if the input format changes, enhance it if requested and so forth.

If that's what you meant, great! But if not, reconsider how you want to approach this, knowing that I'll explain forever but you'll have to be the one who actually puts the pieces together into a working whole.

I understand Bob. We're on the same page.

Ok, just making sure. So when you're ready, tell me: How much do you already know about VBA? Have you ever written one, even a very simple one? If so, we can just talk about the logic (and it really won't be complicated). But if not, we should start by introducing you to the, um, the "infrastructure", I suppose it could be called: how to get to the VBA editor, creating a program module and so forth.

Here's what the table/text file/spreadsheet look like for a different product:

Pricing table from website:


Text file that I took into Excel; this time I used the '^' as a delimiter so the prices would be at end of each cell (rather than one long string all in one cell); I don't know if that helps or not:


(And you mentioned RTF; the import/export file is actually a .txt file if that matters.)

Spreadsheet of values to merge into the text file:

Oh, and I mentioned RTF only because that's what all those "\r\n" codes remind me of. RTF is another markup language, like HMTL. But I don't really care what the format is, much less the file extension; I was just showing off. The real point is that we can use the codes (no matter what they are) to identify the pieces of data.

That's a good start, though it may not sound like much to you. Now, one of my failings is also a strength: I like to explain things, perhaps too much. But in this venue, long explanations will just bore you, especially when I don't know how much you know and may try to explain something at length that's old had to you. So I'll try going fast—but you really must ask about everything you're not sure about, because otherwise I'll end up skipping over something you need to know.

Your import macro, as I see it, will have to 1) find the text file, either with or without the user's help—we'll decide that later—2) open and read it, and 3) parse out the text for each line and place the values into a worksheet. Maybe it'll have to clean out the old worksheet, too, or maybe create a new one; that's a detail that I'll leave up to you. Exporting the data to a text file can also be done in VBA, but maybe it won't be necessary.

I gotta run out this evening, and I'm late now, so let me just assign a trivial exercise as homework: Write a VBA program, and run it, that generates some simply message, such as "Hi, there!" (or whatever else you like). If you already know how, great. If you don't, here's how it would look if I did it:Try that (or something more scurrilous, if that'll help challenge your creative juices) and let me know whether you run into trouble. We'll add parts that do what you actually want to do, one at a time, starting when I get back.

Homework assignment done, I think: I just opened up a blank new spreadsheet, went into VBA, clicked on 'view code' icon in the Project - VBAProject window and pasted your code. I tried to run the macro but it didn't appear in the list until I took out the '0' between parentheses. Once it was removed, I was able to run the macro and have the message box appear.

I don't remember putting the '0' in; maybe my brain was fried that late at night, or maybe my fingers did it all by themselves without my having to think about it all. At any rate, it shouldn't have been in there, so you did right.

It probably doesn't matter, but I didn't hear you say anything about what module you pasted the code into. Did you create a new one, or use one that was already listed among the "Microsoft Excel Objects" in the Project window? The program worked, and that's probably all that counts, but I usually prefer to create ("insert") a module for each new main program I'm creating. Perhaps someone else here can give good reasons for that, but all I have to defend it is a sense of neatness or organization. Of course, when you're writing big project it matters, but maybe not for this.

Anyway, let's proceed. I said that your program will eventually read the data directly from the text file, but let's start by writing part of the code that'll turn a text string into the six display columns. I've looked at the two sample strings you supplied, and there are some differences that I need to ask about, but the general pattern seems to be this:

1. The item name
2. Delimiter '#'
3. A series of quantities and their corresponding prices, each delimited (or perhaps terminated) by "\r\n"
4. Delimiter '#'
5. Text that can be discarded

Each item #3 breaks down like this:

1. Lower quantity
2. Delimiter '|'
3. Higher quantity (ignored during import, calculated during export)
4. Delimiter '|'
5. Price

Is all that correct? And what's that bit with NO AD and WITH AD? Your program will have to know what to do with that, too.

Once we can define exactly how that string is defined, we can explain it to your program. There are always lots of ways to do any particular task in a programming language, but I would probably go about it like this:

1. Use the Split function to cut the main string up into parts delimited by '#'. Let's assume we call this "Array1"; that puts the item name in Array1(0), all the quantities and princes in Array1(1) and the rest (which we're ignoring) into 2 and 3 and beyond.
2. Next cut the quantities/price series (currently in Array1(1)) into the various sub-pieces delimited by "\r\n". Let's put those in Array2; the lowest quantities and prices would be in Array2(0), the next in Array2(1) and so on.
3. Then cut each piece in Array2 by '|'; the low quantity would be in Array3(0), the high quantity in Array3(1) and the price in Array3(2).
4. Put each of the pieces into the worksheet.

The first part of this code might look like this:Now, this isn't ready to run; it cuts up the pieces correctly enough (as far as I know), but it doesn't put anything into spreadsheet where you can see it. I'm stopping here partly so you can look it over and see how much of it you understand (this is where you should ask questions), and partly so you'll have a chance to tell me—it's it's true—"Oh, I get that part, I just don't know how to [something else]".

Thanks Bob; sorry to take so long to get back on this. Other business has kept me away from it.

The 'NO AD' and 'WITH AD' strings we want to incorporate; it communicates to the buyer that lower qties do not have their ad imprinted on the calendars and higher qties do include their ad. Evidently when we import the string with 'NO AD' & 'WITH AD' portions (without any price), the system will default with price of $0.00 (see first image in my first post).

And it may make no difference at all, but here's a screenshot of the field within Miva Merchant that we input pricing for the TY16 (first item at top of thread that I intro'd with):



Everything else seems pretty straightforward although I don't know anything about arrays...I guess it's just ordering/sorting information.

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: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: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:Now let's let VBA break that up into its component pieces: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.

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:
—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?

Hi Bob. The main function we're after is to take pricing that we have in a spreadsheet (third image of orig. post) and turn it into the character strings (one per product) that we will then upload into the website. My apologies if I was unclear on this point....