Boa tarde,
Senhores, preciso importar uma tabela do excel para um banco do Access, estou usando a seguinte rotina
Private Sub Comando0_Click()
Dim xlApp As Excel.Application
Dim xlBk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim dbRst As Recordset
Dim dbs As Database
Dim SQLStr As String
Dim sSQL As String
Set dbs = CurrentDb
Set xlApp = Excel.Application
Set xlBk = xlApp.Workbooks.Open("C:\Users\Eduardo Yamasaki\Desktop\test\plan.xlsx")
Set xlSht = xlBk.Sheets(1)
SQLStr = "DROP TABLE excelData"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLStr)
SQLStr = "CREATE TABLE excelData(columnOne TEXT, columnTwo TEXT)"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLStr)
Set dbRst = dbs.OpenRecordset("excelData")
dbRst.AddNew
sSQL = "SELECT * INTO [excelData] " & "IN '" & "C:\Users\Eduardo Yamasaki\Desktop\test\plan.xlsx" & "' " & " FROM [Plan1$]"
DoCmd.RunSQL (sSQL)
dbRst.Close
dbs.Close
End Sub
Quando ele vai executar o select, ele não consegue encontrar a planilha Plan1
Obrigado,
At,
Senhores, preciso importar uma tabela do excel para um banco do Access, estou usando a seguinte rotina
Private Sub Comando0_Click()
Dim xlApp As Excel.Application
Dim xlBk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim dbRst As Recordset
Dim dbs As Database
Dim SQLStr As String
Dim sSQL As String
Set dbs = CurrentDb
Set xlApp = Excel.Application
Set xlBk = xlApp.Workbooks.Open("C:\Users\Eduardo Yamasaki\Desktop\test\plan.xlsx")
Set xlSht = xlBk.Sheets(1)
SQLStr = "DROP TABLE excelData"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLStr)
SQLStr = "CREATE TABLE excelData(columnOne TEXT, columnTwo TEXT)"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLStr)
Set dbRst = dbs.OpenRecordset("excelData")
dbRst.AddNew
sSQL = "SELECT * INTO [excelData] " & "IN '" & "C:\Users\Eduardo Yamasaki\Desktop\test\plan.xlsx" & "' " & " FROM [Plan1$]"
DoCmd.RunSQL (sSQL)
dbRst.Close
dbs.Close
End Sub
Quando ele vai executar o select, ele não consegue encontrar a planilha Plan1
Obrigado,
At,