Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: Opening an Excel file from network

    I am writing a sub that would access a series of fodlers in the network and open an excel file from each folder. The path for each folder can be recreated, but the name for each file could not since the name contains a date, which is different for each file. Is there a way that by knowing the network path I can read the name of the excel file located in such folder ahead of time so that I can then insert it in the code and open the file.
    Here is the code that I am currently using and that is not working:

    SN = InputBox("Enter the SN of the starting animal:")
    FolderName = ("\\Africa\Animal\Aligator\Data\"ANIMAL" & SN & "\")
    Filename = "ANIMAL" & SN & "_READINGS_" & "YYMMDD" & ".xls"
    Workbooks.Open Filename:=FolderName & Filename

    Let me point out that if I enter the correct name for the file the code above does open the file. For instance, if I replace the code above for the code below it does open the file "ANIMALSN_READINGS_082509.xls":

    SN = InputBox("Enter the SN of the starting animal:")
    FolderName = ("\\Africa\Animal\Aligator\Data\"ANIMAL" & SN & "\")
    Filename = "ANIMAL" & SN & "_READINGS_" & "082509" & ".xls"
    Workbooks.Open Filename:=FolderName & Filename

    Any help would be greatly appreciated.
    Regards, Tatooo.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by Tatooo
    I am writing a sub that would access a series of fodlers in the network and open an excel file from each folder. The path for each folder can be recreated, but the name for each file could not since the name contains a date, which is different for each file. Is there a way that by knowing the network path I can read the name of the excel file located in such folder ahead of time so that I can then insert it in the code and open the file.
    Here is the code that I am currently using and that is not working:

    SN = InputBox("Enter the SN of the starting animal:")
    FolderName = ("\\Africa\Animal\Aligator\Data\"ANIMAL" & SN & "\")
    Filename = "ANIMAL" & SN & "_READINGS_" & "YYMMDD" & ".xls"
    Workbooks.Open Filename:=FolderName & Filename

    Let me point out that if I enter the correct name for the file the code above does open the file. For instance, if I replace the code above for the code below it does open the file "ANIMALSN_READINGS_082509.xls":

    SN = InputBox("Enter the SN of the starting animal:")
    FolderName = ("\\Africa\Animal\Aligator\Data\"ANIMAL" & SN & "\")
    Filename = "ANIMAL" & SN & "_READINGS_" & "082509" & ".xls"
    Workbooks.Open Filename:=FolderName & Filename

    Any help would be greatly appreciated.
    Regards, Tatooo.
    Hi

    A bit difficult to know precisely what you are trying to do with the date (I think you example should read 080925??) but this routine might help.
    Code:
     Option Explicit
    
    Sub OpenFiles()
        Dim SN As String
        Dim FolderName  As String
        Dim Filename As String
        
        
        SN = InputBox("Enter the SN of the starting animal:")
        If SN = "" Then Exit Sub
        
        FolderName = "C:\Test\ANIMALS\"
        Filename = "ANIMAL" & SN & "_READINGS_??????.xls"
        
        Filename = Dir(FolderName & Filename)
            
        Do Until Filename = ""
            Workbooks.Open Filename:=FolderName & Filename
            Filename = Dir
        Loop
    
    End Sub
    This routine opens all files in the specified directory with the given name with 6 numbers at the specified date position (see wildcards charaters in help).

    For more info on the Dir() function try Excel Help (it is always better to do it yourself, you learn much more that way!!)

    HTH


    MTB

  3. #3
    Join Date
    Sep 2009
    Posts
    3

    Thanks MTB, your example was great!

    By looking at your example, I was able to build the sub that I needed.

    Best regards, Tatooo.

Posting Permissions

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