1774_en-US

liliconcom

Connecting to Sheet in ExcelI see how to use the Open Database statement. I'm trying to build the "DSN=..." string outside the call. I'm trying to use Pick File to get the file, then construct the string to pass to Open Database. Something like

FN = Pick File();
ODB_String = "DSN=Excel Files;DBQ=" || FN;
dt = Open Database(ODB_String,"SELECT * FROM ExcelSheet`");

But I keep getting an error. I noticed that the path to the file has forward slashes as returned from Pick File. However the same path has backslashes when I just use

dt = Open Database("Connect Dialog");

That is, in the JSL statement strored in the table, the path has backslashes.

Gene.Re: Connecting to Sheet in ExcelRather than use munger, you can also use SUBSTITUTE to replace all occurrences of one string with another.

fn = substitute(fn, "/", "\");

Regards,
PeterRe: Connecting to Sheet in ExcelGene,
How did you use the Munger? (it seems to only do a find and replace once, you can't do a find and replace ALL)

Since I could't figure out the munger i figured out that there's a Convert command.

Convert File Path (path,,,
);

and so, added the line:
FN = Convert File Path (FN, absolute, windows);

-AlexRe: Connecting to Sheet in ExcelI got it. I just used munger to go through FN and convert all "/" tp "\" and then it worked fine.

Gene