Hello.
I am using the following code to import two csv files into two seperate tables, the files are distinguished by chemistry or samples, and they are supplied from the laboratory as is, so i am keen to be able to use outlook to save the attachments to a directory if they contain *samples*.csv or *chemistry*.csv (that part works fine).
Anyway i made the import specification and all of that stuff, so that is no problems at all, the code i am using is below:
For Chemistry:
For Samples:
Anyways for some reason it will not import and gives the filename cannot be found (and the actual filename as it found through a dir search, i.e it finds it not me) and won't import when the filename is the confusing filename as supplied by the external lab, however if i rename it to Chemistry for chemistry import, or samples for sample import it works fine.
Does anyone know how i can keep the file format as it would be supplied, so i can save it in the directory as an origional rather than a renamed filename.
Examples of filenames are:
F1012-15.ME104820.Chemistry26.csv
F1012-15.ME104820.Sample26.csv
also, currently i get it to look through I:\ImportCSV for files, does anyone know how i could move it to I:\ImportCSV\Imported\ once it has been imported so it does not duplicate?
Thanks for the help!
I am using the following code to import two csv files into two seperate tables, the files are distinguished by chemistry or samples, and they are supplied from the laboratory as is, so i am keen to be able to use outlook to save the attachments to a directory if they contain *samples*.csv or *chemistry*.csv (that part works fine).
Anyway i made the import specification and all of that stuff, so that is no problems at all, the code i am using is below:
For Chemistry:
Code:
Sub EsdatSamples()
Dim file_name As String
Dim strPath As String
' NOTE: Make certain that CurDir is set to the
' directory of your choice
strPath = "I:\ImportCSV" & "\" & "*Sample*.csv"
strfile = Dir(strPath)
' Loop through the files
Do While strfile > ""
file_name = "I:\ImportCSV" & "\" & strfile
DoCmd.TransferText acImportDelim, _
"ImportEsdatSamples", _
"Esdat Samples", _
file_name, False
' Get the next file in the Directory
strfile = Dir
Loop
End Sub
For Samples:
Code:
Sub EsdatChemistry()
Dim file_name As String
Dim strPath As String
' NOTE: Make certain that CurDir is set to the
' directory of your choice
strPath = "I:\ImportCSV" & "\" & "*.*.Chemistry*.csv"
strfile = Dir(strPath)
' Loop through the files
Do While strfile > ""
file_name = "I:\ImportCSV" & "\" & strfile
DoCmd.TransferText acImportDelim, _
"ImportEsdatChemistry", _
"Esdat Chemistry", _
file_name, True
' Get the next file in the Directory
strfile = Dir
Loop
End Sub
Anyways for some reason it will not import and gives the filename cannot be found (and the actual filename as it found through a dir search, i.e it finds it not me) and won't import when the filename is the confusing filename as supplied by the external lab, however if i rename it to Chemistry for chemistry import, or samples for sample import it works fine.
Does anyone know how i can keep the file format as it would be supplied, so i can save it in the directory as an origional rather than a renamed filename.
Examples of filenames are:
F1012-15.ME104820.Chemistry26.csv
F1012-15.ME104820.Sample26.csv
also, currently i get it to look through I:\ImportCSV for files, does anyone know how i could move it to I:\ImportCSV\Imported\ once it has been imported so it does not duplicate?
Thanks for the help!