none
conexão RRS feed

  • Pergunta

  • 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,
    quarta-feira, 23 de outubro de 2013 22:25