Connecting to Sheet in Excel

Question

I 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.

pmroz · Answer

Rather than use munger, you can also use SUBSTITUTE to replace all occurrences of one string with another.

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

Regards,
Peter

alex · Answer

Gene,
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);

-Alex

gene · Answer

I got it.  I just used munger to go through FN and convert all "/" tp "\" and then it worked fine.

Gene