Excel Macro (open and save files with different drives)

[jxbeeman]

Hi,
I'm trying to write a macro that will open and save a file from a lan drive
that may have different drive letters depending on what each person wanted?
I've been susessful in getting a file to open with my current mapped drive
but i won't work if other people use it.

MyFile = Dir$("G:\Daily\Report *.xls*")
Do While MyFile <> ""
DirectoryListArray(Counter) = MyFile
MyFile = Dir$
Counter = Counter + 1
Loop

ReDim Preserve DirectoryListArray(Counter - 1)

Thanks for any help,
Josh

 

[Chip Pearson]

Try some code like the following:

Sub AAA()
Dim ServerName As String
Dim ShareName As String
Dim FileName As String
Dim FullFileName As String

ServerName = "\\Altamira"
ShareName = "\Transfer"
FileName = Dir(ServerName & ShareName & "\*.*")
Do Until FileName = vbNullString
FullFileName = ServerName & ShareName & "\" & FileName
Debug.Print FullFileName
FileName = Dir()
Loop
End Sub



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)