0

I have an ADB that I am looking to automatically loop through Windows Explorer, import the file to a weekly report table (Wkly_Rprt), perform formatting, and append it to a weekly summary table (Wkly_S_Rprt), and then delete each file from Windows Explorer? File names are in the same format with the date changed at the end. If so, what would that look like as I have looked through numerous other examples without much luck.

Thanks so much for your help!

Dr. Acula
  • 1
  • 2
  • Maybe this topic helps you along your way: http://stackoverflow.com/questions/20245053/running-microsoft-access-as-a-scheduled-task – Rene Jul 20 '16 at 20:36
  • Forget looping through a folder, just loop through `TblImportMenu`. How is this table populated? Which of these files in table do you need imported? Right now it takes just the first. – Parfait Jul 21 '16 at 00:01
  • As for files, anytime a file is added to the Windows Explorer folder, that file is to be imported. Whether that's 2 or 50 files simultaneously. I will take a look at how the TblImportMenu works. Where would I add this, before the DoCmd or after? – Dr. Acula Jul 21 '16 at 20:30
  • Thanks Rene, I will review that link and see how that fits! – Dr. Acula Jul 21 '16 at 20:34
  • Thanks Parfait, I did see that command in one area, but just wasn't sure where to actually place it. – Dr. Acula Jul 21 '16 at 20:34

1 Answers1

0

Use Scripting.FileSystemObject to iterate through file system folders and files. Here is a sample of code I use for importing files from a specific folder:

Private sub ImportCSVs(TempImportFolder as String)

    On Error Goto ImportCSVs_Err
    Dim objFS As Object, objFolder As Object, objFiles As Object

    Set objFS = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFS.GetFolder((TempImportFolder)) 
    Set objFiles = objFolder.Files
    For Each file In objFiles
        DoCmd.TransferText acImportDelim, "[import_spec]", "Wkly_Rprt",  TempImportFolder & "\" & file.Name, False 
        'Remove old data ....   
        'Append new data ....
        'Delete file:
        objFs.DeleteFile TempImportFolder & "\" & file.Name
NextFile:
    Next
ImportCSVs_Exit:
    Exit Sub
ImportCSVs_Err:
    'Handle Errors occurred do to file structure, and Resume NextFile

End Sub

You can scan a folder for sub folders, and run this code on each sub-folder too, sample code at this reference

marlan
  • 1,485
  • 1
  • 12
  • 18
  • Wow marlan, this looks great! From the look of this, I would replace pretty much all of mine less the Remove and Append pieces where you have those commented out. Am I correct in this assumption? – Dr. Acula Jul 21 '16 at 20:33
  • Yup, that's the way I do it. If a file is not in the correct format, that is: not csv, or is csv if a different format, an error will occur. Just resume with the next file... **Please note:** *remove old data* should correlate with the data you are importing, make sure you don't remove data just imported in the previous iteration... ;) – marlan Jul 24 '16 at 07:48
  • Hi marlan, I looked at the bit of code and how it worked in my situation. Ran it, but it didn't like the On Current Event Procedure as a button - Procedure declaration does not match description of event. I believe it is due to OnOpen because an event name is expected to run when the event occurs. Any suggestions? And probably a dumb question for this, will this prompt to open a folder or am I to place the source of the folder within here such as in "TempImportFolder"? Thanks so much, – Dr. Acula Jul 25 '16 at 12:56
  • **As for the event**: you chose the event to run the code according to your system plan. In my case, I had it in a Button `OnClick` event, when the user had the need to import new data. **As for the the folder**: this procedure receives a folder as a parameter, the calling procedure must supply one when calling. The calling procedure can receive a folder name from Form field, dialog box ([link to API function I use](http://www.ammara.com/access_image_faq/browse_for_folder_dialog.html)), DB field, hard code etc... I have the dialog box save to a textbox, linked to a db field... – marlan Jul 26 '16 at 08:33