Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'm working in a large corporate environment. I wrote an Excel VBA macro utility that extracts data from a series of workbooks, creates tab delimited files for the data, and then moves those files to a folder for use by someone else. Everything works great.
    Now I've been asked to have the utility put the files in a folder on a Unix server. To do this, the admin set up what he called a "Samba shared drive" with some magic I don't care to ask about. Basically it gives me a path with an IP address that leads to the folder. When we did this in a test environment the path looked like this "\\10.25.430.106\Data" and I simply used that path in my .movefile statement (with my Scripting.FileSystemObject) and it worked. Now we are trying to go to a production server (the IP address is different) and the .movefile statement is failing with "run-time error 76". When I tried to get to the production path by using windows explorer (or My Computer) it asks for a userid and password. The admin gave them to me and said that I need to specify them in order to get to the production folder. I can do that in windows explorer but I can't figure out how to do that in VBA code. I'm also using the .FileExists and .DeleteFile statements to replace existing files in this same path.
    Any suggestions? Is there a way to work with protected folders in Excel? I can hardcode the userid and password in the code if I need to but I have no clue where to start. Any ideas would be most welcome!
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    I think you will need to map a drive letter to the connection. You can find instructions in How to Add and Remove Network Connections.

    I found a slightly simpler example here.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Wow! Great idea. Thanks!
    Someone here suggested mapping a drive on each user's PC but we don't want to do that because we don't want them to have access to the production folder directly. This solves that problem!
    Thanks for the simpler version, I think I like that better!
    One question, is there a way to determine if a local drive letter is already mapped? I'd hate to overlay and then delete the mapping of an existing path for one of my users.
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='VBA-Bumbler' post='793052' date='11-Sep-2009 22:56']is there a way to determine if a local drive letter is already mapped?[/quote]
    There is probably a much more sophisticated way to do this, but you could try
    str = Dir("H:\NUL")
    where H is the drive letter you want to check.

    This seems to return "NUL" if the drive exists, and an empty string if it doesn't

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you very much! You are making me look good and I appreciate it!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •