1

I have been trying to figure out a way to run the following VBA code on all of the files I have in a folder without having to manually open each file.

This is the code I have right now (exports the desired table as a delimited txt file, including column names):

Private Sub Command4_Click()

    Dim MyObj, MySource As Object, File As Variant, stDocName As String, Counter As Integer

    On Error GoTo Err_Command4_Click
        Dim stDocName As String, Counter As Integer
        Counter = 1
        stDocName = "tblSCTurCount"
        DoCmd.TransferText acExportDelim, "", stDocName, "C:\Users\name\Downloads\cnt\cnt_output.txt", True

    Exit_Command4_Click:
        Exit Sub

    Err_Command4_Click:
        MsgBox Err.Description
        Resume Exit_Command4_Click

End Sub

When researching the problem, I found a process that works in excel, but I'm not sure how to do the variables change in access, especially the workbook references.

Thank you!

EDIT -- Code that worked:

Dim FS As FileSystemObject
Set FS = New FileSystemObject
Dim MyFolder As Folder
Set MyFolder = FS.GetFolder("C:\Users\name\Downloads\cnt\Folder")
Dim MyFile As File
Set appAccess = CreateObject("Access.Application")

For Each MyFile In MyFolder.Files
    appAccess.OpenCurrentDatabase (MyFile.Path)
    appAccess.Visible = True
    NewFileName = MyFile.Path & ".txt"
    appAccess.DoCmd.TransferText acExportDelim, "", "tblScTurCount", NewFileName, True
    appAccess.CloseCurrentDatabase
Next
Erik A
  • 31,639
  • 12
  • 42
  • 67
Chris
  • 13
  • 5

1 Answers1

0

Consider using the FileSystemObject.

For that you will have to add a reference the Microsoft Scripting Runtime library. (Go to tools > references... in the VBA editor)

Sub test()
    Dim FS As FileSystemObject
    Set FS = New FileSystemObject

    Dim MyFolder As Folder
    Set MyFolder = FS.GetFolder("C:\path\of\the\folder")

    Dim MyFile As File
    For Each MyFile In MyFolder.Files
        'do what you want to do with each file

        'to use the file name:
        MyFile.Name

        'I suppose you have to:
        Application.OpenCurrentDatabase MyFile.Path
        '(please verify if this path contains the filename and extension too). 

         'But create a different filename for each txt:
         NewFileName = MyFile.Path + ".txt"

        'Then you do:
        DoCmd.TransferText acExportDelim, "", "tblScTurCount", NewFileName, True

    Next
End Sub

Considering you are using VBA in Access, use the Application.OpenAccessProject or the Application.OpenCurrentDatabase methods to open files in Access.

Daniel Möller
  • 84,878
  • 18
  • 192
  • 214
  • Thanks for the advice. Unfortunately, on `Set MyFolder = FS.GetFolder("C:\path\of\the\folder")` I am getting "Run-Time error '91': Object variable or With block variable not set" For reference, I have added the Microsoft Scripting Runtime library and have changed "C:\path\of\the\folder" to the actual path of my folder. Thank you again! – Chris Jul 06 '15 at 16:08
  • Oh, sorry, I'll update it, forgot to `create` the FS object. – Daniel Möller Jul 06 '15 at 16:11
  • I think it's almost there, I'm just unsure how to use the variable `MyFile` in the `DoCmd.TransferText` statement in the op. Specifically, how do I run that command on the table in MyFile (the tables are all named "tblSCTurCount"). I've also edited the original post to show what my current code looks like – Chris Jul 06 '15 at 16:31
  • Are you using Access or Excel? I don't understand what you are trying to do. What is `DoCmd` ? – Daniel Möller Jul 06 '15 at 17:07
  • `MyFile` is one of the files in the directory. It has many properties you can see with `MyFile.` (type a period after MyFile). It has `Name`, `Path` and other properties you can get. – Daniel Möller Jul 06 '15 at 17:11
  • I'm using Access. I'm writing code to export a specific table (tblScTurCount) from several access databases in a folder into individual .txt files. `DoCmd.TransferText` is the method that creates the .txt export. Thanks again for your help. – Chris Jul 06 '15 at 17:12
  • I suppose you must do that sequence I added. – Daniel Möller Jul 06 '15 at 17:20
  • I believe this is the right track, so thank you again. This time I am getting "Run-time error '7867: You already have the database open." even though it is referencing an unopened database. I am taking an hour break so I'll look into this further when I get back – Chris Jul 06 '15 at 17:35
  • Looking at the links and the `return type` of the open methods (nothing is returned), it seem an access application can only handle **one** database at a time. You can either close the current database or create a `New Application` – Daniel Möller Jul 06 '15 at 18:14
  • Thank you so much. I got everything working and have updated the op with the final code – Chris Jul 06 '15 at 19:59