Solved

"Loop" in Access table and export to Excel

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now