vba - Excel to Access 导入假列

Question

我正在处理大量历史数据，并制作了一个宏来将这些 Excel 电子表格格式化为 Access 友好的信息。但是，将这些 excel 文件导入 Access 时遇到问题。无论我在 VBA 中编码什么，Access 仍然认为在前四个实际数据之后有大约 30 个空白列。防止这种情况的唯一方法是手动进入并删除列。出于某种原因，我的 VBA 代码不会阻止它。我正在处理很多电子表格，因此手动删除这些列需要相当长的时间。我的代码如下；关于如何让 Access 正确解释这些的任何想法？

    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 将“使用的范围”作为表格导入，这与“所有包含数据的单元格”并不完全相同。

'UsedRange' 属性获取空字符串、格式和(有时)实时选择和命名范围......

...而且它有时会无缘无故地捡起一个超大的二手范围，雷德蒙德以外的任何人都不会知道。

所以你的下一个工作是重新定义短语“Access 友好”

最“Access 友好”的方法是导出 csv 文件 - 您可能会听到相反的意见，但不是来自经常这样做以至于遇到 JET OLEDB 4 Excel 驱动程序中的内存泄漏的任何人。

但最简单的方法是 指定范围在链接表中或 - 更好的是 - 连接 ODBC 的 SQL 查询:

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

注意指定工作表和范围的格式:'$'，而不是'!'将工作表名称和地址分开。您可以使用 Sheet$，但您又回到了整个猜测使用范围的事情上。

请注意，我说过有一个标题行，单元格 D3:E3，列出了字段名称“HDR=YES”。您不必这样做，但我确实推荐它:按名称调用列对于数据库引擎来说更容易。

请注意，我还指定了“IMEX=0”，这应该意味着“不要猜测字段类型，它们都是文本”，但 JET 数据库驱动程序会毫不在意地处理它。因此，将其导入带有文本列的表中，并在随后的 MS-Access 查询中对这些文本字段执行数据类型和格式工作。

'IN' 后面的那两个引号？不要问。

我正在使用“.xls”文件，Excel 8.0 版。查找 ConnectionStrings.com 以获取更高版本，或在 MS-Access 中为所需文件类型构建链接表，然后查询 Tabledef.Connect 属性。

现在您会想到，您可以动态地构建查询，为从庞大的电子表格文件夹中连续导入提供文件名和工作表名；所以这是最后一段 SQL，以及指定字段名称的原因:

JET SQL 用于将行从 Excel 范围直接插入 MS-Access 表:
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];

这将在 MS-Access 数据库中运行:不要尝试从要导出的电子表格文件中的 ADODB 连接执行它。