Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Question Using wildcards in VBA

    Is there any way to get access to use wildcards in vba code?

    this is a sample of the code That I have
    Private Sub cmdImport_Click()
    On Error Resume Next
    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, 8, "nonsts", "c:\WorkOrders\nonsts\nonsts.xls", True, ""


    MsgBox "Import complete.", vbOKOnly, "Import Complete"
    Me.Requery

    I need the line where the file path is stated to say
    DoCmd.TransferSpreadsheet acImport, 8, "nonsts", "c:\WorkOrders\nonsts\non*.xls", True, ""

    Im not haveing success with this, is it posable do do?
    Regards Jim

  2. #2
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    I suggest you first find all the finles you need (non*.xls) and put them in an array, and then do the operation on each file (since you can't select more than one in that case)

    Take a look at this code that will return you ALL the file, using WildCards, of a directory

    Code:
    Private Sub dirrr()
        
        Dim strReturn As String
        Dim strAcc As String
        
        'The first Dir call is used to start the searching in a directory
        
        'strReturn = Dir("c:\WorkOrders\nonsts\non*.xls", vbNormal)
        strReturn = Dir("c:\*.*", vbNormal)
        
        
        'When dir return "", that is because the search is finished for
        'that directory
        While strReturn <> ""
            strAcc = strAcc & strReturn & vbCrLf
            'MsgBox strReturn
            
            'Here, we call Dir function WITHOUT parameters to CONTINUE the
            'amorced search
            'In that case, I use a single string, but it should be interesting
            'to use an array of string to keep all name
            strReturn = Dir
        Wend
        
        MsgBox strAcc, , "File List"
            
    End Sub
    JefB - hope it helps

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Thats an awsome start, but how do i get access to process the individual file as it finds it?
    I know that i need something like a SQL case statment, but im not that familure with that in access. Im looking into it now but a heads up would be great.
    Much thanx
    Jim

  4. #4
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    You'll have to put you doCmd in the loop OR to keep all the string in an array (dim strArrayOfString(10) as string), that code show how to do it in the loop, but you may want to use a function (by passing the filename), and do all your stuff here like error handler:

    Code:
    Private Sub dirrr()
        
        Dim strReturn As String
        Dim strAcc As String
        
        'The first Dir call is used to start the searching in a directory
        
        'strReturn = Dir("c:\WorkOrders\nonsts\non*.xls", vbNormal)
        strReturn = Dir("c:\*.*", vbNormal)
        
        
        'When dir return "", that is because the search is finished for
        'that directory
        While strReturn <> ""
            strAcc = strAcc & strReturn & vbCrLf
            'MsgBox strReturn
            DoCmd.TransferSpreadsheet acImport, 8, "nonsts", "c:\" & strReturn, True, ""
    
            'Here, we call Dir function WITHOUT parameters to CONTINUE the
            'amorced search
            'In that case, I use a single string, but it should be interesting
            'to use an array of string to keep all name
            strReturn = Dir
        Wend
        
        MsgBox strAcc, , "File List"
            
    End Sub
    JefB - hope it helps

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Thats what I thought i would have to do but the problem I have is when I run that it tells me that Access cant find C:\filename. It doesnt keep the full file path. Im trying to figure out that now.
    Thanks for your help
    Regards Jim



    Originally posted by JefB
    You'll have to put you doCmd in the loop OR to keep all the string in an array (dim strArrayOfString(10) as string), that code show how to do it in the loop, but you may want to use a function (by passing the filename), and do all your stuff here like error handler:

    Code:
    Private Sub dirrr()
        
        Dim strReturn As String
        Dim strAcc As String
        
        'The first Dir call is used to start the searching in a directory
        
        'strReturn = Dir("c:\WorkOrders\nonsts\non*.xls", vbNormal)
        strReturn = Dir("c:\*.*", vbNormal)
        
        
        'When dir return "", that is because the search is finished for
        'that directory
        While strReturn <> ""
            strAcc = strAcc & strReturn & vbCrLf
            'MsgBox strReturn
            DoCmd.TransferSpreadsheet acImport, 8, "nonsts", "c:\" & strReturn, True, ""
    
            'Here, we call Dir function WITHOUT parameters to CONTINUE the
            'amorced search
            'In that case, I use a single string, but it should be interesting
            'to use an array of string to keep all name
            strReturn = Dir
        Wend
        
        MsgBox strAcc, , "File List"
            
    End Sub
    JefB - hope it helps

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Man if it was a dog it would have bitten me!!!!!!
    I figured it out.
    Thanx for all of your help you definatly got me in the correct direction
    Regards Jim


    Originally posted by JDionne
    Thats what I thought i would have to do but the problem I have is when I run that it tells me that Access cant find C:\filename. It doesnt keep the full file path. Im trying to figure out that now.
    Thanks for your help
    Regards Jim

  7. #7
    Join Date
    Dec 2002
    Posts
    1
    I am trying to do something similar, can you tell me what you had to do for the file(s) to import? I keep receiving a message that the database engine cannot find the object (but lists the filename that I wanted after using a wildcard). Any help would be appreciated.

    Thanks

Posting Permissions

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