Pulling data from network location to import

If they have permissions to access the network folder you have mapped as a network drive, just use the 'fully-qualified-domain-name' instead of the mapped drive letter for your links.


For example, instead of : 'X:\MyFolder\[MyFile.xlsm]mysheet'!

Use: '\\compName\MyFolder\[MyFile.xlsm]mysheet'!

Originally Posted by amorshc

And does the drive HAVE to be mapped on the local machine, or is there a way to tell excel "Hey, this is the webserver 192.168.1.200" and this will never change, pull all information from this IP address"?

Yes - you can do that. I usually have used \\computerName instead of the IP address, but I believe this will work just the same: \\192.168.1.200


Example:
='\\IP_ADDRESS\Folder_Path\[FileName.xlsm]Sheet1'!$A$1

Hey there... thought I posted this earlier.

I have an alternative solution, but I'm not sure you will like it because it goes another way.

First, let me make sure I understand. They have read/write permissions to the files on that drive - but you have locked the single workbook with a username password? If that is the case, why is that locked?


If you answer is, 'so they don't mess with it', then I would suggest this alternative:

Insert this macro into a code module:

Please Login or Register  to view this content.

Insert this macro, into the 'ThisWorkbook' code module:

Option Explicit

Please Login or Register  to view this content.

What will happen is, the user will open the book you want them to use - and will be asked: ""Refresh from CSV?" - IF they answer Yes, the refresh will happen 'under the hood' - meaning they will have no idea where the file is located on the network - so you can leave it unlocked and just squirrel it away somewhere.

If they answer no, no update will happen. This is just an example you could have the refresh launched in other ways. I just see that you have a problem that the usual ribbon-based solution is not solving - so me personally, I would just use a macro to have absolute control over what I want w/o any unfortunate limitations.

:D


EDIT:

Note - I've tested this out and for some reason I do indeed have to use the computer name in the macro solution, instead of the IP address.

That is generally a good idea anyway IMO - computer names are less likely to change

You can modify and test out the attached example.

Note: upon open, enable the content, and answer "no" to the question. Then press alt+F11 and edit the 'ThisWorkbook' code module (meaning, expand excel objects in the project explorer, right click on the object named "ThisWorkbook" and select option View Code).

Originally Posted by GeneralDisarray

Hey there... thought I posted this earlier.

I have an alternative solution, but I'm not sure you will like it because it goes another way.

First, let me make sure I understand. They have read/write permissions to the files on that drive - but you have locked the single workbook with a username password? If that is the case, why is that locked?


If you answer is, 'so they don't mess with it', then I would suggest this alternative:

Insert this macro into a code module:

Please Login or Register  to view this content.

Insert this macro, into the 'ThisWorkbook' code module:

Option Explicit

Please Login or Register  to view this content.

What will happen is, the user will open the book you want them to use - and will be asked: ""Refresh from CSV?" - IF they answer Yes, the refresh will happen 'under the hood' - meaning they will have no idea where the file is located on the network - so you can leave it unlocked and just squirrel it away somewhere.

If they answer no, no update will happen. This is just an example you could have the refresh launched in other ways. I just see that you have a problem that the usual ribbon-based solution is not solving - so me personally, I would just use a macro to have absolute control over what I want w/o any unfortunate limitations.

:D


EDIT:

Note - I've tested this out and for some reason I do indeed have to use the computer name in the macro solution, instead of the IP address.

That is generally a good idea anyway IMO - computer names are less likely to change

I currently have the refresh happening under the hood - the file is not locked, the only part that requires a username and password is the access of the file on the remote server, as explained below. I've tried messing with some macros to login under a username and password to pull a file, but they don't seem to work well with the network address I have (As its a remote server)

P.S.

Thanks for all of your help! I appreciate you taking time to reply to me.

ah ha! That's interesting. Yes, having to 'credential in' is going to hamper this kind of thing. Sounds like your company is handle things differently than I'm used to. Do you guys log into the domain (like with user specific active directory credentials) or with a generic login on non-domain computer?