VBA - 将Excel文件导入Access表 [英] VBA - Import Excel files into Access Table

问题描述

我有以下代码在一个访问表中导入Excel工作表

代码工作正常但是有一个位于测试文件夹下方的一系列子文件夹。

非常感谢任何帮助

Anthony

公共函数Import_Multi_Excel_Files（）
 
 Dim InputFile As String 
 Dim InputPath As String 
 
 InputPath =" \\server\dfsroot $ \Change \\\\ Impacts \ testing" 
 InputFile = Dir（InputPath&" * .xlsx"）
 
 Do InputFile<> "" 
 
 DoCmd.TransferSpreadsheet acImport ,," tests" &安培;格式（Now（），"ddmmyy"），InputPath& InputFile，True，"行动计划！" 
 InputFile = Dir 
 Loop 
 
结束函数

解决方案

你好Anthony，

听起来你可能需要一个递归函数。此外，如果您要遍历子文件夹，最好使用文件系统对象而不是Dir（）命令。

只是想一想......

Hi,

I have the following code that imports Excel sheets in one Access Table

Code works fine however there are a series of sub-folders that sit beneath the folder testing.

Any help is much appreciated

Anthony

Public Function Import_Multi_Excel_Files()

Dim InputFile As String
Dim InputPath As String

InputPath = "\\server\dfsroot$\Change\3\Impacts\testing"
InputFile = Dir(InputPath & "*.xlsx")

Do While InputFile <> ""

DoCmd.TransferSpreadsheet acImport, , "tests" & Format(Now(), "ddmmyy"), InputPath & InputFile, True, "Action Plan!" 
InputFile = Dir
Loop

End Function

解决方案

Hi Anthony,

Sounds like you might need a recursive function. Also, if you're going to loop through subfolders, you might be better off using a File System Object rather than the Dir() command.

Just a thought...

这篇关于VBA - 将Excel文件导入Access表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！