?
Solved

"Loop" in Access table and export to Excel

Posted on 2009-05-20
5
Medium Priority
?
221 Views
Last Modified: 2012-05-07
Hi,
I'm exporting data form access in excel worksheet using VBA code below according to date I select in combo box. It means that I'm able only to export data that reffers to selected date. I would appreciate enyones help in order to make possible exporting data from access in excel as it is described in addition:
Table content in Access:
Column1      Column2           Column3
xx                      A               31.01.2009
xx                      B               28.02.2009
xx                      C               31.03.2009
xy                      A               31.01.2009
xy                      B               31.03.2009

Result I woud like to have in Excel table:

Column1       Column2         Column3        Column4
xx                    A                      B                   C
xy                    A                                           B

Please help in order to adjust my current VBA (as below) or if someone has better solution.
Thank you.
Private Sub Start_Command_Click()
Dim col As Integer
Dim row As Integer
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
 
stDB = "Path to db\db31.mdb"
 
Range("B5") = Me.ComboBox1.Value
Range("D5") = Me.ComboBox2.Value
 
 
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;" _
& "Data Source=" & stDB & ";"
Range("A7:E888") = " "
strSQL = " SELECT [T1.RB],[T1.ID],[T1.ID2], [T1." & Me.ComboBox1.Value & "] WHERE T1.Date= '" & Me.ComboBox2.Value & "' order by T1.RB "
 
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
Comment
Question by:garfild_1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:jparul
ID: 24435520
what's the criteria to put data in col 2,3,4?
0
 
LVL 6

Expert Comment

by:jparul
ID: 24435536
And are you getting any errors in the above posted code?
 
0
 

Author Comment

by:garfild_1
ID: 24438238
Sorry for delay.
Above VBA code works just fine but is limited only for one date.
Criteria for selection would be column name in Access table.
I know that posted VBA code does not fit to my request but I just wanted to provide general idea how it works currently.
So basicaly, in Where criteria I would use Cobmo box value in excel worksheet (loaded with each column name for Access table) - it is not a problem to define. No date would be required as criteria any longer, but data should be posted in excel worksheet (in colums) according to date in Access table. (like all data with 31,01.2009 form selected column go to Column b in excel, then all data with 28.02.2009 from selected column go to column c in excel and so on....)


0
 

Author Comment

by:garfild_1
ID: 24438262
When I say criteria is Column name in Access table then in my particular e.g. Column 2 would be that criteria for selection. Column 3(which contains only date) would never be criteria for selection data (only should be somehow used as information for posting data into excel worksheet in correct column.
0
 

Accepted Solution

by:
garfild_1 earned 0 total points
ID: 24443089
Hi,
Since I don't have any reply jet is this possible to make working anyway.
Solution should work the same way as pivot table in excel but I have to make this working in VBA code (exporting data from Access db etc...)
I would appreciate very much assistance regarding this...
Please one more find VBA I'm currently using to export data from access according to column name and date both selected in comboboxes. The same joins two tables but it can be desagreed because my question is only related to one table and exporting data from the same one.
Somehow I need assistance to adjust the same in order to have result as described in my original (first) question (if adjustment of the same is possible anyway in order to have solution)

Private Sub Start_Command_Click()
Dim col As Integer
Dim row As Integer
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
 
stDB = "\\Path to database\Access\db31.mdb"
 
Range("B5") = Me.ComboBox1.Value
Range("D5") = Me.ComboBox2.Value
 
 
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;" _
& "Data Source=" & stDB & ";"
Range("A7:E888") = " "
strSQL = " SELECT [O.RB],[O.Name],O.ID, [O." & Me.ComboBox1.Value & "], [B." & Me.ComboBox1.Value & "]*1000 FROM O LEFT OUTER JOIN B ON (O.ID = B.ID) AND (O.Date=B.Date) WHERE O.Date = '" & Me.ComboBox2.Value & "' order by O.RB "
 
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
MsgBox "Done"
End Sub

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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