• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

Extracting data from Access to Excel worksheet

Hello,
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

Sample.xls
db1.mdb
0
garfild_1
Asked:
garfild_1
  • 3
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

0
 
garfild_1Author Commented:
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
    Loop
  
    rs.MoveNext
  Loop
 
Set rs = Nothing: Set cnt = Nothing
End Sub

Open in new window

0
 
garfild_1Author Commented:
Hi,
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
    Loop
  
    rst.MoveNext
  Loop
 
Set rst = Nothing: Set cnn = Nothing
End Sub

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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"
0
 
garfild_1Author Commented:
Hello,
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

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now