BreadCrumbs: Excel

Excel

From Luke Jackson

(Difference between revisions)
Jump to: navigation, search
Revision as of 14:04, 3 May 2008 (edit)
Ljackson (Talk | contribs)
(→Souces)
← Previous diff		 Revision as of 17:18, 26 September 2008 (edit)
Ljackson (Talk | contribs)

Next diff →
Line 1: Line 1:
 += Windows XP =
 +
 +== Date Filters Not Available for Pivot Table / Chart ==
 +
 +Date Filters are not supported in Excel 2003 compatibility mode.
 +
 +Re-save document in Excel 2007 format and the options will no longer be greyed out.
 +
= Mac OS X = = Mac OS X =

Revision as of 17:18, 26 September 2008

Contents

Windows XP

Date Filters Not Available for Pivot Table / Chart

Date Filters are not supported in Excel 2003 compatibility mode.

Re-save document in Excel 2007 format and the options will no longer be greyed out.

Mac OS X

File Not Found

When starting Microsoft Excel 2004 for Mac OS X you may get the error "File Not Found".

First place to check is the Excel Startup folder: 

ljackson 14:46:50 ~/Library/Preferences> cd /Applications/Microsoft\ Office\ 2004/Office/Startup/
ljackson 14:47:33 /Applications/Microsoft Office 2004/Office/Startup> ll
drwxrwxr-x   4 ljackson  admin  136B May  3 14:39 Excel
drwxrwxr-x   2 ljackson  admin  68B Apr  8  2004 PowerPoint
drwxrwxr-x   2 ljackson  admin  68B Apr  8  2004 Word
ljackson 14:47:33 /Applications/Microsoft Office 2004/Office/Startup> cd Excel/
ljackson 14:47:37 /Applications/Microsoft Office 2004/Office/Startup/Excel> ll
total 96
-rw-r--r--   1 ljackson  admin    25K May  3 14:15 8DAE9700
-rw-r--r--   1 ljackson  admin    16K May  3 14:39 Personal Macro Workbook

In my case the alphanumerical file was generated when Excel crashed. So I will delete it: 

ljackson 14:47:38 /Applications/Microsoft Office 2004/Office/Startup/Excel> rm -f 8DAE9700 
ljackson 14:47:53 /Applications/Microsoft Office 2004/Office/Startup/Excel> ll
total 40
-rw-r--r--   1 ljackson  admin    16K May  3 14:39 Personal Macro Workbook

Now I don't get the anoying error "File Not Found" every time I launch Excel.

VBA Macros 

Sub RangeDateFix()
    Dim ndate As String
    'ActiveCell.CurrentRegion.Cells.Select
    For Each c In ActiveWindow.RangeSelection.Cells
        ndate = USdateEU(c.Value)
        c.Value = ndate
    Next
End Sub

Public Function USdateEU(ByVal tdate As String)
    Dim txt As String, ftxt As String, x As Variant, i As Long
    txt = tdate
    x = Split(txt, "/")
    'For i = 0 To UBound(x)
       'MsgBox x(i)
    'Next i
    ftxt = x(1) & "/" & x(0) & "/" & x(2)
    
    USdateEU = ftxt
    'MsgBox ftxt
End Function

Public Function Split(ByVal sInput As String, _
Optional ByVal sDelimiter As String, _
Optional ByVal nLimit As Long = -1, _
Optional ByVal bCompare As Integer = vbBinaryCompare _
) As Variant
  
Dim nCount As Long
Dim nPos As Long
Dim nDelimiterLength As Long
Dim nStart As Long
Dim sOutput() As String
  
If nLimit = 0 Then
    Split = Array()
Else
    nDelimiterLength = Len(sDelimiter)
    
    If nDelimiterLength = 0 Then
        Split = Array(sInput)
    Else
        nStart = 1
        nPos = InStr(nStart, sInput, sDelimiter, bCompare)
    
    Do While nPos
      
    ReDim Preserve sOutput(0 To nCount) As String
      
        If nCount + 1 = nLimit Then
            sOutput(nCount) = Mid(sInput, nStart)
            Exit Do
        Else
            sOutput(nCount) = Mid(sInput, nStart, nPos - nStart)
            nStart = nPos + nDelimiterLength
        End If
      
    nCount = nCount + 1
      
    nPos = InStr(nStart, sInput, sDelimiter, bCompare)
      
    Loop
      
    ReDim Preserve sOutput(0 To nCount) As String
      
    sOutput(nCount) = Mid(sInput, nStart)
      
    Split = sOutput
      
    End If
  
End If
  
End Function

Souces

Retrieved from "http://wiki.ljackson.us/Excel"