excel replace function in access vba

0
votes

I am trying to run some excel vba code inside access vba. It almost work but i have problem with replace function. Can anyone look at code belowe and tell me what is wrong?

 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
2
excelvbams-access
Check such things as xlToRight, xlByRows, xlPart - Access won't understand them with late binding. In Excel enter ?xlToRight into the Immediate window and it will return -4161. Use the number it gives you. – Darren Bartrup-Cook
If your code is working as is - it looks like you've set a reference to Excel, but are using late binding in the code - you define xlx as an Object rather than an Excel.Application. – Darren Bartrup-Cook
Thanks a lot, that was a real problem so my code in this part now looks like: .columns(2).Replace What:="* - ", Replacement:="", LookAt:=2, _ SearchOrder:=1, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False – Ketyas

2 Answers

1
votes

As has already been said you need to deal with the Excel VBA constants like xlToRight.

One way to do that is to substitute the constants for their values, another is to declare them as constants at the top of your code like this.

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

Problem was a little bit diffrent Replace function in excel vba and access vba. Now i only use:

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

Problem solved.

EDIT:
It sholud be 

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