?
Solved

"Loop" in Access table and export to Excel

Posted on 2009-05-20
5
Medium Priority
?
225 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
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

829 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