Error Passing Formula from Access to Excel (1 Viewer)

[pooldead]

I wrote the following formula in Excel originally in order to concatenate a bunch of data.

=TEXTJOIN(";",TRUE,IF(A:A=A2,B:B,""))

I am trying to pass the formula into Excel from Access in order to run the concatenation on selected files. However I keep getting an "Application-defined or object-defined error".

Dim xlFormula as String
Dim xlSheet as Excel.Worksheet

xlFormula = "=TEXTJOIN("""";"""",TRUE,IF(A:A=A2,B:B,""""))"
With xlSheet
    .range("C2").formula = xlFormula
End With

 

[Gasman]

You have to set the xlsheet object as well as the excel app object?

 

[pooldead]

Sorry, I shortened the code. I do have as my declarations:

Dim xlApp As Excel.Application
Dim xlWork As Excel.workbook
Dim xlSheet As Excel.Worksheet
Dim xlFormula As String

Set xlApp = CreateObject("Excel.Application")
Set xlWork = xlApp.Workbooks.Open(fileName)
Set xlSheet = xlWork.Worksheets(sheetName)

 

[Gasman]

Please show ALL the code, otherwise it is just a guessing game.?

 

[pooldead]

My apologies

Public Function excelModify(fileName As String, sheetName As String)

    Dim xlApp As Excel.Application
    Dim xlWork As Excel.workbook
    Dim xlSheet As Excel.Worksheet
    Dim newPath As String, fileNameWOPath As String, fileNameWOExt As String, path As String, xlFormula As String
    
    xlFormula = "=TEXTJOIN("""";"""",TRUE,IF(A:A=A2,B:B,""""))"

    Set xlApp = CreateObject("Excel.Application")
        Set xlWork = xlApp.Workbooks.Open(fileName)
            xlApp.Visible = False
            
            Set xlSheet = xlWork.Worksheets(sheetName)
                With xlSheet
                    .Range("C1").Formula2R1C1 = "Workgroup_Members"
                    .Range("C2").Formula = xlFormula
                    .Range("C2").AutoFill Destination:=.Range("C2:C" & .Range("B" & .Rows.Count).End(xlUp).Row)
'                        .AutoFill Destination:=Range("C2:C" & .Range("B" & .Rows.Count).End(xlUp).Row)
'                    .Range("C2:C" & .Range("B" & .Rows.Count).End(xlUp).Row) = "=TEXTJOIN(';',TRUE,IF(A:A=A2,B:B,""))"
                    .Range("C2", .Range("C2").End(xlDown)).Copy
                    .Range("D2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                    .Columns("B:C").Delete shift:=xlToLeft
                    .Range("A1:B" & .Range("B" & .Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
                    .Range("A1").Select
                End With
            Set xlSheet = Nothing
            
            fileNameWOPath = Right(fileName, Len(fileName) - InStrRev(fileName, "\"))
            fileNameWOExt = Left(fileNameWOPath, InStr(fileNameWOPath, ".") - 1)
            path = Left(fileName, InStrRev(fileName, "\"))
            
            newPath = path & fileNameWOExt & "_Edited.csv"
            
            xlWork.SaveAs newPath
        Set xlWork = Nothing
        
        xlApp.Quit
    Set xlApp = Nothing

End Function

 

[Gasman]

Try setting that xlFormula in the immediate window. I get an error.
I think it is confused with all the quotes?

 

[Gasman]

I think you need this for the formula?

xlFormula = "=TEXTJOIN("";"",TRUE,IF(A:A=A2,B:B,""""))"

TEST it in the immediate window as it is not recognised in 2007

 

[pooldead]

I received the same error you did when I tested in Immediate

 

[Gasman]

I received the same error you did when I tested in Immediate

That string I posted does not produce and error?, and looks the correct syntax to me?

 

[pooldead]

Yeah, when I post it like that in Excel it works fine. It's when I try to translate it from Access that it goes haywire. I tried running the string you posted in Immediate and it error'd out

 

[Gasman]

Yeah, when I post it like that in Excel it works fine. It's when I try to translate it from Access that it goes haywire. I tried running the string you posted in Immediate and it error'd out

So in that picture in post #9, is that not the correct syntax?

 

[pooldead]

The syntax is correct, but Access is producing that error on the formula when I try to run it. The only difference between the pic and Access is having an extra set of double quotes around ";" in order to define it as a string when the formula gets passed into Excel

 

[Gasman]

But I pasted that TEXTJOIN string using your code into an empty worksheet using Access?

 

[arnelgp]

.Range("C2:C" & .Range("B" & .Rows.Count).End(xlUp).Row) = "=TEXTJOIN(';',TRUE,IF(A:A=A2,B:B,''))"