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
|