Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 15th, 2007, 05:10 AM
Authorized User
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel to Access using ADO

I understand from what I have been told that to send data from Excel to Access automatically could be written in vba using ADO connection.

How could I write some code so that once the user clicks on a button that it takes a range of data and connects to a database and imports the data to the relevant table?
My data consists of columns of Text, Numbers and Currency. would it be best to change the Currency to Numbers for smooth transfer?

I am really stuck with this, can anyone help?


Reply With Quote
  #2 (permalink)  
Old June 25th, 2007, 09:13 AM
Authorized User
Join Date: Jun 2004
Location: Darmstadt, , Germany.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts

hi Jez,
It sounds a little bit as though you're jumping in at the deep-end. You might find this tutorial helpful..

Ne auderis delere orbem rigidum meum!
Reply With Quote
  #3 (permalink)  
Old July 22nd, 2007, 05:55 PM
Registered User
Join Date: Jul 2007
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts

The following code works for me when I need to export data to Access. The example assumes the data is the a worksheet named Data and the data excluding the headings is in a range named TheDataRange. The excel data is appended to an Access table named Shipments. Also the Excel workbook and Access files are in the same folder. If your Access file is a different Folder, you will need to use the full path and name for the Access database file. Remember to add the ADO object to your reference in VBA.

   Dim ws As Worksheet
    Dim rngData As Range
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    Dim DBPathName As String
    Dim cnnstring As String

    DBPathName = ThisWorkbook.Path & "\" & "SHIPMENTS_WITH_FORECAST.mdb"
    cnnstring = "Provider=microsoft.Jet.OLEDB.4.0;Data Source = " & DBPathName
    cnn.Open cnnstring

    Dim TheTable As String
    TheTable = "Shipments"

    With rst
        .ActiveConnection = cnn
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open TheTable, Options:=adCmdTableDirect
    End With

    Set ws = Sheets("Data")
    Set rngData = ws.Range("TheDataRange")

    Dim I As Long
    Dim J As Long

    Dim Imax As Long
    Dim Jmax As Long

    Imax = rngData.Rows.Count
    Jmax = rngData.Columns.Count

    Dim FirstRow As Integer

    FirstRow = rngData(1, 1).Row

    For I = FirstRow To FirstRow + Imax - 1
        For J = 1 To Jmax
            If Len(ws.Cells(I, J).Value) <> 0 Then
                rst.Fields(J - 1) = ws.Cells(I, J).Value
            End If
        Next J
    Next I

    Set cnn = Nothing
    Set rst = Nothing

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO.NET and Excel Theone84 ADO.NET 0 July 7th, 2008 05:20 AM
Using ADO to Query Excel alex_w Excel VBA 1 October 19th, 2006 04:42 AM
Using ADO to Query Excel alex_w Access VBA 1 May 18th, 2005 11:59 AM
ado excel/access loane Excel VBA 5 August 17th, 2004 10:18 AM

All times are GMT -4. The time now is 10:50 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.