VBA - Changing Data Source Settings - Excel 2013

[cliuvena]

Hi Friends,
I need a write a vba script that changes my excel data source setting, but I can't find the command to review/edit this information at all. I've attached a screenshot of the exact information that I am looking to edit. Does any one have any experience in this?

I basically need to change it to whatever directory the file is sitting in. The rest of it is easy, I just need to find the commands to edit that directory string. Please help!

Excel Data Source Settings.png

Cheers,

Charlie Liu

[JLGWhiz]

ChDir is the command (Function) to change directories. You use it to change the from the current directory to a dofferent one. It does not make any permanent changes to Excel settings. It only makes the other directory the active directory. Your system defaults will remain the same as they were before using the command.

Please Login or Register  to view this content.

The directory must exist for it to make the change. If you want to create a new directory then

Please Login or Register  to view this content.

Then you can use the ChDir function to change to the new directory.

[cliuvena]

Hey JLGWhiz,
Thank you so much for helping out. How do I specify which "Data Source Setting" that I am changing though?
If you see my screenshot, you can see 2 in this case. I need the function to call out the list of sources that I have as well, since I will need to write a loop to change any sources that uses a specifc directory.

Thanks.

[JLGWhiz]

Sorry, I did not look close enough the first time. You are looking for code to change the data source in a query. I don't have any experience in doing queries and would need to do research to get the answer. Maybe someone with more expertise in that area will pick up on the thread and provide you a quick answer. I am pretty sure it is fairly simple, but as I said, it is not an area I am familiar with.
Regards, JLG