Extracting data from Access to Excel worksheet

Posted on 2009-02-12
Last Modified: 2012-05-06
I would appreciate help :
I wish to export some data from access to excel this way:
I have two tables im MS Access dataBase with following structure:
Table A_09:
Position        PositionID   Value 1     Value2      Value3     Value4     Value5      Value 6        Date
Position1                  X1      1      2      3      4      5      6        31.01.2009
Position2                  X2      1      2      3      4      5      6        28.02.2009
Position3                  X3      1      2      3      4      5      6        31.01.2009

and Table B_09 with the same structure as table A_09. (both in attached Acces database). Table B_09:

Position               PositionID   Value1      Value2      Value3     Value4     Value5      Value 6        Date
Position1                  X1      4      5      6      7      8      9        31.01.2009
Position2                  X2      4      5      6      7      8      9        28.02.2009
Position3                  X3      4      5      6      7      8      9        31.01.2009

Also I have excel workbook in which I wish to extract selected data from these two tables in Access data base. Please find attached xls workbook as well. What I need to do is as follows:
I created two comboboxes in excel worksheet which are loaded with data from Access db. The First combo is loaded with Value1,Value2,Value3,Value4,Value5 and Value6  and the second one is loaded with date value. Also there is Start command button that should execute sub which will load my cells in excel worksheet with information I wish to have according to selected values im my comboboxes. For example:
If in first combobox I select Value1 and in second combobox I select 31.01.2009 date value than the cells in excel sheet should be populated with the following data from tables form Access database:

Position                 PositionID             Value_A        Value_B
Position1              X1                        1                         4
Position3              X3                        1                         4

Otherwise if i select 28.02.2009 date value instead 31.01.2009 then the cells in excel result should be populated as follows:

Position                 PositionID             Value_A        Value_B
Position2              X2                       1                         4

The same explanation is applied if I change selected value in firstcombo (Value1,Value2.....)
What is very importan is that table A_09 is leading table meaning that if there is no for instance Position3 in the same but the position3 existing in table B_09 then no data should be extraced to excel workbook. Otherwise if position3 exist in Table A_09 but doesn't in B_09 than information should be extracted to excel worksheet. So, cells in column named Position and PositionId are populatzed from table A_09 form Access database.
I would appreciate your help in order to provide me with VBA code  that will solve my problem and have results as I wish. This is very important to me and in that manner I thank in advance for all your time and effort.
Also, plese be aware that you have to set path to Access database in Excel VBA in order to make this combobox load working.
Thank you very much

Question by:garfild_1
    LVL 84
    So what have you tried so far in order to accomplish this? Can you post the code samples you've used?

     Your question seems more like instructions on what programming tasks you'd like us to perform for you than a request for coding help. That's not really what EE is about.


    Author Comment

    Hello, sorry for missunderstanding. Please fine bellow VBA code I'm trying to make working.
    First I have problem because the same gives me an error as folows:
    Run-time error  2147467259-80004005
    Method Open of Object "_Recordset" failed.
    It is my first problem...
    Second one is how to join another table from Access database in my Sql that will select Value2 field in my excel worksheet.
    Following, data should be imported from row 7.
    I hope I was more clear now . hoping I can get help from you
    thank you in advance for your time and effort.
    Kind regards
    Private Sub Start_Command_Click()
    Dim col As Integer
    Dim row As Integer
    Dim cnt As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim stDB, user, stConn As String
    stDB = "your path to database\db1.mdb"
    Set cnt = New ADODB.Connection
    Set rs = New ADODB.Recordset
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;" _
    & "Data Source=" & stDB & ";"
    cnt.Open stConn
    cnt.CursorLocation = adUseClient
    strSQL = "select Position, PositionID, Value1, value2 from  A_09 "
    rs.Open strSQL, cnt
    col = 0
     row = 7
      Do While Not rs.EOF
        row = row + 1
        col = 0
        Do While col < rs.Fields.Count
          Cells(row, col + 1) = rs.Fields(col).Value
          col = col + 1
    Set rs = Nothing: Set cnt = Nothing
    End Sub

    Open in new window


    Author Comment

    I have made this VBA working. See bellow.
    I would appreciate your help in order to adjust my SQL statement as follows:
    I need to select Position,PositionID and Value1 from table A_09 , bur Value2 I need to select from another table named B_09. (Both tables A_09 and B_09 are in the same Access database).
    To all Experts who can help:
    Can someone help me make(adjust) this my  SQL statement (with joined tables) as I described. (Now I select all information only from A_09 table)
    strSQL = "select [Position],[PositionID],[Value1],[Value2] from A_09 "
    Thank you very much.
    Kind regards...
    Dim col As Integer
    Dim row As Integer
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    stDB = "C:\Users\Samira\Desktop\db1.mdb"
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;" _
    & "Data Source=" & stDB & ";"
    strSQL = "select [Position],[PositionID],[Value1],[Value2] from A_09 "
    rst.Open strSQL, cnn
     col = 0
     row = 6
      Do While Not rst.EOF
        row = row + 1
        col = 0
        Do While col < rst.Fields.Count
          Cells(row, col + 1) = rst.Fields(col).Value
          col = col + 1
    Set rst = Nothing: Set cnn = Nothing
    End Sub

    Open in new window

    LVL 84

    Accepted Solution

    How are A_09 and B_09 related? Typically, when you Join tables in a Query, they would be related in some way (i.e. Table A_09 would have a Foreign Key field that stores the ID from a record in TAble B_09, or vice versa).

    If they aren't related, you can use a Union query:

    SELECT You can use a Union query"

    "select [Position],[PositionID],[Value1],[Value2] from A_09  UNION select [Position],[PositionID],[Value1],[Value2] from B_09"

    Author Comment

    I've solved problem with the following VBA code. Anyway thank you very much for your time and effort.
    Kind regards...

    strSQL = " SELECT [A_09.Position],A_09.PositionID, [A_09." & Me.ComboBox1.Value & "], [B_09." & Me.ComboBox1.Value & "] FROM A_09 LEFT OUTER JOIN B_09 ON A_09.PositionID = B_09.PositionID WHERE A_09.Date = '" & Me.Combobox2.Value & "'"

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now