Subscribe Bookmark RSS Feed

Connecting to Sheet in Excel

gene

Community Trekker

Joined:

Jun 23, 2011

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.
3 REPLIES
gene

Community Trekker

Joined:

Jun 23, 2011

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

Gene
alex

Community Trekker

Joined:

Jun 23, 2011

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
pmroz

Super User

Joined:

Jun 23, 2011

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

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

Regards,
Peter