Hi!

I'm pretty new to VBA and I should write a little macro but I get syntax error. Any help is really appreciated!!!

**********************

Sub replace_RU()
Dim varCompanies As Variant

'get all the companies into a variant array:
With Sheets("start")
varCompanies = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Value
End With

'convert variant 2 dimensional array (1 column wide by however many rows) to a 1 dimensional array:
varCompanies = Application.Transpose(varCompanies)

'Now build up a string using Join function (use delimiter of ',' to build up string in
'correct syntax for SQL statement):
varCompanies = Join(varCompanies, "','")

'add ' to beginning and end:
varCompanies = "'" & varCompanies & "'"

'now use in your existing SQL:
Sheets("Query1").Select
Range("A1").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Recons_801_100.mdb;DefaultDir=C:\Documents and Settings\chk3372\My Documents;DriverId=25;FIL=MS A" _
), Array("ccess;MaxBufferSize=2048;PageTimeout=5;"))
.CommandText = Array( _
"SELECT qry_recons_V801_V100_RU_FSACC.`Consolidation unit`, qry_recons_V801_V100_RU_FSACC.`FS item`, qry_recons_V801_V100_RU_FSACC.BCS_LC, qry_recons_V801_V100_RU_FSACC.ECCS_LC, qry_recons_V801_V100_RU" _
, _
"_FSACC.Diff_LC, qry_recons_V801_V100_RU_FSACC.`SumOfSumOfPV GC`, qry_recons_V801_V100_RU_FSACC.`SumOfSumOfValue in group currency`, qry_recons_V801_V100_RU_FSACC.Diff_GC" & Chr(13) & "" & Chr(10) & "FROM `C:\Recons_801_100`.qry_" _
, _
"recons_V801_V100_RU_FSACC qry_recons_V801_V100_RU_FSACC" & Chr(13) & "" & Chr(10) & "WHERE (qry_recons_V801_V100_RU_FSACC.`Consolidation unit`IN(" & varCompanies & ")" _
)
.Refresh BackgroundQuery:=False
End With
End Sub

*****************************

But I get a syntax error 1004 at ".Refresh BackgroundQuery:=False".

Maybe any ideas???

Thanks,
Georg