Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Running Excel 2000 VBA Application on Excel 2003 | Excel Worksheet Functions | |||
Excel randomly crashes during save as | Excel Discussion (Misc queries) | |||
Excel XP Crashes | Excel Discussion (Misc queries) | |||
Excel crashes when opening excel document by double-clicking icon | Excel Discussion (Misc queries) |