MS Query
 

I have created a Workbook(A) which extracts data from different workbooks(B to G) by using MS Query and stores in Workbook (A)

Workbook(A) is sitting on Server A.

Workbooks (B to G) are located on N, O, P, Q, R, S i.e. 6 different servers on 6 different locations in 6 different cities.

Workbook(A) has a macro assigned to run the MS Query (Refresh all)

I am connected to Server A and so as my colleague.

Workbook (A) is saved in a folder to which both of us have same access rights to alter, modify etc. all the files.

I can always use macro to refresh all the data and the query runs perfectly fine. However, my colleague encounters a problem and even though can access the file, he is not able to use Macro to refresh and run the query (the error message pertains to path)

I have reached a deadlock and do not have any answer to why. I can fully understand that path problems will come for my colleagues sitting on different servers but I have failed to understand as to why we 2 of us sitting on same server in same folder but my colleague cannot run the query.

Any help would be greatly appreciated.

Ps: I am not technically strong rather a DIY person.

MS Query
 

The path specified in your macro must also be relative to the
workstation executing the macro if using mapped refs to the target
folders. Otherwise, try using an IP address for the target servers.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

IP address is probably the best way to go unless the computers are not using a static address. If they use DHCP they will keep the same address and usually everyone is happy but in the case where power goes out or the PC is offline beyond its lease you may be assigned a new IP address. If you use a full address make sure you are not getting that address through a mapped drive where it looks like it should work but does not. Also mapped drive might be different. I also amuse that all the connections have been established for each location. The links for one location will not be the same as those at another location.

Make sure the rights are set up correctly too. Access to a shared folder does not mean you have rights to the folders they are in so the link might be stopped due to improper rights.

Sometimes the surest way to to manual input the path for each location and see if you can make a link.

MS Query
 

I agree!
Thanks for the expanded detail...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

===========================

Garry and Raptor
It does make sense to me. I guess the surest solution would be to do manual input of the path for each location. I will give it a go this weekend and see whether it will work or not.

Many thanks for your time and effort in these replies; much appreciated. I will be back next week posting here my result whether I was successful in making this work. As said being a DIY I get results after few hits and trials.

Dear Garry and Raptor
Many thanks for your valuable advise. After few tries and it works perfectly now. I opened the connection in MS Excel and changed the path. Thank you very much.

Now, I need to ask you another question please.

There are 6 connections on each worksheet.

There will be 12 workbooks; 1 for each month; each workbook will have 4 worksheets; each representing each week.

You can imagine the amount of work involved if I have to create and manually correct each connection path.

Is there a simple way to work around this?

And to clarify further macro written in my first question. I did not wrote any code; I do not know actually how to write it. All I had done was recorded all the steps in a macro to activate 'refresh all' command found under Data tab.

I am assuming that a code will need to be written for performing above task. In simple terms, what I want is that every time I create a connection, correct path is recorded by default rather than me going in and correcting each path manually.

Any suggestions would be much appreciated.

Kindest regards

MS Query
 

On Wed, 29 May 2013 10:35:59 +0100, SinghNZ
wrote:

Is there a simple way to work around this?


Come up with a better paradigm for performing the data archiving task
you think this accomplishes.

It is like you are setting up all these interdependent links as an
exercise, as no professional would set up such an array to store critical
accounting data. It has too many vulnerabilities.

It would seem the location for a lot of the worksheets would best be on
the same sheet, instead of keeping them separate, thereby solving many of
the problems your concept has created.

Hi
Thanks for your suggestions. I am a novice. I have my own reasons for doing this way, may be not efficient in the eyes of a professional.

(Vulnerabilities) Each worksheet will have data in about 400 to 500 rows. Having this amount of data on each worksheet, and all worksheets in one workbook, in my humble view, opens up another grave vulnerability - corruption of data. I am trying to create separate individual worksheets to avoid post data corrupt scenario. Should this to occur, only that particular sheet will get corrupted and unusable, and not the entire data in all the worksheets. This is the sole reason for creating individual worksheets.

(It would seem the location for a lot of the worksheets would best be on the same sheet, instead of keeping them separate, thereby solving many of the problems your concept has created.) - I would love to go your way as suggested if this can save me all the effort of manually entering path for each connection. My only worry is, I reiterate, post data corruption scenario. Do I assume correctly that creating a back up file could be the simple solution for post data corruption scenario? If so, I will need to learn how to extract / use data from the back up file, (in the case of data corruption in the main file) and make my main workbook working again.

Regards