I wrote this to format Excel worksheets so that they will be more readily importable into Access data tables.

vbaMakeTempWorkbook

Public Sub MakeTempWorkbook(ByVal FileName As String)
    
    'Edit this if you need to change the path where the temp file is saved
    Dim strTempLocation As String
    strTempLocation = "C:\Documents and Settings\temp.xls"

    'Declare our variables
    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.Application")
    Dim xlWb As Excel.Workbook
    Dim xlWs As Excel.Worksheet
    Dim lngLastRow As Long
    Dim i As Long
    
    'Turn off warnings
    xlApp.Visible = True
    xlApp.DisplayAlerts = False
    
    'Sets
    Set xlWb = xlApp.Workbooks.Add(FileName)
    Set xlWs = xlWb.Worksheets(1)
    
    'Get last row of the worksheet
    lngLastRow = xlWs.UsedRange.Rows.Count
    
    'Delete the last 3 rows in the workbook because they're junk and we don't want Access to import them
    'Delete the first 9 rows for the same reason
    'Loop through and take out any other junk rows in the sheet
    With xlWs
        Range(lngLastRow - 2 & ":" & lngLastRow).Delete
        Rows("1:9").Delete
        For i = 1 To lngLastRow
            If Left(Cells(i, 3).Value, 1) = "(" Then
                Rows(i).Delete
                i = i - 1
            ElseIf Cells(i, 1).Value = "" Then
                Rows(i).Delete
                i = i - 1
            End If
        Next i
    End With
    
    'Save it, close it, clean up
    xlWb.SaveAs strTempLocation
    xlWb.Close
    xlApp.Quit
    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing
    
    'Fin
    
End Sub