VBA excel crashes
 

Hi,

I have a macro that does a database query. I recorded a macro and then just
inserted a line that specifies which directory to use. Since the user can
input the directory into Cell A1

I have

Dim tempDir as string
tempDir=Sheets("summary").Range("a1").value

When I try inserting this into the query excel crashes, but when the macro
is run without the tempDir everything works.

Does anyone know what the problem could be? Here is the code with the
variable tempDir used. (actually it is named "temp_dbf_dir")


Sub HarvestOutput1()
Dim temp_dbf_dir As String

temp_dbf_dir = Sheets("Summary").Range("C4").Value

With Workbooks("Harvest
Data.xls").Worksheets("Sheet1").QueryTables.Add(Ar ray( _
Array( _
"ODBC;DSN=Visual FoxPro
Tables;UID=;PWD=;SourceDB=temp_dbf_dir;SourceType= DBF;Exclusive=No;BackgroundFetch=Yes;Collat" _
), Array("e=Machine;Null=Yes;Deleted=Yes;")),
Sheets("Sheet1").Range("A1"))
.Sql = Array( _
"SELECT gmab_standard_1.product, gmab_standard_1.purpose,
gmab_standard_1.`group`, gmab_standard_1.disc_rate, gmab_standard_1.time,
gmab_standard_1.period, gmab_standard_1.t_from, gmab_standard_1.t_to," _
, _
" gmab_standard_1.af_gmab, gmab_standard_1.af_gmdb,
gmab_standard_1.af_gmib, gmab_standard_1.af_gmwb, gmab_standard_1.assets_ga,
gmab_standard_1.assets_sa, gmab_standard_1.db_chg_pv, gmab_standard_1.db" _
, _
"_dth_pv, gmab_standard_1.ey_gmab_ch, gmab_standard_1.ey_gmab_cl,
gmab_standard_1.ey_ib_chpv, gmab_standard_1.ey_ib_clpv,
gmab_standard_1.pol_ct_dth, gmab_standard_1.pol_db_sur,
gmab_standard_1.policie" _
, _
"s_e, gmab_standard_1.res_sepacc, gmab_standard_1.surplus,
gmab_standard_1.surplus_ga, gmab_standard_1.surplus_sa,
gmab_standard_1.y_wb_ch_pv, gmab_standard_1.y_wb_cl_pv,
gmab_standard_1.input_var" & Chr(13) & "" & Chr(10) & "FRO" _
, "M gmab_standard_1 gmab_standard_1")
.PostText = True
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.SavePassword = True
.SaveData = True
.Refresh False
End With
End Sub