VBA 将 Excel 电子表格链接到 Access
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10703621/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA to link Excel spreadsheets to Access
提问by user1410368
I am creating a code in VBA in Access 2010 to link excel sheets and put them into tables in access. I keep getting an invalid outside of procedure at the strFile = Dir(StrPath &"*.xls")It keeps telling the the strPath is invalid outside procedure
我正在 Access 2010 中的 VBA 中创建一个代码来链接 Excel 工作表并将它们放入访问表中。我在程序外不断收到无效信息strFile = Dir(StrPath &"*.xls")它不断告诉strPath is invalid outside procedure
Please help.
请帮忙。
Option Compare Database
Option Explicit
'code will link to excel and pull site survey files into access tables
'Setting the path for the directory
Const strPath As String = "C:\Users\cparson\Documents\Survey_Eqpm\SiteSurveyData.xlsx"
'FileName
Dim strFile As String
'Array
Dim strFileList() As String
'File Number
Dim intFile As Integer
'Looping through the folder and building the file list
strFile = Dir(strPath & "*.xls")
While strFile <> ""
'adding files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'checking to see if files where found
If intFile = 0 Then
MsgBox "No Files Found"
Exit Sub
End If
'going through the files and linking them to access
For intFile = 1 To UBound(strFileList)
DoCmd.TransferSpreadsheet acLink, , _
strFileList(intFile), strPath & strFileList(intFile), True, "A5:J17"
Next
MsgBox UBound(strFileList) & "Files were linked"
End Sub
回答by Siddharth Rout
You have an End Subbut no procedure name?
你有一个End Sub但没有过程名称？
Option Compare Database
Option Explicit
Const strPath As String = "C:\Users\cparson\Documents\Survey_Eqpm\SiteSurveyData.xlsx"
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Sub Sample() '<~~ You are missing this...
strFile = Dir(strPath & "*.xls")
'~~> Rest of your code
End Sub
回答by jones-chris
I know this is an old question, but I came across it in a google search and realized that you already have the .xlsxextension in the strPathvariable, but you add it to the string variable, strFile, also.
我知道这是一个老问题，但我在谷歌搜索中遇到了它，并意识到您已经.xlsx在strPath变量中添加了扩展名，但您也将其添加到字符串变量中strFile。
Const strPath As String = "C:\Users\cparson\Documents\Survey_Eqpm\SiteSurveyData.xlsx"
strFile = Dir(strPath & "*.xls")
I might be wrong, but just wanted to point it out.
我可能错了，但只是想指出它。
回答by Bruno Leite
You can try too ADO, it's a easy way in my opnion
你也可以试试 ADO，我认为这是一个简单的方法
YourConnObj.execute "SELECT * INTO YourTableName from [Excel 14.0;DATABASE=c:\temp\data copy.xlsx].[Sheet1]"
