Sub Macro1()
'引用Microsoft ActiveX Data Objects 2.x Library
'引用Microsoft AD0 Ext 2.8 for DDL and Security
Dim cnn As New ADODB.Connection
Dim Cat As New ADOX.Catalog
Dim myPath As String
Dim sh As Worksheet
Dim SQL As String
myPath = Replace(ThisWorkbook.FullName, "xls", "mdb")
If Dir(myPath) <> "" Then Kill myPath
Cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myPath
Set Cat = Nothing
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myPath
SQL = "SELECT * INTO " & sh.Name & " FROM [Excel 8.0;Database=" & ThisWorkbook.FullName _
& ";].[Sheet3$" & sh.Range("a1") .CurrentRegion.Address(0, 0) & "]"
cnn.Execute SQL
MsgBox "已经将工作表数据生成新数据表。", vbInformation, "生成新数据表"
cnn.Close
Set cnn = Nothing
End Sub
大神帮我看看:
SQL = "SELECT * INTO " & sh.Name & " FROM [Excel 8.0;Database=" & ThisWorkbook.FullName _
& ";].[Sheet3$" & sh.Range("a1") .CurrentRegion.Address(0, 0) & "]"
cnn.Execute SQL
说这里语法错误,几十人搞来搞去都不明白
把sheet3写入到access
'引用Microsoft ActiveX Data Objects 2.x Library
'引用Microsoft AD0 Ext 2.8 for DDL and Security
Dim cnn As New ADODB.Connection
Dim Cat As New ADOX.Catalog
Dim myPath As String
Dim sh As Worksheet
Dim SQL As String
myPath = Replace(ThisWorkbook.FullName, "xls", "mdb")
If Dir(myPath) <> "" Then Kill myPath
Cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myPath
Set Cat = Nothing
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myPath
SQL = "SELECT * INTO " & sh.Name & " FROM [Excel 8.0;Database=" & ThisWorkbook.FullName _
& ";].[Sheet3$" & sh.Range("a1") .CurrentRegion.Address(0, 0) & "]"
cnn.Execute SQL
MsgBox "已经将工作表数据生成新数据表。", vbInformation, "生成新数据表"
cnn.Close
Set cnn = Nothing
End Sub
大神帮我看看:
SQL = "SELECT * INTO " & sh.Name & " FROM [Excel 8.0;Database=" & ThisWorkbook.FullName _
& ";].[Sheet3$" & sh.Range("a1") .CurrentRegion.Address(0, 0) & "]"
cnn.Execute SQL
说这里语法错误,几十人搞来搞去都不明白
把sheet3写入到access