Import text file using Wildcard ( *.csv )

[gbs01]

I have the follwoing code behind my command button:
----------------------------------
Function Appaoo()
On Error GoTo Appaoo_Err

DoCmd.OpenQuery "001qryDelOldRecords", acNormal, acEdit
DoCmd.TransferText acImportDelim, "14&16IN Import Specification", "14&16IN0205Template", "H:\Billing\Switch CDRs\14&16IN" & "*.csv", False, ""
DoCmd.OpenQuery "qryUpdateInTableOnlySS7to2", acNormal, acEdit
DoCmd.OpenQuery "qryUpdateOutTableOnlySS7to1", acNormal, acEdit
DoCmd.OpenQuery "UpdQryCopyCicOutToCicInIfZero", acNormal, acEdit
DoCmd.OpenQuery "UpdQryCopy9915toCicOutIf0", acNormal, acEdit

Beep
MsgBox "IMPORT COMPLETE ! All Files were imported correctly!", vbOKOnly, "FILES IMPORTED INTO DATABASE"

Appaoo_Exit:
Exit Function

Appaoo_Err:
MsgBox Error$
Resume Appaoo_Exit
------------------------------------
How do I get it to import my file H:\Billing\Switch CDRs\14&16IN0205.csv when the 0205 part changes each month?

I tried my code at the top but it says: You cannot import this file.

Thanks in advance for your help!
jlig

 

[lupins46]

Dim mthyr as string
mthyr = format(date(),"mmyy")

DoCmd.TransferText acImportDelim, "14&16IN Import Specification", "14&16IN" & mthyr & "Template", "H:\Billing\Switch CDRs\14&16IN" & "*.csv", False, ""

 

[gbs01]

This worked great. But to further refine this, How can I alter the mthyr field to allow me to choose the file no matter what the date is?

To make it more compatible, and since the text file always starts with 14&16IN , how do I change it so it only cares if the file starts with 14&16IN?

This is what I tried :
--------------------
Dim mthyr as string
mthyr = "*.csv"
--------------------
but it just appends the * to the filename and doesn't make it a wildcard :

H:\Billing\Switch CDRs\14&16IN*.csv

Thanks again!

 

[lupins46]

I don't believe you can use a wildcard in this statement, you have to import a specific file.

You would need to use a dir() function to return an actual filename based on the wildcard string and then use the result in the transfertext command.

 

[gbs01]

ok, thanks! for the help.
jlig

 

[gbs01]

ps: Could you suggest a sample using the Dir() ?

I saw this code on another post:
=====================================
Function importfiles()

Dim FileName As String
FileName = ""
FileName = Dir("Path to the folder where the files are stored" & "*extension of the files i.e .xls")
If FileName <> "" Then
Do Until FileName = ""
DoCmd.TransferText acImportDelim, "1 Spec", "table1", " Path to the folder where the files are stored " & FileName, False, ""
End If
FileName = Dir()

End Function
=======================================
Comparing it to my original, I'm not sure how to have it search for any files starting with 14&16.

Thanks agian,
jlig

 

[VBslammer]

Something like this:

Sub importfiles()
  Dim strFound As String
  Dim strSearch As String
  
  strSearch = "H:\Billing\Switch CDRs\14&16IN*.csv"
  strFound = Dir(strSearch)
  
  Do Until Len(strFound) = 0
    DoCmd.TransferText acImportDelim,"14&16IN Import Specification","14&16IN0205Template",strFound,False
    strFound = Dir()
  Loop
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[gbs01]

Thanks for sharing. Works great!
jlig