dcsimg
Results 1 to 13 of 13

Thread: vba excel dir

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    7

    Angry vba excel dir

    In a macro in Excel I do the following:

    Code:
        Dim path, FileName As String
        path = ThisWorkbook.path
        FileName = Dir$(path, "*.docx")
    Working on local files (on my laptop) it works fine, but as soon as I try this on a network, this gives an error:
    Types don't match

    Trying Dir instead of Dir$ gives the same error.

    What am I doing wrong?

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,247

    Re: vba excel dir

    Your are not using the correct syntax for Dir()
    The first parameter is the path including a filename or wildcard, the second parameter is to specify the attributes
    Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

    Syntax
    Dir [ (pathname [ ,attributes ] ) ]

    The Dir function syntax has these parts.

    Part Description
    pathname Optional. String expression that specifies a file name — may include directory or folder, and drive. A zero-length string ("") is returned if pathname is not found.
    attributes Optional. Constant or numeric expression, whose sum specifies file attributes. If omitted, returns files that match pathname but have no attributes.
    Code:
    Dim Folder As String, FileName As String
        Folder = ThisWorkbook.path
        FileName = Dir$(Folder & "\*.docx")

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    7

    Re: vba excel dir

    Quote Originally Posted by Arnoutdv View Post
    Your are not using the correct syntax for Dir()
    The first parameter is the path including a filename or wildcard, the second parameter is to specify the attributes


    Code:
    Dim Folder As String, FileName As String
        Folder = ThisWorkbook.path
        FileName = Dir$(Folder & "\*.docx")
    I did try that already, but unfortunately it doesn't help.

  4. #4
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,247

    Re: vba excel dir

    Can you show your actual code and print the content of ThisWorkbook.Path
    Code:
    Dim Folder As String, FileName As String
        Folder = ThisWorkbook.path
        Debug.Print "Current path: " & Folder
        FileName = Dir$(Folder & "\*.docx")

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,352

    Re: vba excel dir

    Welcome to VBForums

    Thread moved from the 'General Developer' forum (for programming topics that don't fit in other forums) to the 'Office Development/VBA' forum.

  6. #6

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    7

    Re: vba excel dir

    Code:
    Sub Nieuw_Tentamen()
    
        Dim path, FileName As String
        Dim i As Integer
        path = ThisWorkbook.path & "/toetsen/"
        
        FileName = Dir(path & "*.docx")
        ............................................ etc.
    Result after the statement: "path =" is: https://connect.fontys.nl/instituten...abase/toetsen/
    in "toetsen" I have a few docx-files.

    As soon as it reaches FileName = .... an error occurs, it's in Dutch, but translated it says: illegal filename or filenumber.

  7. #7

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    7

    Re: vba excel dir

    Code:
    Sub Nieuw_Tentamen()
    
        Dim path, FileName As String
        Dim i As Integer
        path = ThisWorkbook.path & "/toetsen/"
        
        FileName = Dir(path & "*.docx")
        ............................................ etc.
    Result after the statement: "path =" is: https://connect.fontys.nl/instituten...abase/toetsen/
    in "toetsen" I have a few docx-files.

    As soon as it reaches FileName = .... an error occurs, it's in Dutch, but translated it says: illegal filename or filenumber.

  8. #8
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,247

    Re: vba excel dir

    Aha, it's not a network location, it's a website address.

    The Dir() method only works on network shares and local drives, not on internet locations.
    You have to use different methods to get the content of a website.
    Also if you do have to complete URL I think you first have to download the file, because you can't open it directly with Excel from an URL.

  9. #9

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    7

    Re: vba excel dir

    OK, Arnout,

    Thank you. I don't want to open the file, I just need the filename. I want Excel te create a list of the docx's that are present, that's all.
    But as I understand it correctly, I don't think that's possible?

  10. #10
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,247

    Re: vba excel dir

    What is shown if you open the URL in a webbrowser?
    Does your server support FTP or Webdav?

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: vba excel dir

    you can try using the internet APIs
    search on internetopen
    using these APIs is probably different in 64 bit to 32 bit
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    7

    Re: vba excel dir

    Quote Originally Posted by westconn1 View Post
    you can try using the internet APIs
    search on internetopen
    using these APIs is probably different in 64 bit to 32 bit
    I will mark this solved as soon as it is.
    Thank you for your reply.

  13. #13

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    7

    Re: vba excel dir

    Quote Originally Posted by Mart van Hulten View Post
    I will mark this solved as soon as it is.
    Thank you for your reply.
    You may see it as solved. It seems to be a problem of this specific network.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width