I think I have it, but I don't know if this is possible, or quite how to do it. I'm automating the import of 6 files and I want to have as little user intervention as possible. Right now I have the user having to find each file - the program steps them through it.
But I was thinking that it could be automated with the user being prompted to input the Plan number, because the file name begins with the Plan number, has a date & time stamp, then ends with a three digit code that is different for each import file.
Here's what I have - the only thing I don't know what to do is have the code skip the date and time stamp (and underscore characters). The below date & time will be the same for all 6 files. It will always be 17 characters long.
=====================
Dim imp_PLNNUM as String
imp_PLNNUM = Me!Text0 '(which will be the name of the input box on the form. Or however I decide to do this...)
Dim imp_AGD as String
imp_AGD = "AGD" '(this is the last three characters - I would do this for all 6 - AGD, AGN, BIL, COM, DAT, PLN)
On Error Go To Err_Import_Click
DoCmd.TransferText, acImportDelim, "AGD_Import_Spec"_ '(there would be a differenct spec for each file)
"100_tbl_IMPORT_102_PREX5AGD",_ '(name of the table to import to)
"M:\\EBAPPS1\COMMISSION\PREMSUMM\"_ '(the location of the file)
& imp_PLNNUM & "_CCYYMMDD_HHMMSS_" & imp_AGD,_ '(the name of the file)
YES '(Has field names)
=====================
Do you have any clue as to how I could code this to ignore those 17
characters??? My duplication check will be in the next step of the
import, which will append the imported data into the main tables.
Thanks!
Carie
But I was thinking that it could be automated with the user being prompted to input the Plan number, because the file name begins with the Plan number, has a date & time stamp, then ends with a three digit code that is different for each import file.
Here's what I have - the only thing I don't know what to do is have the code skip the date and time stamp (and underscore characters). The below date & time will be the same for all 6 files. It will always be 17 characters long.
=====================
Dim imp_PLNNUM as String
imp_PLNNUM = Me!Text0 '(which will be the name of the input box on the form. Or however I decide to do this...)
Dim imp_AGD as String
imp_AGD = "AGD" '(this is the last three characters - I would do this for all 6 - AGD, AGN, BIL, COM, DAT, PLN)
On Error Go To Err_Import_Click
DoCmd.TransferText, acImportDelim, "AGD_Import_Spec"_ '(there would be a differenct spec for each file)
"100_tbl_IMPORT_102_PREX5AGD",_ '(name of the table to import to)
"M:\\EBAPPS1\COMMISSION\PREMSUMM\"_ '(the location of the file)
& imp_PLNNUM & "_CCYYMMDD_HHMMSS_" & imp_AGD,_ '(the name of the file)
YES '(Has field names)
=====================
Do you have any clue as to how I could code this to ignore those 17
characters??? My duplication check will be in the next step of the
import, which will append the imported data into the main tables.
Thanks!
Carie
