Dir function

[Guest]

I am using the dir function and the transfer text function to import several files with the same 7 first letters of the file name and the same extension

When it is ran, it gives a run time error that the file cannot be found. I do not understand...

Thanks!!

 

[Dirk Goldgar]

I am using the dir function and the transfer text function to import
several files with the same 7 first letters of the file name and the
same extension.

When it is ran, it gives a run time error that the file cannot be
found. I do not understand....

Thanks!!

You must post your code if you want someone to debug it.

 

[Guest]

Here is the original code

Function import_idcard_and_coupon_file(

Dim ImpSpecID As Strin
Dim ImpTblID As Strin
Dim ImpFileID As Strin

Dim ImpSpecCoup As Strin
Dim ImpTblCoup As Strin
Dim ImpFileCoup As Strin

ImpSpecID = "Import ID Card Data
ImpTblID = "ID Card Data
ImpSpecCoup = "Import Coupon Book Data
ImpTblCoup = "Coupon Book Data

DoCmd.RunSQL "Delete * from [ID Card Data]
DoCmd.RunSQL "Delete * from [Coupon Book Data]

filnameID = Dir("\\david\c\documents and settings\dave\desktop\ahp_idcards*.txt"
Do Until filnameID = "
DoCmd.TransferText acImportDelim, ImpSpecID, ImpTblID, filnameID, Tru

filnameID = Dir(
Loo

filnameCoup = Dir("\\david\c\documents and settings\dave\desktop\ahp_coupon*.txt"
Do Until filnameCoup = "
DoCmd.TransferText acImportDelim, ImpSpecCoup, ImpTblCoup, filnameCoup, Tru

filnameCoup = Dir(
Loo

DoCmd.OpenQuery "Coupon Book Query

DoCmd.RunMacro "Update_TMG_COUPON_File

DoCmd.OpenReport "ID Cards (8-up)", acViewPreview, "", "", acNorma

DoCmd.OpenReport "Coupon Book", acViewPreview, "", "", acNorma

DoCmd.OpenQuery "Count ID Cards", acViewNormal, acEdi

DoCmd.OpenQuery "Count Coupon Books", acViewNormal, acEdi

DoCmd.OpenQuery "ID Card Data Archive Qry", acViewNormal, acEdi

DoCmd.OpenReport "Coupon Book Archive Qry", acViewNormal, "", "", acWindowNorma

DoCmd.OpenReport "Count of Return Envelopes", acViewPreview, "", "", acNorma

End Functio

 

[Dirk Goldgar]

Here is the original code:

Function import_idcard_and_coupon_file()

Dim ImpSpecID As String
Dim ImpTblID As String
Dim ImpFileID As String

Dim ImpSpecCoup As String
Dim ImpTblCoup As String
Dim ImpFileCoup As String

ImpSpecID = "Import ID Card Data"
ImpTblID = "ID Card Data"
ImpSpecCoup = "Import Coupon Book Data"
ImpTblCoup = "Coupon Book Data"

DoCmd.RunSQL "Delete * from [ID Card Data]"
DoCmd.RunSQL "Delete * from [Coupon Book Data]"

filnameID = Dir("\\david\c\documents and
settings\dave\desktop\ahp_idcards*.txt") Do Until filnameID = ""
DoCmd.TransferText acImportDelim, ImpSpecID, ImpTblID, filnameID, True

filnameID = Dir()
Loop

filnameCoup = Dir("\\david\c\documents and
settings\dave\desktop\ahp_coupon*.txt") Do Until filnameCoup = ""
DoCmd.TransferText acImportDelim, ImpSpecCoup, ImpTblCoup,
filnameCoup, True

filnameCoup = Dir()
Loop

[remainder snipped]

If you step through the code, I think you'll find that the filnameID and
filnameCoup variables -- both apparently undeclared, by the way --
receive only the name and extension of the file from the Dir() function.
In the call to TransferText, you'll need to prefix that name with the
path to the folder containing the file. Like this:

Function import_idcard_and_coupon_file()

Dim ImpSpecID As String
Dim ImpTblID As String
Dim ImpFileID As String

Dim ImpSpecCoup As String
Dim ImpTblCoup As String
Dim ImpFileCoup As String

Dim ImpFolder As String

ImpSpecID = "Import ID Card Data"
ImpTblID = "ID Card Data"
ImpSpecCoup = "Import Coupon Book Data"
ImpTblCoup = "Coupon Book Data"

ImpFolder = "\\david\c\documents and settings\dave\desktop\"

DoCmd.RunSQL "Delete * from [ID Card Data]"
DoCmd.RunSQL "Delete * from [Coupon Book Data]"

filnameID = Dir(ImpFolder & "ahp_idcards*.txt")

Do Until filnameID = ""

DoCmd.TransferText acImportDelim, ImpSpecID, ImpTblID, _
ImpFolder & filnameID, True

filnameID = Dir()
Loop

filnameCoup = Dir(ImpFolder & "ahp_coupon*.txt")

Do Until filnameCoup = ""

DoCmd.TransferText acImportDelim, ImpSpecCoup, ImpTblCoup, _
ImpFolder & filnameCoup, True