View Full Version : Excel-VBA
Hi
Am new to excel programming.I have created one table known as tab1 with 5 fields using ms access.In the excel sheet i inserted one button.
I need a macro such that when i click the button in the excel sheet,i need the datas from the table which i have created in access to be displayed in the excel sheet.I think we can write sql query in the macro to bring all the columns in excel sheet.Please help me to do this
Bob Phillips
01-18-2012, 05:37 AM
Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"
sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
Range("A1") = ary
Else
MsgBox "No records returned.", vbCritical
End If
oRS.Close
Set oRS = Nothing
End Sub
mancubus
01-18-2012, 05:38 AM
hi and wellcome to VBAX.
try this with a copy of your file...
Option Explicit
Sub Import_AccessData()
'http://www.ozgrid.com/forum/showthread.php?t=11285
'You need to set a reference to Microsoft ActiveX Data Objects x.x library
'Microsoft ActiveX Data Objects Recordset Objects x.x library, Microsoft Access XX.0 Object Library, etc
'via Tools | Reference... in the VB-editor.
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim stDB As String
Dim wsSheet As Worksheet
Dim lnNumberOfField As Long, lnCount As Integer
Set wsSheet = ThisWorkbook.Worksheets("Sheet1") 'change Sheet1 to your sheet name
stDB = ThisWorkbook.Path & "\" & "XLData.mdb" 'chage XLData.mdb to your db name
'assuming excel and access files are in the same folder.
'otherwise stg like this:
'stDB = "C:\Users\xxxx\MyFiles\XLData.mdb"
wsSheet.Range("A1").CurrentRegion.Clear
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & stDB & ";"
'for office 2007-2010:
'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & stDB & ";"
rst.Open "SELECT * FROM tblName", cnt 'change tblName to your table name
lnNumberOfField = rst.Fields.Count
For lnCount = 0 To lnNumberOfField - 1
wsSheet.Cells(1, lnCount + 1).Value = rst.Fields(lnCount).Name
Next lnCount
wsSheet.Cells(2, 1).CopyFromRecordset rst
Set rst = Nothing
Set cnt = Nothing
End Sub
Hi mancubus,
I tried ur code.Am getting an error as user-defined type not defined in the line Dim cnt As New ADODB.Connection.
Kenneth Hobs
01-23-2012, 07:04 AM
'You need to set a reference to Microsoft ActiveX Data Objects x.x library 'Microsoft ActiveX Data Objects Recordset Objects x.x library, Microsoft Access XX.0 Object Library, etc 'via Tools | Reference... in the VB-editor.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.