|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
搜一下就出来了,下面是zhaogang1960老师的代码。- Sub 获取工作表名称OpenSchema法()
- Dim cnn As Object, rst As Object, SQL$, Filename$, i&
- Set cnn = CreateObject("ADODB.Connection")
- Set rst = CreateObject("ADODB.Recordset")
- Filename = Application.GetOpenFilename(filefilter:="Excel文件(*.xls),*.xls", Title:="选择Excel工作簿")
- If Filename = "False" Then Exit Sub
- cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Filename
- Set rst = cnn.OpenSchema(20) 'adSchemaTables=20
- i = 1
- Cells(i, 1) = "指定工作簿的工作表名称:"
- i = i + 1
- Do Until rst.EOF
- If rst.Fields("TABLE_TYPE") = "TABLE" Then
- s = Replace(rst("TABLE_NAME").Value, "'", "") '表名含有“1月”等时有多余的单引号
- If Right(s, 1) = "$" Then '排除无效表名
- Cells(i, 1) = s
- i = i + 1
- End If
- End If
- rst.MoveNext
- Loop
- rst.Close
- Set rst = Nothing
- cnn.Close
- Set cnn = Nothing
- End Sub
复制代码 |
|