access VBA中的Excel替换函数

Question

我试图在access VBA中运行一些Excel代码。它几乎可以工作，但我的替换功能有问题。

Sub test2()
    Dim lngColumn As Long
    Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim blnEXCEL As Boolean

    blnEXCEL = False

    ' Establish an EXCEL application object
    On Error Resume Next
    Set xlx = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set xlx = CreateObject("Excel.Application")
        blnEXCEL = True
    End If
    Err.Clear
    On Error GoTo 0
    xlx.Visible = True
            
    ' Replace C:\Filename.xls with the actual path and filename
    ' of the EXCEL file from which you will read the data
    Set xlw = xlx.Workbooks.Open("Q:\21 Projekty\FlowControl\Flow.xlsx", , True) ' opens in read-only mode

    ' Replace WorksheetName with the actual name of the worksheet
    ' in the EXCEL file
    Set xls = xlw.Worksheets("Flow")
            
    ' Replace A1 with the cell reference from which the first data value
    ' (non-header information) is to be read
    Set xlc = xls.range("A1") ' this is the first cell that contains data
            
    With xls
        .Columns(2).Insert Shift:=xlToRight
        SelectedColumn = SelectedColumn + 1
        
        .Columns(3).Copy _
            Destination:=.Columns(1)
            
        .Columns(2).Replace What:=" - *", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False 
    'Error there  Subscript out of range (Error 9) 
        
        .Columns(3).Replace What:="* - ", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        
        .Columns(2).AutoFit
        .Columns(3).AutoFit
    Errorhandling:
        'Application.ScreenUpdating = True
        'End Sub
    End With
End Sub

Answer 1

如前所述，您需要处理像xlToRight这样的Excel常量。

这样做的一种方法是用常量代替它们的值，另一种方法是将它们声明为代码顶部的常量，如下所示。

Const xlToRight = -4161
Const xlPart = 2
Const xlByRows = 1

Answer 2

问题是excel和access vba中的替换函数有点不同。现在我只使用：

.columns(2).Replace What:=" - *", Replacement:=""

问题解决了。

编辑：

它应该是

.columns(2).Replace What:="* - ", Replacement:="", LookAt:=2, _
        SearchOrder:=1, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

​