Solved

"Loop" in Access table and export to Excel

Posted on 2009-05-20
5
217 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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