将文件夹中的所有文件（所有 Excel 文件）导入到 Access 数据库中的单个表中

发布于 2024-11-30 22:03:10 字数 1212 浏览 1 评论 0原文

我正在尝试将一堆带有 A:H 列的 excel 2003 文件导入到 access 2003 数据库中的表中，并且它们位于相同的标题等下。这是我正在制作的access模块。我使用文件搜索来查找以 Format 开头的每个文件（它们都这样做）以获取文件夹路径中的所有文件。有没有更有效的方法来做到这一点？以某种方式选择文件夹中的所有文件？并将每个导入到访问中的同一个表中？ DoCmd.TransferSpreadsheet 似乎单独获取每个文件路径，因此我不确定如何获取文件夹中的每个文件名以将其导入。

如果您可以解决这个问题，或者您有任何关于更好的方法或任何建议，那就太好了！提前谢谢你=）

我现在有这个：

Sub Import()

Dim db As Database
Set db = CurrentDb

Dim appendtbl As Recordset
Set appendtbl = db.OpenRecordset("sampletbl", dbOpenDynaset)

 Dim FilePathString As String
Dim folderString As String

folderString = "C:blahblahblah"
Dim lngFileNumber As Long
With Application.FileSearch
    .NewSearch
    .LookIn = folderString
    .FileType = xls
    .Filename = "Format"
    If .Execute > 0 Then

    For lngFileNumber = 1 To .FoundFiles.Count

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "sampletbl",       FilePathString, True, "A:H"

    Next lngFileNumber

    End If
    End With

    End Sub

在我尝试编写代码后我做了一些谷歌搜索，并找到了一些更有效的外观方法，例如：

strFileName = Dir("somepath\*.XLS")
Do Until strFileName = ""
'import from file "somepath\" & strFileName
strFileName = Dir()
Loop

我将尝试一下，看看它是如何进行的

原文

I am trying to import a bunch of excel 2003 files all with A:H columns and they are under the same headings etc. into a table in access 2003 database. This is a module in access im making. Im using a file search to look for every file that begins with Format (which they all do ) to get at all the files in the folder path. is there a more efficient way to do this? somehow select all files in a folder? and import each to the same table in access?
The DoCmd.TransferSpreadsheet seeems to take each file path individually so I'm not sure how to get each file name in the folder to import it.

If you can solve this problem, or if you have any reccommendations as to a better way of doing this or anything that would be great! thank you in advance =)

I have this at the moment:

Sub Import()

Dim db As Database
Set db = CurrentDb

Dim appendtbl As Recordset
Set appendtbl = db.OpenRecordset("sampletbl", dbOpenDynaset)

 Dim FilePathString As String
Dim folderString As String

folderString = "C:blahblahblah"
Dim lngFileNumber As Long
With Application.FileSearch
    .NewSearch
    .LookIn = folderString
    .FileType = xls
    .Filename = "Format"
    If .Execute > 0 Then

    For lngFileNumber = 1 To .FoundFiles.Count

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "sampletbl",       FilePathString, True, "A:H"

    Next lngFileNumber

    End If
    End With

    End Sub

I did some googling after i tried writing the code and foudn some much more efficient lookign ways such as :

strFileName = Dir("somepath\*.XLS")
Do Until strFileName = ""
'import from file "somepath\" & strFileName
strFileName = Dir()
Loop

I'm gonna try it out and see how it goes

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

二智少女 2024-12-07 22:03:10

您可以使用 FileSystemObject 来做到这一点。

Dim oFs As New FileSystemObject
Dim oFolder As Folder
Dim oFile As File
If oFs.FolderExists(FullPath) Then
    Set oFolder = oFs.GetFolder(FullPath)

    For Each oFile In oFolder.Files

    //oFile contain the file information

有关 FSO 的详细信息，请查看 MSDN 文档： http://msdn.microsoft.com/en-us/library/6tkce7xa%28v=VS.85%29.aspx

You can do that using the FileSystemObject.

Dim oFs As New FileSystemObject
Dim oFolder As Folder
Dim oFile As File
If oFs.FolderExists(FullPath) Then
    Set oFolder = oFs.GetFolder(FullPath)

    For Each oFile In oFolder.Files

    //oFile contain the file information

For more information on the FSO check out the MSDN docs: http://msdn.microsoft.com/en-us/library/6tkce7xa%28v=VS.85%29.aspx

回复收藏 0 原文

~没有更多了~