利⽤VBA创建ACCESS数据库并导⼊CSV数据
⽅法⼀：利⽤VBA创建ACCESS数据库
Sub CR_DB()
Dim AC As Object
Set AC = CreateObject("ACCESS.APPLICATION")
Db = ThisWorkbook.Path & "\test.accdb"
If Dir(Db) <> "" Then
Kill Db
End If
With AC
.NEWCURRENTDATABASE Db
.
CloseCurrentDatabase
End With
Set AC = Nothing
⽅法⼆：利⽤VBA导⼊CSV数据到ACCESS数据库
Dim cnn As Object
access数据库生成网页版
Dim myPath$, MyFile$, Sql$, s$
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb" '连接数据库
myPath = ThisWorkbook.Path & "\"
MyFile = Dir(myPath & "*.CSV")
While MyFile <> ""
s = "[" & MyFile & "]" & vbCrLf & "ColNameHeader = TRUE" & vbCrLf & "Format = CSVDelimited" & vbCrLf & "MaxScanRows=0"
‘’‘若表已经存在，如TEST，则直接导⼊如下：
Sql = " INSERT INTO TEST SELECT DATE_ID,EUTRANCELLTDD AS CELL,InterferencePwrPusch AS PUSCH⼲扰,InterferencePwrPucch AS PUCC H⼲扰  FROM [TEXT;HDR=NO;FMT=Delimited;DATABASE=" & myPath & ";].[" & MyFile & "];"
’‘’若表不存在则先创建，TEST表，如下：
Sql = " SELECT DATE_ID,EUTRANCELLTDD AS CELL,InterferencePwrPusch AS PUSCH⼲扰,InterferencePwrPucch AS PUCCH⼲扰 INTO TEST  F ROM [TEXT;HDR=NO;FMT=Delimited;DATABASE=" & myPath & ";].[" & MyFile & "];"
Open myPath & "schema.ini" For Output As #1
Print #1, s
Close #1
cnn.Execute Sql
MyFile = Dir()
Wend
cnn.Close
Set cnn = Nothing
Kill ThisWorkbook.Path & "\schema.ini"
End Sub

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。