If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Using wildcards in VBA

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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



Quote:
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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


Quote:
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On