Excel to Access import false columns

Question

I'm dealing with a lot of historical data and I made a macro to format these excel spreadsheets into an Access friendly information. However I'm having issues when importing these excel files into Access. No matter what I code into the VBA, Access still believes there are about 30 blank columns after the first four of actual data. The only way to prevent this is to manually go in and delete the columns. For some reason my VBA code just won't prevent it. I'm dealing with a lot of spreadsheets, so it's going to take considerable time to manually delete these columns. My code is below; any ideas on how I could make Access interpret these correctly?

    Public CU_Name As String
Sub RegulatorFormat()
    Dim wks As Worksheet
    Dim wks2 As Worksheet
    Dim iCol As Long
    Dim lastRow As Long
    Dim Desc As Range
    Dim lastCol As Long

    Application.ScreenUpdating = False
    Worksheets.Select
    Cells.Select
    Selection.ClearFormats
    Call FormulaBeGone
    ActiveSheet.Cells.Unmerge
    CU_Name = [B1].Value

    lastRow = Range("C" & Rows.Count).End(xlUp).Row
    Set Desc = Range("A1", "A57")
    Desc.Select

    For Each wks In ActiveWindow.SelectedSheets
        With wks
            On Error Resume Next
            For iCol = 16 To 4 Step -1
                Dim PerCol As Date
                    PerCol = Cells(1, iCol)
                    .Columns(iCol).Insert
                    Range(Cells(1, iCol), Cells(lastRow, iCol)) = CU_Name
                    .Columns(iCol).Insert
                    Range(Cells(1, iCol), Cells(lastRow, iCol)) = Desc.Value
                    .Columns(iCol).Insert
                    Cells(1, iCol).Value = PerCol
                    Range(Cells(1, iCol), Cells(lastRow, iCol)) = Cells(1, iCol)
                    Range(Cells(1, iCol), Cells(lastRow, iCol)).NumberFormat = "mm/dd/yyyy"
            Next iCol
        End With
    Next wks
    Rows("1:2").EntireRow.Delete
    Columns("A:C").EntireColumn.Delete
    lastCol = ws.Cells.Find(What:="*", _
                After:=ws.Cells(1, 1), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column

    For Each wks2 In ActiveWindow.SelectedSheets
        With wks2
            On Error Resume Next
            For iCol = 52 To 6 Step -4
                lastRow = Range("C" & Rows.Count).End(xlUp).Row
                Set CutRange = Range(Cells(1, iCol), Cells(54, iCol - 3))
                CutRange.Select
                Selection.Cut
                Range("A" & lastRow + 1).Select
                ActiveSheet.Paste
            Next iCol
        End With
    Next wks2
Columns("E:ZZ").Select
Selection.EntireColumn.Delete
Application.ScreenUpdating = True
Rows("1").Insert
[A1] = "Period"
[B1] = "Line#"
[C1] = "CU_Name"
[D1] = "Balance"
Columns("E:BM").Select
Selection.Delete Shift:=xlToLeft
Call Save
End Sub

Sub FormulaBeGone()
    Worksheets.Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Select
    Application.CutCopyMode = False
End Sub

Sub Save()
    Dim newFile As String

    newFile = CU_Name
        ChDir ("W:\ALM\Statistics\MO Automation\2015")
        'Save folder
        ActiveWorkbook.SaveAs Filename:=newFile

    'Later should seperate CU's into folder by province and year
End Sub

Answer 1

Access is importing the 'used range' as a table, and that's not quite the same as 'all the cells with data'.

The 'UsedRange' property picks up empty strings, formatting and (sometimes) live selections and named ranges...

...And it sometimes picks up an oversize used range for no reason anyone outside Redmond will ever know.

So your next job is to redefine the phrase 'Access-Friendly'

The most 'Access-Friendly' method of all is to export csv files - you may hear opinions to the contrary, but not from anyone who's done it often enough to encounter the memory leak in the JET OLEDB 4 Excel driver.

But the easiest way is to specify the range in a linked table or - better still - an ODBC-Connected SQL Query:

SELECT *
  FROM [Sheet1$D3:E24] 
    IN "" [Excel 8.0;HDR=YES;IMEX=0;DATABASE=C:\Temp\Portfolio.xls];

Note the format for specifying a sheet and range: '$', not '!' to separate the sheet name and the address. You could use Sheet$, but you're back to the whole guess-the-used-range thing.

Note that I've said there's a header row, cells D3:E3, listing the field names 'HDR=YES'. You don't have to, but I do recommend it: calling columns by name is easier for the database engine.

Note that I've also specified 'IMEX=0', which ought to mean 'don't guess the field types, they are all text' but the JET database drivers treat it with cavalier disregard. So import this into a table with text columns and do your data type and format work in a subsequent MS-Access query on those text fields.

Those two quote marks after 'IN' ? Don't ask.

And I'm using an '.xls' file, Excel version 8.0. Look up ConnectionStrings.com for later versions, or build a linkled table in MS-Access to the type of file you want, and interrogate the Tabledef.Connect property.

It will have occurred to you by now that you can dynamically construct the query, supplying file names and sheet names for successive imports from a vast folder of spreadsheets; so here's the final piece of SQL, and the reason for specifying field names:

JET SQL for inserting rows directly into an MS-Access table from an Excel range:

INSERT INTO Table1 (Name, PX_Last, USD, Shares) 
SELECT *
  FROM [Sheet1$D3:E24] 
    IN "" [Excel 8.0;HDR=YES;IMEX=0;DATABASE=C:\Temp\Portfolio.xls];

This will run in the MS-Access database: don't try to execute it from an ADODB connection inside the spreadsheet files you're exporting.