We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Extracting data from Access to Excel worksheet

Medium Priority
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

Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

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.


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


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

Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
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"

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.