Excel Access
Hola!!!
Tengo una super pregunta, podrías decirme como puedo pasar datos de excel a access sin la utilizacion de ado.
Tengo este código (lo encontre en Internet) pero marca un error en el tipo de variable Database y pues no se como resolverlo.
Option Explicit
Private Sub cmdLoad_Click()
Dim excel_app As Object
Dim excel_sheet As Object
Dim db As Database
Dim new_value As String
Dim row As Integer
Screen.MousePointer = vbHourglass
DoEvents
' Crea la aplicación Access.
Set excel_app = CreateObject("Excel.Application")
excel_app.Visible = True
' Open the Excel spreadsheet.
excel_app.Workbooks.open FileName:=txtExcelFile.Text
' Check for later versions.
If Val(excel_app.Application.Version) >= 8 Then
Set excel_sheet = excel_app.ActiveSheet
Else
Set excel_sheet = excel_app
End If
' Open the Access database.
Set db = OpenDatabase(txtAccessFile.Text)
' Get data from the Excel spreadsheet and insert
' it into the TestValues table.
row = 1
Do
' Get the next value.
new_value = Trim$(excel_sheet.cells(row, 1))
' See if it's blank.
If Len(new_value) = 0 Then Exit Do
' Insert the value into the database.
'On Error Resume Next
db.Execute "INSERT INTO TestValues VALUES (" & CSng(new_value) & ")"
'db.Execute "INSERT INTO TestValues VALUES (0.198663)"
row = row + 1
Loop
' Close the database.
db.Close
Set db = Nothing
' Comment the rest of the lines to keep
' Excel running so you can see it.
' Close the workbook without saving.
excel_app.ActiveWorkbook.Close False
' Close Excel.
'excel_app.Quit
'Set excel_sheet = Nothing
'Set excel_app = Nothing
Screen.MousePointer = vbDefault
MsgBox "Copiados " & Format$(row - 1) & " valores."
End Sub
' Note that this project contains a reference to
' Microsoft DAO 3.51 Object Library.
Private Sub Form_Load()
Dim file_path As String
file_path = App.Path
If Right$(file_path, 1) "\" Then file_path = file_path & "\"
txtExcelFile.Text = file_path & "XlsToMdb.xls"
txtAccessFile.Text = file_path & "XlsToMdb.mdb"
End Sub
de ante mano muchas gracias
Tengo una super pregunta, podrías decirme como puedo pasar datos de excel a access sin la utilizacion de ado.
Tengo este código (lo encontre en Internet) pero marca un error en el tipo de variable Database y pues no se como resolverlo.
Option Explicit
Private Sub cmdLoad_Click()
Dim excel_app As Object
Dim excel_sheet As Object
Dim db As Database
Dim new_value As String
Dim row As Integer
Screen.MousePointer = vbHourglass
DoEvents
' Crea la aplicación Access.
Set excel_app = CreateObject("Excel.Application")
excel_app.Visible = True
' Open the Excel spreadsheet.
excel_app.Workbooks.open FileName:=txtExcelFile.Text
' Check for later versions.
If Val(excel_app.Application.Version) >= 8 Then
Set excel_sheet = excel_app.ActiveSheet
Else
Set excel_sheet = excel_app
End If
' Open the Access database.
Set db = OpenDatabase(txtAccessFile.Text)
' Get data from the Excel spreadsheet and insert
' it into the TestValues table.
row = 1
Do
' Get the next value.
new_value = Trim$(excel_sheet.cells(row, 1))
' See if it's blank.
If Len(new_value) = 0 Then Exit Do
' Insert the value into the database.
'On Error Resume Next
db.Execute "INSERT INTO TestValues VALUES (" & CSng(new_value) & ")"
'db.Execute "INSERT INTO TestValues VALUES (0.198663)"
row = row + 1
Loop
' Close the database.
db.Close
Set db = Nothing
' Comment the rest of the lines to keep
' Excel running so you can see it.
' Close the workbook without saving.
excel_app.ActiveWorkbook.Close False
' Close Excel.
'excel_app.Quit
'Set excel_sheet = Nothing
'Set excel_app = Nothing
Screen.MousePointer = vbDefault
MsgBox "Copiados " & Format$(row - 1) & " valores."
End Sub
' Note that this project contains a reference to
' Microsoft DAO 3.51 Object Library.
Private Sub Form_Load()
Dim file_path As String
file_path = App.Path
If Right$(file_path, 1) "\" Then file_path = file_path & "\"
txtExcelFile.Text = file_path & "XlsToMdb.xls"
txtAccessFile.Text = file_path & "XlsToMdb.mdb"
End Sub
de ante mano muchas gracias
1 respuesta
Respuesta de denciso
1