导入Excel文件到Access而循环 [英] Import Excel files into Access whilst Looping
问题描述
我很舒服,使用VBA导入Excel表格导入Access,并遍历给定文件夹带回在那里的一切。不过,我想遍历一个文件夹,只导入选择的文件。有人可以帮忙吗?每一个文件名为 REPORT1
等,并运行到 REPORT67
。我只想挑 1-47
。
code以下工作正常,但这只是复制一切从指定的位置。
子样品2()
常量cstrFolder的String =F:\ TCB_HR_KPI \数据视图\
昏暗strFile作为字符串
昏暗我只要
strFile =目录(cstrFolder和放大器;的* .xls)
如果len(strFile)= 0则
MSGBOX没有找到文件
其他
做,而莱恩(strFile)> 0
Debug.Print cstrFolder和放大器; strFile
DoCmd.TransferS preadsheet acImport,ACS preadsheetTypeExcel9,_
strFile,cstrFolder和放大器; strFile,真
I = I + 1
strFile =目录()
循环
MSGBOX I和文件导入
结束如果
结束小组
我会做: DIR $() DoWhile缬氨酸(中间(文件名,7,2))≤; 48 导入文件 DIR $() 循环
I am comfortable with using VBA to import excel sheets into access, and to loop through a given folder to bring back everything in there. However, I want to loop through a folder and only import a selection of the files. Can someone help? Each file is called REPORT1
etc and runs to REPORT67
. I only want to pick 1-47
.
Code below works fine, but this just copies everything in from the specified location.
Sub Sample2()
Const cstrFolder As String = "F:\TCB_HR_KPI\Data View\"
Dim strFile As String
Dim i As Long
strFile = Dir(cstrFolder & "*.xls")
If Len(strFile) = 0 Then
MsgBox "No Files Found"
Else
Do While Len(strFile) > 0
Debug.Print cstrFolder & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strFile, cstrFolder & strFile, True
i = i + 1
strFile = Dir()
Loop
MsgBox i & " Files are imported"
End If
End Sub
I would do: Dir$() DoWhile Val(Mid(filename, 7, 2))<48 Import the file Dir$() Loop
这篇关于导入Excel文件到Access而循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!