Refresh Existing Connections Workbook- read Only Problem

Hi guys,

I was wondering whether someone can please help me with the below. For simplicity sake I have attached a folder with a snapshot of the issue I am having,

Basically I have two excel files which are found in the same folder:
- Test 1
- Test_2

Test_2 is connected with Test 1. Therefore Test 2 is feeding Data to the workbook called test 1.

I have inserted a vba code in test 1 so as soon as I open Test 1 it will refresh my data which is coming from Test 2:

Please Login or Register  to view this content.

So far so good. However I'm having a small issue. As soon as I am trying to open the workbook 'Test 2' it is opening up as 'read Only' or else it is giving me an error stating 'Test_2.xlsx is locked for editing by 'another user''.

Funnily enough when I removed the VBA which is refreshing all connections this did not occur.

Can someone please help me out/guide me whether I can change something to have the file (test_2) opening up not as read only?

Much appreciated guys!

Keibri

I'm just tried the files as they are (had to change the path in the link to match my set up) and everything worked fine.
No error at all. Just refreshed the data.

BSB

Hi badlySpelledBuoy,

Thanks for your reply!

Hmm actually you are right. I tried running the same files on a different PC and it worked (i.e. it did not open Test_2 as read only)

Somehow it is not working on the PC I originally created the connections on.

Not sure if there is a work around maybe or are there some suggestions?

Thanks a lot for your input. Much appreciated!

why not use Connection Properties - Usage - and check these options what you want?

Hi Sandy666.

Thanks for your input.

I tried as suggested: created the connections from scratch and removed the macro. Instead I selected the 'refresh data when opening the file'. However it is still resulting in the same problem.

This is driving me mad :/

Thanks for your help guys. I m trying to work around it, not sure what else I can do.

I didn't see any error as BSB too so I can't say more.
Maybe try to defrag your drive, use chkdsk to check drive for errors, kill some unnecessary apps from memory... I really don't know

have a luck

Thanks guys. Appreciate your indications.

I am still trying to figure it out

so guys, I've been at this for a whole week and finally managed to solve it and find a work around it.

Thanks to the article below:

https://www.accountingweb.com/techno...rsion-of-excel


I changed all my connections to query based and voila it worked magic.


Thanks all for your help