Connection with Path instead of Drive letter?

[largeselection]

Hi,

I have some code which creates a connection to a folder at a path I specify in a cell, but I'd like to be able to have others use it on the network drive. The issue is that not everyone has the same Drive letter that I have. I've tried the fix I usually use, but it did not work.

How the path currently appears in my workbook:
E:\MainFolder\Project1

I've tried changing it to:
\\Server\Drive\Mainfolder\Project1

but my code will not create the connection with that. Any ideas on how to fix?

This is the code I use to create the connection:

Dim Fpath as String
Fpath = Sheets("MAIN").Range("B12").Value
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DBQ=" & Fpath & ";DefaultDir=" & Fpath & ";" _
        ), Array( _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransacti" _
        ), Array("ons=0;Threads=3;UID=admin;UserCommitSync=Yes;")), Destination:=Sheets("QUERY").Range("$A$1")).QueryTable

 

[schielrn]

Where it says Drive are you actually putting the drive letter?

This little script will give you the path of a mapped drive:

Set objNetwork = CreateObject("Wscript.Network")
Set colDrives = objNetwork.EnumNetworkDrives
strDriveLetter = inputbox("Enter Drive Letter")
for i = 0 to colDrives.count-1
if colDrives.Item(i) = ucase(strDriveLetter) & ":" then
strCopy = colDrives.Item(i+1)
exit for
end if
'Dummy = InputBox("","" , strCopy)
next
 
' Declare an object for the word application ' 
Set objWord = CreateObject("Word.Application") 
 
' Using the object ' 
With objWord 
   .Visible = False             ' Don't show word ' 
   .Documents.Add               ' Create a document ' 
   .Selection.TypeText strCopy  ' Put text into it ' 
   .Selection.WholeStory        ' Select everything in the doc ' 
   .Selection.Copy              ' Copy contents to clipboard ' 
   .Quit False                  ' Close Word, don't save '  
End With
msgbox strCopy & " copied to clipboard"

It is a vbscript and actually opens word to save the path to the clipboard, but all that isn't really necessary as you could return it to excel.

The only thing I could think is wrong from looking at the code is that you are not giving the correct path?

Hope that helps.

 

[largeselection]

No I don't list the actual drive letter. Sorry for the confusion.

The server is actually two parts - so when I mapped to the drive I'm on something like Division12 on MainGroup1 so in the past I've always replaced the "E:/" with "//MainGroup1/Division12/"

I've tried this path in a different bit of code (that doesn't create a connection) and it works so I think it's the right path.

Thanks for your suggestion!

 

[largeselection]

I ran that bit of code that you sent and it confirmed the path was correct so I'm still not sure what the problem is.

 

[schielrn]

Sorry that is as much knowledge as I can give on this. Not sure what the issue could be if the path is correctly put in there.