Hi,
Please find the below code for reference:
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;" ‘this is the path of
your access database.
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
The macro example assumes that your VBA project has added a reference to the
ADO object library.
You can do this from within the VBE by selecting the menu Tools, References
and selecting Microsoft
ActiveX Data Objects x.x Object Library.
Use ADO if you can choose between ADO and DAO for data import or export.
Hope this above code will help. Please do let me know incase I can provide
any further help on it.
Regards,
Deepak
On Wed, Dec 9, 2009 at 2:28 AM, Chanti-Hyderabad <ramesh1...@gmail.com>wrote:
>
> Hope you are doing well.
>
> Yes I agree with you, and let me apologize for the the vague request.
>
> You guessed it right, i have a Access database and a excel report..i
> want to pull certain fields from the access table.
>
> It is not that i want a button, but even if a code can extract from
> background would be fine.
>
> Hope i made my request clear this time.
>
> Sorry Paul.
>
> Best regards,
> Ramesh
>
> On Dec 7, 6:14 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
> > I suspect you'll be waiting quite a while.
> > adding a "browse button" is quite confusing.
> > You might as well say: "I'd like to add a "take the dog for a walk"
> button"
> > Because you can't do that with Access either.
> >
> > the problem is that YOU know what you mean by "browse"
> > but no one else does.
> >
> > What we can GUESS from your question is that:
> > A) You have an MS-Access2007 database.
> > B) You have an Excel 2007 workbook.
> > C) You'd like to GET data from the Ms-Access database
> > INTO the Excel2007 file.
> > D) You want a button to do it.
> >
> > Now, what do you need help with?
> > Writing a macro to retrieve data from MS-Access?
> > Defining which data elements to retrieve?
> > Or assigning the macro you already have to a button?
> >
> > Or do you just want to take the dog for a walk and start over when you
> get back?
> > (just kidding)
> >
> > Paul
> >
> > ________________________________
> > From: Chanti-Hyderabad <ramesh1...@gmail.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Mon, December 7, 2009 5:11:04 AM
> > Subject: $$Excel-Macros$$ Re: Add brwose button to MS Excel to retrieve
> data from MS Access 2007
> >
> > Hi Gurus,
> >
> > Awaiting for your help..Thanks
> >
> > On Dec 1, 5:23 pm, Chanti-Hyderabad <ramesh1...@gmail.com> wrote:
> >
> > > Hi Excel Gurus,
> >
> > > Hope this new post find you all well.
> >
> > > I would like to add a 'Browse button' (command button) to my excel
> > > 2007 file, which should pull the data from MS Access 2007.
> >
> > > I hope its a bit confusing request, please do let me know if you need
> > > any further clarification.
> >
> > > Regards---
> >
> > --
> >
> ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads
> athttp://www.excelitems.com
> > 2. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 3. Learn VBA Macros athttp://www.vbamacros.blogspot.com
> > 4. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> >
> > To post to this group, send email to excel-macros@googlegroups.com
> > If you find any spam message in the group, please send an email to:
> > Ayush Jain @ jainayus...@gmail.com or
> > Ashish Jain @ 26may.1...@gmail.com
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
> >
> > We reach over 6,500 subscribers worldwide and receive many nice notes
> about the learning and support from the group. Our goal is to have 10,000
> subscribers by the end of 2009. Let friends and co-workers know they can
> subscribe to group athttp://groups.google.com/group/excel-macros/subscribe
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> http://www.excelitems.com
> 2. Excel tutorials at http://www.excel-macros.blogspot.com
> 3. Learn VBA Macros at http://www.vbamacros.blogspot.com
> 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>
> To post to this group, send email to excel-macros@googlegroups.com
> If you find any spam message in the group, please send an email to:
> Ayush Jain @ jainayus...@gmail.com or
> Ashish Jain @ 26may.1...@gmail.com
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,500 subscribers worldwide and receive many nice notes about
> the learning and support from the group. Our goal is to have 10,000
> subscribers by the end of 2009. Let friends and co-workers know they can
> subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
--
Thanks,
Deepak Rai
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
