Excel VBA tutorials to enhance WordPress pages - #2 - Accessing data files

Coding for Part II

Using Microsoft Excel to improve a hosted WordPress site's functionality - WordPress.org not WordPress.com

Creating data text files via Excel and uploading to a WordPress server; using PHP include and HTML drop down menu

Copy these notes to NotePad and save.

1. Create / Open Microsoft Excel Workbook;

Copy the data for Excel Sheet2 from the video (or create own). Top left cell of table ('University'): B6.

2. Open Excel's Visual Basic Editor, insert a Visual Basic Module and add this sub-routine;

Set up a folder 'phpFolder' on your Desktop.

Sub php_UniTextFiles()

Dim n As Integer

Dim strUniName As String
Dim strUrl As String

Dim strStudents As String
Dim strViceChancellor As String

Dim strWikiComment As String
Dim strWikiUrl As String

Dim strText(2) As String
Dim outputText(2) As String

Dim fileName As String
Dim filePath1, filePath2 As String


Worksheets("Sheet2").Select

For n = 1 To 5

strUniName = Cells(6 + n, 2)
strUrl = Cells(6 + n, 3)

strStudents = CStr(Cells(6 + n, 4))
strViceChancellor = Cells(6 + n, 5)

strWikiComment = Cells(6 + n, 6)
strWikiUrl = Cells(6 + n, 7)

fileName = strUniName

' NOTE: adjust the file paths below to match your own PC folder layout

filePath1 = "C:\Users\YourName\Desktop\phpFolder\" & fileName & "_textFile.txt"
filePath2 = "C:\Users\YourName\Desktop\phpFolder\" & fileName & "_wikiFile.txt"


NOTE: Youtube 'Description' does not allow HTML characters so change ++ into the opening left pointing arrow and +++ into the closing right pointing arrow (delete this note)

strText(1) = "++div style='font-size:1.2em;'+++" & strUniName & "++br+++ Number of students: " & strStudents
strText(2) = "++br+++ Vice-Chancellor: " & strViceChancellor & " ++br+++ ++a href='" & strUrl & "' target='_blank'+++ University Web site ++/a+++ ++/div+++"

outputText(1) = strText(1) & strText(2)

outputText(2) = strWikiComment & "++br+++ ++a href='" & strWikiUrl & "' target='_blank'+++ More on Wikipedia .... ++/a+++"


Open filePath1 For Output As #1
Print #1, outputText(1)
Close #1

Open filePath2 For Output As #1
Print #1, outputText(2)

Close #1

Next

End Sub


3. Download and or activate FileZilla FTP client (or your own choice).

Log on to your WordPress web site and open 'wp-content' directory (directory is same as 'folder' on Windows);
If not already done create New Directory called 'phpFolder' - keep same name as own PC;
Upload the five text files and five wiki files from 'Desktop\phpFolder' into 'wp-content/phpFolder';


4. Add New Page to your WordPress pages for practice;

Make Permalink page title 'PHP Text Files' which will create URL address 'your site / php-text-files/ '
Add and activate Plug-in called 'Insert PHP';
Copy following lines in Editor Text View of new page;

start -------------------------------------------------------------

[insert_php] $uniStr=$_GET["uni"] ; [/insert_php]

[insert_php] include 'wp-content/phpFolder/' . $uniStr . '_textFile.txt' ; [/insert_php]

++P+++Here's what Wikipedia says about this University:

[insert_php] include 'wp-content/phpFolder/' . $uniStr . '_wikiFile.txt' ; [/insert_php]

NOTE: square brackets, single quotes, full-stops / periods, semi-colons and slash in end brackets. (delete this note)

To look at another university choose from the drop-down list below:

++form action="/php-text-files/" method="get"+++
++select name="uni"+++
++option value="[insert_php] echo $uniStr; [/insert_php]"+++Choose a University++/option+++
++option value="Sheffield Hallam University"+++ Sheffield Hallam University++/option+++
++option value="University of Bradford"+++University of Bradford++/option+++
++option value="University of Leeds"+++University of Leeds++/option+++
++option value="University of Sheffield"+++University of Sheffield++/option+++
++option value="University of York"+++University of York++/option+++
++/select+++
 
++input type="submit" value="Submit" /+++
++/form+++

------------------------------------------------------------ end


End Part II