Date Format Fix in Excel 2003

Due to an issue in Excel 2003, sometime the date formats are not updated in the cells, which leads to the problem, that a cell shows a date but is not read as a date by macros and formulas.

You could go through each cell, press 'F2' and 'Enter' and Excel would finally identify the content of the cell as a date. But this you would do manually and not by script.

With the following VBA script for Excel, the format can be updated:

Sub UpdateColumnFormat()

    Dim usedRangeOfRows As Integer
    usedRangeOfRows = ActiveSheet.UsedRange.Rows.Count
    
    'Date
    Columns("A:A").Select
    Dim e As Range
    Dim currentValueE As Date
    For Each e In Range("A2:A" & usedRangeOfRows).Cells
        e.Select
        currentValueE = ActiveCell.Value
        ActiveCell.FormulaR1C1 = currentValueE
    Next
    
End Sub

This problem occurs regularly, when I copy data from one sheet to another by VBA scripts.

With the above script, I could fix (at least work around it) this issue.

Enjoy :)

"Create List..." in Excel 2003 with VBA

While working on data within Excel with VBA, I like to use dynamic lists to address the data and eventually to give it a name.

To create a list, the same one as you would do manually by calling "Create List..." from the context menu, you can execute the VBA script below.

Private Sub CreateList()

    Range("A1").Select
    
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    
    Dim NumberOfColumns As Integer
    Dim NumberOfRows As Integer
    Dim StartCell As Range
    Dim EndCell As Range
    
    NumberOfColumns = ActiveSheet.UsedRange.Columns.Count
    NumberOfRows = ActiveSheet.UsedRange.Rows.Count
    
    Set StartCell = Cells(1, 1)
    Set EndCell = Cells(NumberOfRows, NumberOfColumns)
    
    Dim DataRange As Range
    Set DataRange = Range(StartCell, EndCell)
    
    ActiveSheet.ListObjects.Add(xlSrcRange, DataRange, , xlYes).Name = "List1"
    Range(StartCell, EndCell).Select
    ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:=DataRange
    Range("Data").Select

End Sub

I also give the list the name "Data", which I do to access the data by name afterwords, but this is not required to create the list.

Enjoy :)

Read File Properties with Excel VBA

The VBA script for Excel below will read the 'File Version' from the file properties and will read file name and its version each into a column in a Excel sheet.

Sub getFileProductVersion()

   Dim objShell  As Shell
   Dim objFolder As Folder
   Dim objFolderItems As FolderItems
   Dim objFolderItem As FolderItem
   Dim numberOfFiles As Long
   Dim i As Integer
   Dim j As Integer

   Set objShell = New Shell
   Set objFolder = objShell.Namespace("C:\Temp\files")

   If (Not objFolder Is Nothing) Then
       Set objFolderItems = objFolder.Items()
       numberOfFiles = objFolderItems.Count
       Range("A1").Select

       For i = 0 To numberOfFiles - 1
           Set objFolderItem = objFolderItems.Item(i)
           ActiveCell.Offset(i, 0) = objFolderItem.Name
           ActiveCell.Offset(i, 1) = objFolder.GetDetailsOf(objFolderItem, 39)
       Next i

   End If

   Set objFolder = Nothing
   Set objShell = Nothing

End Sub

Enjoy :)