Solved

"Loop" in Access table and export to Excel

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

911 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

19 Experts available now in Live!

Get 1:1 Help Now