Import Data from Access Query Results to Excel using VBA

excel6000

Board Regular
Joined
Jul 2, 2014
Messages
61
I have some code which runs a few queries to get some data from an Access database. In my code below, you will see towards the bottom several lines of code which I have tried to use to import this data, but none of them have worked for me. This is why I have turned them into comments. The line which reads "Worksheets("Div 1").Cells(246, 1).CopyFromRecordset rst1" actually imports data, but it just imports whatever data the query is returning for that specific instant ("WSName" is a variable which changes). I need to import the data based on the rst1 variable which changes and this is why I have tried things like "div 5" and "div 1" as these are the names of the changing variables.
Code:
Sub GetData()
    Dim appAccess As Access.Application
    Dim Db  As DAO.Database
    Dim rstsum, rst1, rst2 As DAO.Recordset


    Set appAccess = Access.Application
    
    Set Db = appAccess.CurrentDb


    strsql = "SELECT EstimateSummary.WSName, EstimateSummary.PrtCode " & _
                "FROM EstimateSummary " & _
                "WHERE (((EstimateSummary.PrtCode)=""1""));"


    Set rstsum = Db.OpenRecordset(strsql)
    
    Do While Not rstsum.EOF
        strsql = "SELECT EstimateDetail.SheetName, EstimateDetail.EstFlag, EstimateDetail.HdrRow, EstimateDetail.EstOrder, EstimateDetail." & _
                            "[*ExtDesc], EstimateDetail.[*ExtCalcQty], EstimateDetail.[*ExtUoM], EstimateDetail.[*ExtTotalU], EstimateDetail.[*ExtTotalCost]" & _
                    " FROM EstimateDetail" & _
                    " WHERE (((EstimateDetail.SheetName) = " & Chr(34) & rstsum("WSName") & Chr(34) & ") And ((EstimateDetail.HdrRow) = 3))" & _
                    " ORDER BY EstimateDetail.EstOrder;"
    
        Debug.Print strsql
        
        Set rst1 = Db.OpenRecordset(strsql)
        
        '... some code to import
        
        'Worksheets("Div 1").Cells(246, 1) = rst1("SheetName")
        
        'Sheets("Div 5").Add.rst1 ("div 5")
                
        'Worksheets("Div 1").Cells(246, 1) = rst1("Div 1")


        'Worksheets("Div 1").Cells(246, 1).CopyFromRecordset rst1
                
        'Div 1.Range(“A1").CopyFromRecordset rst1
        
        rstsum.MoveNext
    
    Loop
    
End Sub
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,602
Messages
5,838,321
Members
430,538
Latest member
PedroOliveira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top