在文件夹中的所有Access文件中运行Access VBA(Run Access VBA in all Access files in folder)

在文件夹中的所有Access文件中运行Access VBA(Run Access VBA in all Access files in folder)

我一直试图找到一种方法来在我在文件夹中的所有文件上运行以下VBA代码，而无需手动打开每个文件。

这是我现在的代码（将所需的表导出为分隔的txt文件，包括列名）：

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

在研究问题时，我发现了一个在excel中工作的过程 ，但我不确定如何在访问中改变变量，尤其是工作簿引用。

谢谢！

编辑 - 有效的代码：

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

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

最满意答案

考虑使用FileSystemObject 。

为此，您必须添加Microsoft Scripting Runtime库的reference 。 （转到VBA编辑器中的工具>引用...）

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

考虑到您在Access中使用VBA，请使用Application.OpenAccessProject或Application.OpenCurrentDatabase方法在Access中打开文件。

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.