msquared99
Member
Greetings everyone!
I figured I am always asking for advice on here so now I am giving some. I found this code somewhere, Bacon Bits I believe.
I had build an Access database that is tied live to some other software we have. I needed an easy way to extract data and to be able to analyze it. My delima was I wanted to use Excel. Instead of opening Access, the query and exporting to Excel, I figured why not use VBA to do the job.
I open my Excel spreadsheet, enter a COID in a cell, click a botton, and vola! I get the data I need in Excel.
A few things, you need to select Microsoft DAO XX Object Library in the reference library, XX being the version you have. I have Office 2010 so mine is 3.6. In the Access query where you set your parameter in the Criteria field it must have Like and & "*", in my case, Like [Enter a COID?] & "*" I can leave cell C1 blank to pull all the data.
Here is my code:
I figured I am always asking for advice on here so now I am giving some. I found this code somewhere, Bacon Bits I believe.
I had build an Access database that is tied live to some other software we have. I needed an easy way to extract data and to be able to analyze it. My delima was I wanted to use Excel. Instead of opening Access, the query and exporting to Excel, I figured why not use VBA to do the job.
I open my Excel spreadsheet, enter a COID in a cell, click a botton, and vola! I get the data I need in Excel.
A few things, you need to select Microsoft DAO XX Object Library in the reference library, XX being the version you have. I have Office 2010 so mine is 3.6. In the Access query where you set your parameter in the Criteria field it must have Like and & "*", in my case, Like [Enter a COID?] & "*" I can leave cell C1 blank to pull all the data.
Here is my code:
Code:
Sub RunParameterQuery()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase("C:\Access DB Test\M3 Database Export.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("Commissions")
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter a COID?]") = Range("C1").Value
End With
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 5: Clear previous contents
Sheets("Main").Select
ActiveSheet.Range("A6:K100000").ClearContents 'You can use rows.count if you like
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A7").CopyFromRecordset MyRecordset
'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i
MsgBox "Your Query has been Run"
End Sub