Need help with filepath in "Query from Excel Files"

Thank you for the reply abousetta.

Is still cant get this to work, and probably because i dont know the syntax?

This is how my connection string looks today:

Please Login or Register  to view this content.

This is the command text:

Please Login or Register  to view this content.

Could you show me where and how to include ThisWorkbook.Path in this code?

It seems this get me one step closer. Now i get error message: ODBC Excel driver login failed Cannot update. Database or object is read-only
I dont understand why.

Ive dug some more; This may be an issue with win 7 64 bit, and Excel 32 bit. What do you think?

Source: http://lpetr.org/blog/archives/odbc-...-windows-7-x64

Will try this first thing Monday morning. Ill let you know. Thank you for your help so far!

Good morning!

Didnt work to remove the quotemarks. Returns c:\Users\temi\ThisWorkbook.path & \Ejournal Rapport.xls is not a valid path.

I still think your code (with quotes) is correct, and that it is a windows permissions issue.

#Planteplankton - I've been reading over at the water cooler that you might need a hand. I've read through the posts and I think I see the issue but I just want to be sure I understand completely. It seems there are 4 workbooks that you are dealing with here and to un-complicate things you are working with just two of them. Is this accurate? Would you mind simplifying it maybe one more step? If you could create two workbooks with bogus data and a VERY simple report (or you could attach yours provided nothing is confidential) that displays how this works in the folder you've a working copy, I think I would be able to fix this rather quickly.

kcarley:

Yes, that is correct, im working with 4 workbooks. 3 of them "feed" the 4. The number of rows in the 3 will change from week to week.

Ive uploaded 2 example books. These work on my computer as long as they are saved in C:\Test

Okay, I don't have 2003 readily available but when you get the error that the path is not valid, does it provide you a dialog to change the path? Like the picture here?

ChangePath.gif

Also a relevant question, do you have Excel 2007 or 2010 installed; your profile says you only have 2003? I was going to downgrade the .xlsx file but it will break the formula you have in it.

You could easily move this to a module and have it update on open. That way we'd just pickup the path from code and as long as long as the files are in the same folder it would work fine.

Regards,
K

Ive got excel 2010, my profile was wrong. As i understand the RapportSO.xlsx is a 2010 file, and the Ejournal Rapport.xls is a 2003.

I do not get the changepath dialog when i try to move these example files, (Although i get the dialog when moving the original files, but i think it is only 1 of the "feeder" files that provides the dialog, not all 3. ), this is the error message i get:
Error.gif

Here's what you need to do. First keep in mind that as I understand the other files... Rapport SO.xlsx is the only one that should have errors. What you have are 3 files of data and one that is meant to collect the data from them with maybe some other functions.

When you move the files, the one that is collecting data from the others will display the dialog to change the path. That is working based on post #15 and if you didn't save the file it would ask you every time. What is not working is that whoever wrote the command query included the path to 'c:\Test\Ejournal rapport.xls' as seen here: Connection&Command.gif

Without getting into all the ways to fix this the easiest is to open this dialog and change the hard-coded "From" in the command to match the path in the connection string.

1. In the workbook that will display data from another workbook click the {Data} tab in the ribbon.
2. Click [Connections] button to display all the connections. (you may have to change each one if you are connecting out to 3 different files)
3. If there are more than one listed then just start with the first one by selecting it and click the [Properties...] button.
4. In the "Connection Properties" window click the {Definition} tab.
   Note here you should see the connection string has DBQ equal to the full path and file name you've updated to and DefaultDir is equal to just the path.
5. Copy the value of DBQ and scroll down to the bottom of the "Command text:" box.
6. Paste your clipboard over `c:\Test\Ejournal rapport.xls` but be sure to leave the quotes ( ' ' ) around what you paste.

That's it. Click [OK] and such... You should see the results you want when you get back down to your worksheet.


It took me a bit to find where all this was because I usually do this in code so I don't have to go find it deep in the obscure controls that like to hide things like this. MS Access is notorious for hiding stuff like this too but if it can be coded then I do.

CHEERS!!
K

Kcarley!

I appreciate you taking your time with my problem.

I already know that i can hardcode the paths when moving the file, this is what ive done so far. What im looking for is a sollution where I dont have to do this, so i dont have to run around the workplace to fix this for everyone i send the rapport to, whenever they shuffle around their files.

As i stated in a previous post; I fear that this is a permission issue in ODBC related to my work enviroment.

Actually, there is a VBA solution as I've mentioned. I thought you 'could not' get them to work outside the "C:\Test" folder; this is a bit different and can be easily solved if you can provide any other requirements on the data "update" timing. I am thinking that if the data updates when they open the Rapport SO.xlsx file. Also to simplify things we should assume the data files are in the same directory as Rapport SO.xlsx..?

If there are other needs, it's best to ask for the moon so that they can be considered.

Please don't run around the office as there are likely people walking with scissors and you know what mom says about that!

Happy Days!
K

I toyed around with the change in direction here and I think this should work fine for your needs. Let me know if I can help apply this to the other workbooks you've mentioned.

K

Kcarley

Thank you so much! This code works. I will try to apply the code to the workbook myself, it should be a great exercise to learn something new.


/Terje