Import automation and copy to a temp table

[pbaldy]

If this is in a standard module, it can all be in the same one. I didn't know if you had this in a form module.

[Alonza Malcom]

Like this? Getting this error when I run the make- table query...


Compile error:

Only comments may appear after End sub, End function, or End property


Option Compare Database
Option Explicit
Public strfile As String
Function DoImportandAppend()
Public Function GetFileName() As String
GetFileName = strfile
End Function
Dim strPathFile As String
Dim strfile As String
Dim strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in CSV worksheet
' has field names
blnHasFieldNames = False
' Replace C:\Users\u8201332\Desktop\New folder with the new path to the folder that
' contains the CSV files
strPath = "C:\Users\u8201332\Desktop\HRC folder\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strfile = Dir(strPath & "*.csv")

Do While Len(strfile) > 0
strTable = Left(strfile, Len(strfile) - 4)
strPathFile = strPath & strfile
DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames

' Copy imported file to a tempfile, requery to change format and append new data to a
' master HRC table


DoCmd.SetWarnings False
DoCmd.CopyObject "", "tblhrc", acTable, strTable
DoCmd.OpenQuery "qryHRCflatfile", acViewNormal, acAdd
DoCmd.OpenQuery "qryappHRCdata", acViewNormal, acAdd

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported

' Kill strTable
strfile = Dir()
Loop

End Function

[pbaldy]

You put the new function inside the existing one. They have to be separate:

Public Function Blah
...
End Function

Public Function Whatever
...
End Function

[Alonza Malcom]

Did that...the Sourcefile field is coming in blank...any ideas?

Option Compare Database
Public strfile As String
Public Function GetfileName() As String
GetfileName = strfile
End Function

[pbaldy]

You mean in the newly created table? Did you remove

Dim strfile As String

from the existing function (remember I said move it)? This would also help:

http://www.baldyweb.com/OptionExplicit.htm

You've declared it twice, and the function is now pulling the wrong one.

[Alonza Malcom]

You're absolutely right!!! Works beautifully...thank you!!!

[pbaldy]

Happy to help!

[Alonza Malcom]

Good Morning Paul!

I have a new request for this procedure (u gotta love it)! Can I include in the import automation a function that un-zips a folder that the .csv files are received in and include the entire path in the imported filename instead of just the filename itself? Thanks!

[pbaldy]

You can unzip; I found this when I first needed it:

http://www.webr2.com/how-do-i-extrac...ugh-ms-access/

[Alonza Malcom]

OK, but I need to include the full path of the file that i'm inporting as the filename...ex: Include "C:\Users\u8201332\Desktop\HRC folder\" into the filename upon import? Thanks

strPath = "C:\Users\u8201332\Desktop\HRC folder\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strfile = Dir(strPath & "*.csv")