I need to transfer data between files and currently I am doing this using VBA by opening the source file and getting data from its named range into target file's named range. This process given the volume of the source files over the network has became cumbersome due to opening each file to extract data then closing it particulalry if a user needs to do this several times a day. Instead of employing this method I would like to use SQL calls from target to source file, so without opening the source file VBA using SQL call can read data and update the target range on target file.
Attached are two sample files to illustrate the problem. Those are SQL_TestA (Source) and SQL_TestB (Target). The current method which is working just fine is that when I click on the button "Transfer Data" on Sheet1 of the FileB it prompts me to open FileA then it copies from the MyRange of FileA into NewRange of FileB.
In my actual process there is no manual file open, VBA goes to desired path and opens up the files in the specified folder than extracts data from specified ranges and close the file and repeats the cycle. What I need to do here is that I want to replace this process with SQL calls so not a single file is opened in the process hence time savings for the users. Also, another benefit will be to bypass some active X related errors in some files on file open.