totentanz

Seen elsewhere
More stuff from around ...
In the wild - Harry Potter world in Universal Orlando

In the wild - Harry Potter world in Universal Orlando

In the wild

In the wild

Wordpress 3.8 Upgrade Bringing you Down?

Whew, I just went through a stressful few hours - doing the Wordpress 3.8 upgrade, and trying to puzzle my way out of a number of problems …

  1. I couldn’t add any new posts
  • Could not save as draft
  • the Publish button was replaced by a Submit for Review button (hmmm, never had to do that before)
  1. Unable to configure Jetpack - kept getting an ominous error message … 

Your Jetpack has a glitch. Something went wrong that’s never supposed to happen. Guess you’re just lucky: missing_secrets

  1. My Simple Feed Stats just stopped working - well, the subscriber count went down to 0, and that was a mite depressing, so I hoped for software failure over complete internet irrelevance
  2. Unable to connect to Google Analytics using OAuth
  3. Some settings changes were saved, others were not

And trying to troubleshoot brought me yet another problem …

  1. Could not Optimize or Repair the DB (using the WP-DBManager plugin)
  • Could not Optimize or Repair the DB using my phpMyAdmin screen either (missed something here, you will see in a bit)

After searching for fixes and trying a number of things - for all of these different symptoms - I happened upon this post, describing how I would have to nuke and re-install the entire database … which made me think a bit. I also did the upgrade on my Dev box, with no problems - so the issue had to be in my Prod environment.

So I checked out the Available Storage Space on my MySQL database, and saw that it had gone negative. Ah, something was chewing up tons of space - a single plugin called StatPress was tracking every miniscule move of all of my visitors. Fascinating, I suppose, but a huge disk pig - so I uninstalled the plugin, TRUNCATED the table - and lo and behold, every one of my issues was resolved.

No disrespect to StatPress - there are other ways to use the plugin and manage my database better, I am sure - but I don’t obsess that much over what my readers are doing, so a quick snip and I’m back in action.

Load delimited data from Unix file to Excel

I needed to load some data, but sourced from a web app that was creating Unix-style text data files - where newlines are denoted with a line feed (LF) - versus Windows-style, where newlines are given the carriage return + line feed (CR+LF) treatment.

Did some googling to find the answers - an interesting discovery, apparently the Excel VBA functions InputB() and AscB() exist, but are not well documented (kind of hidden on the Input() and Asc() help pages. 

So here is a reasonably complete routine for reading in a simple Unix data file - including the ability to define the delimiter character, plus a quick method to convert Unix date-times to Excel dates.

Sub ImportUnixData()

   Dim iDestStartRow As Integer
   Dim iDestStartCol As Integer
   Dim sSourceFileName As String
   Dim iDelimiter As Integer
   Dim iSpecialCol As Integer

   Dim iFileNumber As Integer
   Dim i, iRow, iCol As Integer
   Dim sInByte As String
   Dim sInField As String

   ' Initialize variables
   
   iDestStartRow = 4                                       ' Which row has the columns headers for the destination table (sic)?
                                                        ' NB: Assumes destination table includes a row for column headers
   iDestStartCol = 1                                    ' Which row has the columns headers for the destination table (sic)?
   sSourceFileName = "C:\samplepath\samplefile.name"    ' Complete path to the input file
   iDelimiter = 9                                       ' ASCII code for the field delimiter - common ones include Tab (9) and Comma (130)

   ' Things go faster when the screen is not showing you everything ...
   ' ... but if you want to see the action, comment these lines out (and the corresponding lines below)
   
   Application.Calculation = xlManual
   Application.ScreenUpdating = False
   Application.EnableEvents = False
   DoEvents

   ' Clear any existing data from the destination table
   If ActiveSheet.UsedRange.Rows.Count > (iDestStartRow + 1) Then
      i = ActiveSheet.UsedRange.Rows.Count
      Rows((iDestStartRow + 1) & ":" & i).Select
      Selection.ClearContents
      Selection.NumberFormat = "General"
   End If

   ' Read in the data file, write it to the destination table

   iRow = iDestStartRow + 1
   iCol = iDestStartCol
   sInField = ""

   iFileNumber = FreeFile()
   Open sSourceFileName For Input As #iFileNumber

   While Not EOF(iFileNumber)
   
      ' read in data 1 byte at a time
      sInByte = InputB(1, #iFileNumber)

      ' what to do?
      If (AscB(sInByte) = iDelimiter) Then        ' Field Delimiter - write the string and move to the next column
         ActiveSheet.Cells(iRow, iCol) = sInField
         sInField = ""
         iCol = iCol + 1
      ElseIf (AscB(sInByte) = 10) Then            ' Line Feed - write the string and move to the next row
         ActiveSheet.Cells(iRow, iCol) = sInField
         sInField = ""
         iCol = iDestStartCol
         iRow = iRow + 1
      Else                                        ' Anything else? Append ... and wait for a delimiter
         sInField = sInField & Chr(AscB(sInByte))
      End If

   Wend

   Close #iFileNumber

   ' Convert the Unix time/date stamps
   ' This is a bit of a hack - provided as an example, shows how to convert specific type of UNIX data

   iRow = iDestStartRow + 1
   iSpecialCol = 1

   While ActiveSheet.Cells(iRow, 1).Value > 0
      ActiveSheet.Cells(iRow, iSpecialCol).Value = (ActiveSheet.Cells(iRow, iSpecialCol).Value + (DateDiff("d", "1/1/1900", "1/1/1970") + 1) * 86400) / 86400
      ActiveSheet.Cells(iRow, iSpecialCol).NumberFormat = "m/d/yyyy"
      iRow = iRow + 1
   Wend

   ' All Done! Restore normal screen-processing quality
   ActiveSheet.Cells(iDestStartRow, iDestStartCol).Select
   Application.EnableEvents = True
   Application.ScreenUpdating = True
   Application.Calculation = xlAutomatic
   DoEvents

End Sub

In The Wild - Gino’s East, Chicago IL

In The Wild - Gino’s East, Chicago IL

Fore!

Fore!

Google Glass hands on

Google Glass hands on

Box.com and Excel - Bug (solved)

A rare event - I’ve found an issue that I couldn’t solve with a Google search - not a common occurrence, so I should get this out there and do my part to make the world a smarter place.

Problem: I am sharing an Excel 2010 spreadsheet (sample.xlsx) on Box.com, but having problems accessing it. If I try to open the file using the Box client on an iPad, I get an error: “Unable to Read Document / Unknown exception”. However, when I open the file using Excel 2010 on a PC, no worries, the file opens right up. 

Root Cause: I checked out the Defined Names in the worksheet - and a large number of them had #REF errors. Basically, the tab I was looking at was probably copied from another worksheet, with other / older / extraneous tabs deleted. Any previously defined names “broke” - signified by the #REF indicators in the range values.

Solution: The problematic Defined Names were not used anywhere in the current incarnation of the sheet, so I deleted them all - and the problem went away; I could preview the file using the Box iPad client. Another solution would be to fix the Defined Names and eliminated the #REF errors.

Excel VBA: Modeless Form, Focus to Cell

A bit of a challenge - I have a little personal productivity “app” in Excel that shows a modeless form with some options - pick one to fill a line of data in a spreadsheet.

The challenge - I want to leave the [modeless!] form on the screen, but return focus to the spreadsheet, so I keep entering data until I need to go back to the form.

I had some trouble finding the right bit of VBA to do this - but finally did. After showing the form and copying the data to the sheet, do the following:


Cells(iRow, iCol).Select         ' Select the cell you want to return to 
AppActivate Application.Caption  ' Return focus to the Excel sheet

Game 7 v Detroit!

Game 7 v Detroit!