how do I get this import to prompt for filename? I have to use ADao Import SQL because it's the only way to do version 2007 excel file. main import is below in blue. at very bottom is prompt function script that I can't figure out how to turn into variable and insert in place of path and filename. Thank you.
dim connectionString as c
connectionString = "{A5API='Excel',A5Syntax='Excel',FileName='C:\alpha5_10\2007.xlsx', A5ExcelVersion=2007}"
delete options
dim options as p
options.ConsolidateArguments= .f.
options.AddTablesToDatabase= .t.
options.ShowProgress= .t.
options.AllowCancel= .t.
delete a_import
dim a_import[0] as p
a_import[].ObjectName = "Page1_1$"
a_import[..].SQLSelectStatement = "SELECT ROW, SOURCE_DIVISION_CODE, BRANCH_CODE, SUPITEM, SUP, ITEM, PRODUCT_DESCRIPTION, PRICE_DIFF, PRICE, UOM_1, PRICE_CHANGE, BIN, TYPE, PRICE_CHG_DATE, QUANT_OH, LAST_REC_DATE FROM [Page1_1$]"
a_import[..].SQLType = "Portable"
a_import[..].Arguments = ""
a_import[..].ImportType = "Append to existing table"
a_import[..].AppendOption = "Append unique, replace existing"
a_import[..].AppendToTableName = a5.get_path() + chr(92) + "labels"+".dbf"
a_import[..].AppendFieldMap = <<%txt%
row|row
source_division_code|source_division_code
branch_code|branch_code
supitem|supitem
sup|sup
item|item
product_description|product_description
price_diff|price_diff
price|price
uom_1|uom_1
price_change|price_change
bin|bin
type|type
price_chg_date|price_chg_date
quant_oh|quant_oh
last_rec_date|last_rec_date
%txt%
a_import[..].MasterTableKey = "Supitem"
a_import[..].TransactionTableKey = "Supitem"
dim flagSilent as l
delete args
dim args as sql::arguments
flagSilent = .f.
delete p
dim p as p
p = a5_AlphaDAO_Import(connectionString,a_import,options,flagSilent,args)
'p has an .hasError and .errorText property
'a_import[] has the following properties for each item: .countRecordsImported, .hasError, .errorText, .userCancelled
this is the standard "prompt for filename" script, converted to xbasic:
'Prompt for a filename and store result in a variable called 'prompt'.
dim prmpt_title as c
dim prmpt_filter as c
dim prmpt_default as c
dim prmpt_flag as c
prmpt_title = ""
prmpt_filter = "(*.*)"
prmpt_default = "C:\alpha5_10\2007.xlsx"
DIM SHARED prompt AS C
prompt = ui_get_file( a5_eval_expression(prmpt_title,local_variables()),prmpt_filter,prmpt_default,"X")
dim connectionString as c
connectionString = "{A5API='Excel',A5Syntax='Excel',FileName='C:\alpha5_10\2007.xlsx', A5ExcelVersion=2007}"
delete options
dim options as p
options.ConsolidateArguments= .f.
options.AddTablesToDatabase= .t.
options.ShowProgress= .t.
options.AllowCancel= .t.
delete a_import
dim a_import[0] as p
a_import[].ObjectName = "Page1_1$"
a_import[..].SQLSelectStatement = "SELECT ROW, SOURCE_DIVISION_CODE, BRANCH_CODE, SUPITEM, SUP, ITEM, PRODUCT_DESCRIPTION, PRICE_DIFF, PRICE, UOM_1, PRICE_CHANGE, BIN, TYPE, PRICE_CHG_DATE, QUANT_OH, LAST_REC_DATE FROM [Page1_1$]"
a_import[..].SQLType = "Portable"
a_import[..].Arguments = ""
a_import[..].ImportType = "Append to existing table"
a_import[..].AppendOption = "Append unique, replace existing"
a_import[..].AppendToTableName = a5.get_path() + chr(92) + "labels"+".dbf"
a_import[..].AppendFieldMap = <<%txt%
row|row
source_division_code|source_division_code
branch_code|branch_code
supitem|supitem
sup|sup
item|item
product_description|product_description
price_diff|price_diff
price|price
uom_1|uom_1
price_change|price_change
bin|bin
type|type
price_chg_date|price_chg_date
quant_oh|quant_oh
last_rec_date|last_rec_date
%txt%
a_import[..].MasterTableKey = "Supitem"
a_import[..].TransactionTableKey = "Supitem"
dim flagSilent as l
delete args
dim args as sql::arguments
flagSilent = .f.
delete p
dim p as p
p = a5_AlphaDAO_Import(connectionString,a_import,options,flagSilent,args)
'p has an .hasError and .errorText property
'a_import[] has the following properties for each item: .countRecordsImported, .hasError, .errorText, .userCancelled
this is the standard "prompt for filename" script, converted to xbasic:
'Prompt for a filename and store result in a variable called 'prompt'.
dim prmpt_title as c
dim prmpt_filter as c
dim prmpt_default as c
dim prmpt_flag as c
prmpt_title = ""
prmpt_filter = "(*.*)"
prmpt_default = "C:\alpha5_10\2007.xlsx"
DIM SHARED prompt AS C
prompt = ui_get_file( a5_eval_expression(prmpt_title,local_variables()),prmpt_filter,prmpt_default,"X")
Comment