vba 允许 Access 用户选择 Excel 工作表进行链接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20079882/
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
Allow Access user to select Excel worksheet for linking
提问by Kevin Malloy
So I am using VBA in Access to create linked tables between Excel and Access. Simple enough and as some online resources guided me I decided to utilize the TransferSpreadsheetcommand. So I ran some code to test out if I had the syntax correct and got this
所以我在 Access 中使用 VBA 在 Excel 和 Access 之间创建链接表。足够简单，并且在一些在线资源的指导下，我决定使用TransferSpreadsheet命令。所以我运行了一些代码来测试我的语法是否正确并得到了这个
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, _
"Link Name", "File Path", True, "Sheet Name!"
So that worked perfectly, but I wanted to automate it so someone who doesn't understand how to code can use the function. So for the file path I set up a file dialog box to come up so the user can select the excel file. Again worked great.
所以这很完美，但我想将它自动化，以便不了解如何编码的人可以使用该功能。所以对于文件路径我设置了一个文件对话框来让用户选择excel文件。再次工作得很好。
So now to the point, I want to create a dialog box for users to select the excel sheet to link as well. So essentially the user would select the excel file first and then select from a drop down box the sheet they want to link. Is this possible? If so how would I go about doing it. Here is my code so far:
所以现在，我想创建一个对话框，供用户选择要链接的 Excel 表。所以基本上用户会先选择 excel 文件，然后从下拉框中选择他们想要链接的工作表。这可能吗？如果是这样，我将如何去做。到目前为止，这是我的代码：
Public Sub linksheet()
Dim fd As FileDialog
Dim strpath As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select Routing File"
'get the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
If FileChosen <> -1 Then
Else
strpath = fd.SelectedItems(1)
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, _
"Test Link", strpath, True, "Current!"
End If
End Sub
To add to this further I was attempting to utilize this code I found to get the names but I'm unsure how to store them as variables to use.
为了进一步补充这一点，我试图利用我发现的这段代码来获取名称，但我不确定如何将它们存储为要使用的变量。
Public Function WorkSheetNames(strwspath As String) As Boolean
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strarray(25)
Set xlApp = CreateObject("Excel.application")
Set xlBook = xlApp.Workbooks.Open(strwspath, 0, True)
For Each xlSheet In xlBook.Worksheets
Debug.Print xlSheet.Name
Next xlSheet
xlBook.Close False
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Set xlSheet = Nothing
End Function
What the above code does is list out each sheet name into the debug window, I just am finding it difficult to push those values to an array.
上面的代码所做的是在调试窗口中列出每个工作表名称，我只是发现很难将这些值推送到数组中。
采纳答案by HansUp
I don't see why you need to store the sheet names in an array. You could store them as a list in a string variable. Then you could assign that string as the RowSourceproperty of a combo box which allows the user to select one of the available sheets.
我不明白为什么需要将工作表名称存储在数组中。您可以将它们作为列表存储在字符串变量中。然后，您可以将该字符串指定为RowSource组合框的属性，该组合框允许用户选择可用工作表之一。
Dim strSheets As String
' adapt your existing code to use this ...
For Each xlSheet In xlBook.Worksheets
'Debug.Print xlSheet.Name
strSheets = strSheets & ";" & xlSheet.Name
Next xlSheet
' discard leading semi-colon
strSheets = Mid(strSheets, 2)
After you have collected the sheet names, apply them as the combo box source.
收集工作表名称后，将它们用作组合框源。
' ComboName should have Value List as Row Source Type
Me.ComboName.RowSource = strSheets
