vba 如何使用vb/ssis获取每张excel的名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18533142/
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
How to get the name of each sheets of excel using vb/ssis
提问by user1254579
In the ssis Excel Source Editor,the name of the excel sheet is available. is there any way to get that name on to a variable? or is there any way to get the name of each and every sheets in the xlsx file? using vb or any other ssis method?
在 ssis Excel 源代码编辑器中，可以使用 Excel 工作表的名称。有什么方法可以将该名称添加到变量中吗？或者有什么方法可以获取 xlsx 文件中每个工作表的名称？使用 vb 或任何其他 ssis 方法？
回答by Noel Widmer
Try this:
尝试这个：
For i=0 to ActiveWorkbook.Sheets.Count-1
msgbox(WorkSheets(i).Name)
Next
I don't know how you need the result. You may want to store it in an array.
我不知道你需要怎样的结果。您可能希望将其存储在数组中。
回答by Brian B.
This will work (insert code into a code module):
这将起作用（将代码插入代码模块）：
Sub IterateAllWorksheetNames()
dim wksht as Worksheet, FriendlyNameStr as String, CodeNameStr As String
For Each wksht in ThisWorkbook.Worksheets
wkshtNameStr = wksht.Name '// This is your worksheet "friendly" name
'// (..the name that is on the worksheet tab).
CodeNameStr = wksht.CodeName '// This is the CodeName of the worksheet
'// (..the actual object name in the collection)
Next wksht
'// NOTE: By Default the CodeName and FriendlyName are by default the same (sheet1,
'// sheet2..) so don't get confused. You're probably looking for just "wksht.Name"
End Sub
UPDATE: Sorry, I didnt see that you wanted it in a table.. This will add a table to Sheet2. Make sure your workbook has a "Sheet2", or just insert any EMPTY worksheet name into the Sheets() method..
更新：抱歉，我没有看到您想要在表格中使用它。这会将表格添加到 Sheet2。确保您的工作簿有一个“Sheet2”，或者只是将任何 EMPTY 工作表名称插入到 Sheets() 方法中。
Dim wksht As Worksheet: Set wksht = Sheets("Sheet2")
With wksht.ListObjects.Add(xlSrcRange)
.Name = "MyWorksheetNames"
.ListColumns(1).Name = "FriendlyNames"
.ListColumns.Add(2).Name = "CodeNames"
For Each x In ThisWorkbook.Worksheets
With .ListRows.Add
.Range(1, 1).Value = x.Name
.Range(1, 2).Value = x.CodeName
End With
Next x
End With
回答by Tanmay Nehete
Try this in vb.net bind sheet name to Combobox
在 vb.net 中尝试将工作表名称绑定到 Combobox
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load Dim filePath As String = "C:\Book1.xlsx"
Dim connString As String = String.Empty
If filePath.EndsWith(".xlsx") Then '2007 Format
connString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", filePath)
Else '2003 Format
connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", filePath)
End If 'Get the Sheets in Excel WorkBook
Dim connExcel As New OleDbConnection(connString)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
cmdExcel.Connection = connExcel
connExcel.Open()
cmbSheets.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) cmbSheets.DisplayMember = "TABLE_NAME" cmbSheets.ValueMember = "TABLE_NAME" connExcel.Close()
End Sub
