|
openschema(20)直接取表名试试看。
Sub test1()
Dim cnn As Object, rst As Object
Dim strPath As String, str_cnn As String, strSQL As String
Dim i As Long, Rst_schema, m As Integer
Set cnn = CreateObject("adodb.connection")
str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\销售.xls"
cnn.Open str_cnn
Set Rst_schema = cnn.openschema(20)
Do Until Rst_schema.EOF
m = m + 1
If Rst_schema.Fields("TABLE_TYPE").Value = "TABLE" Then shtname = Rst_schema.Fields("TABLE_NAME").Value
Rst_schema.MoveNext
Loop
If m > 1 Then MsgBox "源数据不止一张表格,请检查!": Exit Sub
strSQL = "SELECT 店铺代码,店铺名称,店铺供货等级,年份,商品代码 FROM [" & shtname & "]"
Set rst = cnn.Execute(strSQL)
Cells.ClearContents
For i = 0 To rst.Fields.Count - 1
Sheet1.Cells(1, i + 1) = rst.Fields(i).Name
Next
Sheet1.Range("A2").CopyFromRecordset rst
cnn.Close
Set cnn = Nothing
End Sub
|
评分
-
1
查看全部评分
-
|