Jun 26 2009

Creating a Wordpress Import File with Excel

Wordpress Import Files Using Excel

Disclaimer: I absolutely do not advocate splogging of any kind. This is simply a way to get your existing data into a Wordpress blog. This same method could be used for chapters in book, recipes, or whatever other kind of data you may have. Rather than taking the monumental effort of manually creating a Wordpress site based on your work, you can use a little creativity to come up with a fast, easy solution that is surprisingly robust (this article creates a Wordpress blog website with over 31,000 posts).

I’m a big fan of native file formats – as they say, “when in rome”…. Yeah. Anyway, this is a quick article to illustrate how to set up Wordpress Import using an Excel spreadsheet. I’ll try to cover this from point A to point Z and even provide the download files and set up a sample URL so you can see the results.

The Wordpress 2.8 Import File screen lists fifteen different file formats including: Blogger, Blogroll, Blogware, Bunny’s Technorati Tags, Categories and Tags Converter, DotClear, GreyMatter, Jerome’s Keywords, LiveJournal, Movable Type and TypePad, RSS, Simple Tagging, Textpattern, Ultimate, and WordPress. I would be lying if I claimed to know what any of these formats actually are, so I’m going to go the easy route and export a file and use it as a template.

The Wordpress export file saves out as a .wxr file. This format is called the WordPress eXtended RSS file format and contains posts, pages, comments, custom fields, categories, and tags. After performing a Wordpress Blog Export from another blog, I can see that what I’m primarily concerned with is the “item” section which corresponds to a post. I’ll chop out the “item” section and save it as its own file. This will be a template that I’ll use later on from VBA. (I cut a couple elements from the bottom as well which I doubted were required by the schema – flying fast and loose on this one. You’d want to make sure your file conforms with any applicable XSD or DTD, but I’m fairly confident this will work.)

I’m inserting some placeholders in the template sample Wordpress Import file. These placeholders will be search and replaced as we create the actual import file(s). Here is the XML snippet of the template and here is the file available to download (coming soon).

<item>
    <title>[propertitle]</title>
    <link>[link]</link>
    <pubDate>Fri, 12 Jun 2009</pubDate>
    <dc:creator><![CDATA[admin]]></dc:creator>
    <category><![CDATA[[category]]]></category>
    <category domain=”category” nicename=”[category]“><![CDATA[[category]]]></category>
    <guid isPermaLink=”false”>http://www.biblewack.com/[testament]/[category]/[hyphen]/</guid>
    <description></description>
    <content:encoded><![CDATA[[content]
<p align=”center”><span style=”font-size: x-small;”>
<a title=”[backtitle]” href=”[backlink]“>&lt;&lt; [backtitle]</a> || <a title=”[nexttitle]” href=”[nextlink]“>[nexttitle] &gt;&gt;</a></span>
]]>
    </content:encoded>
    <excerpt:encoded><![CDATA[[content]]]></excerpt:encoded>
    <wp:post_id>[postid]</wp:post_id>
    <wp:post_date>2009-06-12</wp:post_date>
    <wp:post_date_gmt>2009-06-12</wp:post_date_gmt>
    <wp:comment_status>open</wp:comment_status>
    <wp:ping_status>open</wp:ping_status>
    <wp:post_name>[hyphen]</wp:post_name>
    <wp:status>publish</wp:status>
    <wp:post_parent>0</wp:post_parent>
    <wp:menu_order>0</wp:menu_order>
    <wp:post_type>post</wp:post_type>
    <wp:post_password></wp:post_password>
</item>

Programming the Wordpress Upload

So, I wanted to create a big Wordpress site for this example – really big. I struggled to think of what kind of freely diseminable data I could come up with to create a massive Wordpress Import sample. I found a good source of data with the Bible. Free to distribute and in nice text file format. Each line numbered (think “unique identifier”) and each line with a hard return after it. A perfect data structure for import into Excel (and eventual import into Wordpress). I’ll digress from the Wordpress Import briefly to illustrate how I sliced and diced the data to get it ready.

1. Import into Excel without delimiters. 1 column
2. Chop out Line Numbers with For-Next Loop and instr(function). 2 columns
3. Derive Chapter Name with Lookup function on alpha characters in previously created column. 3 columns
4. Assign Old Testament or New Testament with manual drag in Excel. 4 columns
5. Figure out what the URLs will be and make URL column. 5 columns

The reason I wanted to figure out the absolute URL for each post is I wanted to include “back” and “next” links in the body of each post so one could traverse throughout the entire book from post to post. This is accomplished simply by looking at the previous row (x-1) and the next row (x+1) while executing the loop.

Here is the entirety of the routine to create the .wrx Import file. You may need to split your file up differently depending on your import limits. You can easily do this by saving out a file every XXXX-th time or whatever you need. The code is very simple to understand. I loop through every data row in the spreadsheet (31102 rows and a header). Variables are assigned to the cell string values. A string variable “S” is assigned the result of our Text2String function. As I’m thinking about it now in retrospect, rather than call that code every time – you could just call it initially and create S once. Then you could assign another variable to the value of S every time rather than going through the readfile process, much more efficient…..Oh well… Lastly, I append the contents of S into a file using Print.

Sub makeWordpressImportFile()

For x = 2 To 31103

sPostid = x
sCategory = Trim(LCase(Sheet1.Cells(x, 2)))
sCategory = Replace(sCategory, ” “, “-”)
sTestament = Trim(LCase(Sheet1.Cells(x, 1)))
sTestament = Replace(sTestament, ” “, “-”)
sBackTitle = Trim(Sheet1.Cells(x – 1, 4))
sNextTitle = Trim(Sheet1.Cells(x + 1, 4))
sBackLink = Trim(Sheet1.Cells(x – 1, 7))
sNextLink = Trim(Sheet1.Cells(x + 1, 7))
sContent = Trim(Sheet1.Cells(x, 8))
sLink = Trim(Sheet1.Cells(x, 7))
sProperTitle = Trim(Sheet1.Cells(x, 4))
sHyphen = Trim(Sheet1.Cells(x, 5))

s = Text2String.Text2String(”c:\scott\item.txt”)
s = Replace(s, “[postid]“, sPostid)
s = Replace(s, “[category]“, sCategory)
s = Replace(s, “[testament]“, sTestament)
s = Replace(s, “[backtitle]“, sBackTitle)
s = Replace(s, “[backlink]“, sBackLink)
s = Replace(s, “[nexttitle]“, sNextTitle)
s = Replace(s, “[nextlink]“, sNextLink)
s = Replace(s, “[content]“, sContent)
s = Replace(s, “[link]“, sLink)
s = Replace(s, “[propertitle]“, sProperTitle)
s = Replace(s, “[hyphen]“, sHyphen)

MySitemap = “c:\scott\bible.txt”
’set and open file for output
fnum = FreeFile()
Open MySitemap For Append As fnum
Print #fnum, s
Close #fnum

Next x
End Sub

Finally, if you’ve been paying attention, you’ll know that we haven’t yet created a well formed XML file – there is no root node. We’ve essentially concatenated together a bunch of nodes, but there is no root. I doubt very seriously this would import into anything. You could write something to open each file and paste in the root node and matching end node – or you can manually open and save your files out. I chose the latter as I didn’t have many files to work with. I used the following as my root node:

<rss version=”2.0″
    xmlns:excerpt=”http://wordpress.org/export/1.0/excerpt/”
    xmlns:content=”http://purl.org/rss/1.0/modules/content/”
    xmlns:wfw=”http://wellformedweb.org/CommentAPI/”
    xmlns:dc=”http://purl.org/dc/elements/1.1/”
    xmlns:wp=”http://wordpress.org/export/1.0/”
>

…and obviously my closing node was simply “</rss>” which indicated the end of file. Once you have your file(s), all you need to do is import it and Wordpress will handle the rest. The categories will be created and the posts will be created. You could obviously fine tune this by working with the dates – future dating posts does work (you can make it look like you’re working when you’re really not!). The sample site that I created is at www.biblewack.com (very hard finding an available URL with “bible” in the title). Good luck.

Popularity: 28% [?]

Jun 25 2009

LRP 21.5 Brushless Crawler Motor and ESC Review

So, after going through an enormous amount of pain and suffering trying to find the right motor combination for the Losi Comp Crawler, I stumbled onto a winning combination. I didn’t know when purchasing this set that it was affiliated with Team Associated or it would’ve made the purchase less painful at a total of $220. It turns out that the setup is top notch – as one would expect from Team Associated.

Anyway, the hand wound Crawler 21.5 Brushless motor features sensor/sensorless operation and neodymium magnets. It has a built-in Hill brake, but that isn’t needed with the worm gears in the Losi Comp Crawler, but I’d like it in my Axial AX10. The ESC also has no reverse delay and 4 unique crawler throttle profiles.

Driving the LRP Brushless Motor

Wow. I have not run brushless in a crawler before. I had that same ’special moment’ when I powered it up as when I threw the Monster Max in my eRevo. I don’t know if it has more to do with the out-of-the-box quality of the the Losi or the LRP combination, but I REALLY like this setup.

I’m reading a lot of people in the crawler community mentioning “wheel speed” lately. It is nice to have a little bit of “blast” on reserve to power up tough sections when you need it, but I also like driving super slow. The LRP setup is great for both. It easily packs more punch with a 6 cell NimH battery than a comparable Novak 55 turn brushed setup on Lipo.

LRP Slow Speed Crawling

Where things really get interesting is at the super slow end. This thing is buttery smooth…unbelievably silky smooth…..super slow….(words can’t describe) My Axial has a Mamba Max with the Novak 55t and I can almost feel the revolutions of the motor if you know what I mean (not literally, but that “chug chug chug” feeling when going slow). The LRP setup is just totally smooth – unbelievable low end. You become intimately intertwined with the Crawler as it responds to the trigger. Inching up something is fun. It’s hard to describe until you do it.

Four thumbs up for the LRP setup – well worth the money!

Popularity: 20% [?]

TAGS: ,
Jun 23 2009

Losi 1/10 Crawler Review

I’ll be writing a complete review with many more actual pictures soon. This is the beginning, first thoughts Losi review – to be followed up by a comprehensive lowdown after I’ve really put it through the paces. There is a well known problem with the front end cv’s busting – the result of a manufacturing flaw, but it only affected early versions of the crawler and Losi is apparently overnighting replacements to affected customers.

First Thoughts on the Losi Crawler

As soon as you take the 1/10 Losi Comp Crawler out of the box, you feel like it is very tight and well designed. Everything just feels right. If you’re familiar with an out of the box Axial AX10, you’ll immediately think, “Man, why didn’t Axial do this?” You’ll be enamored, but then again – you probably are every time you pull a new toy out of a box.

I’m not dogging the Scorpion – quite the opposite. At this point, my stock Losi isn’t able to keep up with my AX10. However, there are several immediate upgrades you need to perform on an AX10 – even the ARTR version. A stock Losi Crawler will MURDER an AX10 out of the box based purely on the battery location alone. In practice, NO ONE puts their battery on the battery plate at the top of an AX10, so that isn’t a legitimate comparison. Indeed, half the fun of crawling is personalizing your crawler and locating your ESC, Batteries, Servos etc.
Losi versus Axial?

Before I get into a discussion piece by piece of the Losi, I’d like to expand a bit on the comparison. The Losi is tight enough out of the box that I felt confident crawling with it without adjustment. That is not to say that I didn’t end up fine tuning it and taking it entirely apart, but I didn’t attack it before driving it. Now, to contrast that with the Axial: almost everyone I know purchases at least the front axle battery tray/ 4 link suspension setup simultaneous with the car and winds up installing that before even attempting to drive it. It’s just a fact. There are a lot of mods you want to do as soon as you get your hands on a Scorpion. You won’t feel like that with the Losi – which is both a good and BAD thing.

You’re probably asking yourself, “why is that a bad thing?”

Well, depending on which upgrades you buy, within about 2 weeks of owning the Axial . . . you will have a very different truck than your buddy. And his will be different from the next guy. And so on. That may not be the case with the Losi. See where I’m going? Do you really want to show up a competition and be driving the EXACT same truck as the next guy? Or even down at the park with your kids?

“Hey, that is my crawler!”

“No, that is my crawler!”

“Wait, they are exactly the same?”

“Oh, this one has my speed control on it….”

Some Positive Notes on the 2.2 Losi Crawler

  • The DIG is great – more on this as I continue to write the review.
  • Out of the box solid rear axle with worm gear.
  • 4-link suspension out of the box – this is a necessary upgrade on the Axial.
  • Comes with the 2.2 Losi Rock Claws which most people end up buying anyway.
  • More shock mount possibilities than the Axial with cooler, aluminum threaded shocks out of the box.

Finding the Right Motor/ESC Combination

This was trickier than expected. The Axial was a no brainer: just bolt on the Novak 55 turn brushed motor with a Mamba Max and start crawling. The Novak barely ever breaks a sweat – doesn’t get past warm even on hot days.

Not so with the Losi – probably because of the worm gears. First, I tried the Mamba Max with a Traxxas Velineon motor (unsensored setup) and it was terrible. I had read that the Losi needed a faster turning motor because of the gear reduction via the worm gears. Not true. The 55 turn was fine, but I didn’t want two crawlers running the same brushed motor. Unsensored brushless motors and crawling do not mix – they cog terribly at slow speeds. I wound up burning the $80 Velineon motor up within a couple hours of operation.

Okay, so I was still on the quest for a faster turning motor and jumped up the a 35 Turn Integy motor. I anxiously put it in and started crawling. It drove nicely, but seemed to bind up a bit (which I think was actually the Dig misengaging at times) but after a short time I noticed the motor was nuclear hot. I let it marginally cool down and drove more – it burned up too.

Choice #3 was a pulled 21 turn Titan out of an eRevo. This started out great – lots of power and unbelievable low end as well. I was very optimistic about the Titan……..until it to burned up.

3 Strikes and You’re Out

I didn’t want to spend another $220 on a brushless setup, but I did and I can only saw Awesome. I bought the LRP setup and did a review of it on it’s own. Perfect.

Popularity: 10% [?]

TAGS:
Jun 20 2009

The Changing Face of Search (Part 1)

Recently, I wrote a post reviewing Newton running shoes. It wasn’t Pulitzer worthy and was simply to share what I thought might be beneficial information with other runners. I’ve largely ignored this blog for awhile as I’ve worked on a bevy of other internet projects, but I’ve been thinking about writing for the internet a lot recently – and I’ve decided to use this blog as the medium.

….Anyway.

So, I published the post and that was it. Boom! A couple days later, I checked my logs and noticed the traffic was through the roof? Wow! …Strange, I thought. I Googled it and found I was about midway through page 3……no man’s land. (the plot thickens….)

I checked a little deeper through my hosting company for the referrers and found that it was, in fact, Google. What’s going on here, I thought?

Traditionally, page 3 was not much better than page 500 in Google. In other words, …search oblivion…

Then, as I was pushing my son on a swing today, I started thinking about my own search patterns. If I search for a “review” on something – say a running shoe, I don’t want to read Runner’s World or Runner or Running Times or anything – I want to read Jane Pocahontas Smith from Poughkipsee, Arkansas and what her experience was… I want absolute honesty. I want to know if they hurt her feet. I want her to mispell basic werds. I want to see her fat, disgusting callouses. I want to see her sweaty bib numbers. I don’t want ads. I don’t want someone paid to review it. I don’t want glossy pictures. In fact, I’ll now routinely skip over everything on the first page looking carefully at the URLs to discern whether they are big name companies – or little name, terd blogs – like “hobbub” and I’ll read accordingly.

Apparently, I’m not the only one.

In any event, this has a couple important implications: (1) people are changing the way they search for products, and (2) Google isn’t returning the correct results…..for “reviews” at least.

 Wise Man Say, “People Smarter Than You Think!”

Popularity: 5% [?]

TAGS:
Jun 19 2009

Asics Gel Hyper Speed 3 Review

A couple days ago, I wrote a favorable review of the Newton Stability Racers. About the same time as I bought the Newtons, I also bought a pair of Asics Gel Hyper 3’s. I haven’t logged near as many miles on the Asics, but I like them almost as much as I like the Newtons (and they were half the price).

The Asics Gel Hyper’s have a very small patch of Gel right under the ball of your foot. Other than that, they are very basic racing flats consisting of a mesh upper, a long lacing area and tongue and a very thin, soft but suprisingly strong sole.

When I first put them on it felt like my foot was too wide for the sole. Like my foot was big and fat and oozing over the sides. Of course, it wasn’t but the shoes gave me that sensation. I could also “feel the ground” through the soles of the shoes. I wouldn’t recommend running on trails or rough surfaces with these shoes, but they are just fine on pavement and even better on a track.

Asics Gel Hyper Review

So, there are really two main things to note with these shoes: (1) how they perform training, and (2) how they perform racing.

Asics Gel Hyper Training: Good, but that isn’t what these shoes are about. They are simply too light and too “unstructured” for daily running unless you are moving toward running minimalism (barefooting). I think they would be a good way to build strength in your foot and probably are about the right precursor to the Vibram five fingers. If I were going the barefoot running route, I’d wear these for about 3-4 weeks before transitioning to the Vibram five fingers. If you’re a traditional runner, you are going to injure yourself trying to train significantly in these shoes.

Asics Gel Hyper Racing:
You’re On! That is what these shoes are all about. I wouldn’t wear these for a marathon, but for 5 and 10K’s, I’m willing to bet you could shave some serious time off – if you’re already a good runner. If you’re a big time heel striker, these shoes will kill you. If you’re already running softly upon your feet and have a high turnover ratio (high “cadence” in Newton terminology), then I’d bet these shoes are better than your current racers. In fact, I can’t think of a shoe I prefer over these. I once had some nice Nike flats (mental note- need review!) and I thought I’d never beat them, but these do. I won’t say these crush them, but these are some great, ultra lightweight running shoes.

So that is it for Fitness Friday!

Wise Man Say, “Asics good running shoes!

Popularity: 67% [?]

TAGS:
Jun 18 2009

Snollygoster

According to the Oxford English Dictionary, Snollygoster originated as a combination of two German words: schnelle geister, meaning “quick spirits”.

The schnelle geister was meant to be a ghastly, monstrous ghost of sorts who scared children. It gradually evolved in early US history to mean a giant bird that may grab children.

Somehow it morphed over the years and began being used as to scare slaves away from voting once they had rights. The slaves were threatened with a giant reptile like bird would cruise in and steal their children if they voted.

By the 19th Century, snollygoster was being used to define “calculatingly dubious people.” Today, the Oxford English Dictionary defines snollygoster as “a shrewd, unprincipled person, especially a politician”.

Snollygoster is thrown around in political circles referring to politicians who are not interested in helping their constiuency but rather are driven by personal gain of wealth (Think Rod Blagojevich).

Popularity: 6% [?]

TAGS:
Jun 18 2009

Text Speed App or Text Speed Tester

So, we were contemplating creating/developing a text speed testing application. LG has been sponsoring a “Fastest Texter” contest for a couple years now. The LG contest appears to be gaining traction and every recent study concludes that text messaging is becoming one of the preferred means of communicating.

Initially, I thought such an application surely already exists – and, it *must* for LG to build a contest around it, right? But, as of today, there is no widely recognized de facto standard for measuring how fast you are texting. You can find a bevy of typing speed applications on the web, but texting speed calculators or text speed tests are conspicuously absent? (As a friend said, “someday people will be putting how fast they text on their resumes.”)

Developing the Text Speed Measuring Application?

One of the hardest business projects around is mobile phone development. The .mobi domain has not yet taken off (will it ever?) and there are a couple firmly entrenched provider specific platforms (think iPhone, Windows Mobile). While these markets are both considerable, unless you pursue parallel development paths, you’re starting off with a fractional overall market at best. Consider the fact that several cell phone companies include their own platforms (think of Palm and the Pre) and you’re problems expand further.

When you start looking at the whole picture, a distinct solution starts to emerge: the iPhone. Every iPhone is essentially the same (+/- 3g, gps) so the portability of an application is virtually assured. When developing for Windows Mobile, the target phones could be vastly different – you also need to consider things such as screen size and orientation. This could dramatically increase the difficulty of what should be a seemingly “easy” application to develop.

The biggest attraction for iPhone development, however, is the Appstore. The Appstore makes purchasing applications unbelievably easy. Our Texting Speed Calculator could be download in seconds! Compare your average iPhone user who is probably intimately familiar with the Appstore to your average Windows Mobile user – who most likely doesn’t even have a third party app yet.

Developing iPhone Applications
Here are some other good points about iPhone application development:
•    The development tools are near professional grade, and are cheap to come by if you’re a Mac guy/girl already.
•    Although it wouldn’t help the hypothetical Speed Texting Tester, the iPhone gives you access to a wide array of cool functionality: GPS, accelerometer, wi-fi, camera, etc.
•    The xcode developer tools also work with C++ and C code (the sdk is in Objective C).

In the end, I chose not to develop the ultimate Text Speed Tester Application. I think it is a great idea and one that will ultimately make someone $$$ money, but I just can’t make room for it in my currently overloaded life plan…

Oh – apparently, I’m not the only one who feels this way about mobile application development.

Wise Man Say, “Saying Is One Thing, Doing is Another.”

Popularity: 14% [?]

TAGS:
Jun 17 2009

Newton Running Shoe Review

Everyone loves new gadgets. I’ve read several times about how the yellow Spira shoes were banned from the Boston Marathon (internal springs), but it was another yellow shoe that caught my eye (and my wallet). After looking carefully at the shiny yellow Newton Men’s Stability Racer, I decided to purchase a pair ($150) and try them out. I’m a mid level runner and I’ve owned them for about 6 weeks now.

First, I visited the Newton website. The website is actually pretty cool and contains a lot of running information, some informative videos and a slew of information on the Newton Running shoes. The one awkward thing was that they have a “Patents” page that doesn’t reference a single patent? (although they claim to have been granted 9…) I found the relevant Newton patent and read the abstract – pretty cool technology they employed with the actuators.

Anyway, I immediately let a good triathlete friend of mine try them out for a mile. (”Yes,” we wear the same size shoe.) He came back and said they were “very hard” on his calves, but liked the weight and the “fast feel” of them. The comment about his calves intrigued me so I went back and Googled more information and found the calf strain comments to be a recurring theme. Newton recommends you slowly become accustomed to their shoes.

Although the calf comments concerned me, I also found plenty of people who weren’t bothered by them because they were already running correctly.

Newton Running Shoes First Run Review

Luckily, I fell into the no-calf-pain camp. I wore the shoes for about a mile the first time out. They felt extremely light, stable and helped my overall body position throughout the run. I felt virtually no residual calf soreness. They make you “feel fast” similar to the way young kids think they can run faster with a new pair of shoes.

Newton Running Shoe

Running Longer Distances with the Newton Racers

I started wearing them exclusively and for longer and longer runs. In my case, my body became accustomed to them almost immediately. After a couple runs, I could no longer even tell that I was wearing them. I literally couldn’t feel the lugs in the bottom (strange?). I did consciously try to shorten my gait on a couple occasions. I also stepped it up a bit by speeding up my running cadence which also perhaps forced me a bit more onto my forefoot. I ran as far as 10 miles with them and they were comfortable and supportive throughout.

Running a 5K Race in the Newton Stability Racers

I wore them for a personal best 5k. I don’t know that I can say much in this regard as I was admittedly in much better shape than at previous 5ks. They are very stable and protective for such a light shoe. I also have a pair of Asic Gel Hyper 3’s that I’ll be reviewing. The Asics may be a tad lighter than the Newtons, but they are no where near as protective. I don’t feel comfortable venturing off pavement in the Asics and they are probably best on a track surface.

There is one significant drawback to the Newton racers: they look FAST! In other words, unless you are perfectly comfortable with your racing – these are intimidating shoes to walk to the start line in….especially if you typically start toward the back. Also, know that people will take great satisfaction in beating you just so that they can say, “well, I was faster than that guy wearing those bright yellow $200 running shoes.”

You can always get them next year. Life is too short. Buy the shoes.

Also, one of the best things about a fancy new pair of shoes is that they increase your enthusiasm for running. I couldn’t wait to put them on and go for a run. I started thinking about running all the time. It was as if I had just running!

Wise Man Say, “A Change Is As Good As A Rest

Popularity: 66% [?]

Jun 17 2009

Text File to String VB Function

Here is a quick little VB/VBA function to turn text files (or html files or xml files) into string variables.

Text File to String Variable Function (xml file to string variable, html file to string variable)

There are numerous Internet Programming situations where large pieces of text need to be populated with variable values. On other occasions the task could be parsing html files to retrieve SEO information. Yet another common situation is working with XML as a string inside VB. The Microsoft XML objects consume a lot of memory. Sometimes it is more efficient to work with XML as a string instead of consuming the overhead necessary to create XMLHTTP or MSXML objects.

You can also use a xml file as a template for SOAP calls. Simply retrieve the xml as a string, populate some predefined placeholder text and you are ready to call inline webservices. As you’ll see, turning text into a string is easy – it doesn’t matter whether it is xml to string or html to string either.

The TextToString Function

The function below is short and relatively straightforward – feel free to customize it to suit your individual needs. It contains some rudimentary error trapping, but it should be enough to catch common errors and display enough information for easy troubleshooting.

The function contains two variables: the strPath variable is the argument parameter that will contain the absolute path to the file. You may want to implement file extension validation if your project requires a little more ‘tightness’ in the code. strBuff acts as a temporary buffer to hold the results until they can be assigned back to the function’s result.

also called:
Public Function XMLToString(strPath As String) As String
Public Function HTMLToString(strPath As String) As String

Public Function TextToString(strPath As String) As String
    Dim strBuff As String
    On Error GoTo ErrTrap
  
    Open strPath For Binary As #1
    strBuff = Space(LOF(1))
    Get #1, , strBuff
   
TextToString = strBuff
    Close #1
ErrTrap:
   If Err Then Err.Raise Err.Number, , “Error from Functions.TextToString ” & Err.Description
End Function

In a later project, we’ll be combining this Text to String Function with the Loop Through Directory routine in order to build content for a Wordpress blog. We’ll examine how to create upload files by manipulating data in Excel.

Wise Man Say, Don’t Run Your Head Against a Stone Wall

Popularity: 37% [?]

Jun 17 2009

Iterating over a Directory with a Dir Loop (Loop Through Directory) VB

VBA is potentially the greatest desktop ‘hammer’ you have at your disposal. You can pop open any Office application, hit ALT+F11 and be writing code before Visual Studio will even be open on most machines.

For doing ad hoc work around the office, VBA allows you to do things that applications alone simply aren’t powerful enough to do. It seems like I’m always running into a situation where I need to get information about a bunch of files in a directory. Let’s say that you just need to retrieve the names of all files in a certain directory and write them to a column in an Excel spreadsheet.

Unfortunately, Excel doesn’t provide a native way to accomplish such a task. However, the task is reduced to less than 15 lines of code using VBA. The VBA Dir Function is great for iterating over a directory.

Understanding the Dir Function

This function returns a String value containing the name of a file, directory, or folder that matches a specified pattern or file attribute. If you’re working at the root level of a file structure, the Dir function will also return the volume label of a drive.

Dir returns the first file name that matches pathname parameter. The trick to looping through a directory is that calling Dir with no arguments returns any additional file names that match pathname. You can see an example of this in the code snippet below: the line with the comment, “get next entry” is where the Dir function will retrieve the next matching filename.

When Dir can no longer return a match, it returns a zero-length string (”"). If a zero-length string is returned, either a new pathname must be passed or an error occurs. You can change to a new pathname without retrieving all of the file names that match the current pathname.

NOTE: Do not nest Dir loops and don’t attempt to call the Dir function recursively. Calling Dir with the vbDirectory attribute does not continually return subdirectories. If you think you need to recurse directories, first consider whether you can copy the results of a simple Windows Search to a new directory to achieve your desired results.

Looping Over Files with Dir, Looping Through Files, Looping Through Directories…

The following VB code snippet is pretty easy to understand. There are three variables: strPath, strFile and x. strPath is used to pass the initial parameter to the Dir function. This simply tells Dir which directory to traverse. strFile is used to hold the file name as the code executes and continues to retrieve file names. x is used simply as a counter so that the correct cell in the Excel spreadsheet is updated with the correct filename (Dir doesn’t return files in any specific order, so you may need to sort them afterward).

Sub LoopThruDirectory()
   
Dim strPath As String
Dim strFile As String
Dim x As Integer

    strPath = “C:\temp\datajunction\XMLOUT\”
    strFile = Dir(strPath)
       
    Do While strFile <> “”
        x = x + 1
        Sheet1.Cells(x, 1) = strFile
        strFile = Dir    ‘ Get next entry.
    Loop
   
End Sub

Wise Man Says, “A Bird in Hand is Worth Two in Bush”

Popularity: 100% [?]