Generating MadCap Flare snippet files using Excel (VBA)


If you have a list of phrases (e.g. a list of STE terms) you would like to quickly add to Flare as snippet files to make good use of Flare’s clever “auto suggest based on snippet” feature, keep reading.

The idea is to copy or write the phrases into a spread sheet, add a column for the file name, and another column for the MadCap Flare snippet XML code, then write a short VBA script that creates a new snippet file for each phrase in the spread sheet, and saves the files on your desktop. Take a look at the screen capture:

excel_1

The formula for the file name is =LEFT(SUBSTITUTE(B2;” “;”_”);30)&”.flsnp”It first replaces all spaces with “_”, and just keeps the 30 first characters of the phrase, ending with the “flsnp” file extension.

The Excel formula for the MadCap Flare XML code is

=”<!–?xml version=”&CHAR(34)&”1.0″&CHAR(34)&” encoding=”&CHAR(34)&”utf-8″&CHAR(34)&”?>
xmlns:MadCap=”&CHAR(34)&”http://www.madcapsoftware.com/Schemas/MadCap.xsd”&CHAR(34)&&#8221; MadCap:lastBlockDepth=”&CHAR(34)&”2″&CHAR(34)&” MadCap:lastHeight=”&CHAR(34)&”43″&CHAR(34)&” MadCap:lastWidth=”&CHAR(34)&”878″&CHAR(34)&”>
<head>
</head>
<body>
<p>“&B2&”</p>
</body>
</html>”

Note that for the text to make sense in an Excel formula you have to replace (search and replace) all quotation marks () with “&CHAR(34)&”. You also have to link the phrase column to the XML code formula (see the red text “&B2&” above).

The Excel VBA code needed to create a new snippet file for every phrase is:

Option Explicit
Sub GenerateSnippets()

Dim nbr, i As Integer
nbr = ActiveSheet.UsedRange.Rows.Count

Dim Text() As String
Dim FileName() As String
ReDim FileName(nbr – 1) As String
ReDim Text(nbr – 1) As String

For i = 2 To nbr – 1
FileName(i) = “C:\Users\Your user name here\Desktop\” + ActiveSheet.Cells(i, 1)
Text(i) = ActiveSheet.Cells(i, 3)
Open FileName(i) For Output As i
Print #i, Text(i)
Close i
Next i

End Sub

Now you can just run the VBA macro to generate the snippet files, and then move them to the folder of your Flare project.

Feel free to download this Excel sheet and play around with it 🙂

Advertisements
Posted in Excel, MadCap Flare
3 comments on “Generating MadCap Flare snippet files using Excel (VBA)
  1. nitabeck says:

    What a fantastic tip-n-trick to share with Flare users. Sometimes, a little automation can go a long way! I appreciate also that you’ve provided the spreadsheet to play with. I’ll definitely point your post out to the Flare user group that I manage.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: