访问vba中的excel替换功能-Java 学习之路

我试图在访问vba中运行一些excel vba代码 . 它几乎工作，但我有替换功能的问题 . 任何人都可以看看下面的代码并告诉我有什么问题吗？

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

1 回答

问题有点不同在excel vba和访问vba中替换功能 . 现在我只使用：

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

问题解决了 .

EDIT:
它是sholud

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

回复于 2024-05-03T09:02:10+08:00

访问vba中的excel替换功能

1 回答

相关问题