garfild_1
asked on
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,Value
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
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,Value
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
ASKER
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
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 SubASKER
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],[V
Thank you very much.
Kind regards...
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],[V
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello,
I've solved problem with the following VBA code. Anyway thank you very much for your time and effort.
Kind regards...
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 & "'"
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.