Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Upload your resume securely at https://www.dwsimpson.com
to be contacted when our jobs meet your skills and objectives.


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 09-16-2017, 07:22 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 824
Default Import Access query into Excel

I am looking for some Excel VBA code that can import query results from Access into Excel.

I'd also like to check if the Access database containing the query is open or not.

Anybody have any code they would be kind enough to share?
Reply With Quote
  #2  
Old 09-17-2017, 07:00 PM
Chuck Chuck is offline
Member
SOA AAA
 
Join Date: Oct 2001
Location: Illinois
Posts: 4,518
Default

I modified slightly to generalize, so it may need some debugging and improvements. But the Excel VBA code below runs parameter queries from Access and writes them to a tab in your Excel worksheet. I always find it easier to store parameter queries in Access and run them (rather than directly running SQL). I'm sure code could be modified to do that too though.

Not claiming it is great code, but it works.

Excel VBA must add reference to use ADO. I am using:

Microsoft ActiveX Data Objects 6.1 Library.

I reference other things, but I am pretty sure this is the one that needs adding.

Quote:

Sub queries_GetQueryData(tTargetSheetName as string, tQueryName as string, tQueryParameters as string, tDataBaseFile as string)


'Objects needed to setup and use ADO database connection
Dim objConnect As New ADODB.Connection 'Database Connection string
Dim objRecSet As New ADODB.Recordset 'Database Record set
Dim objQuery As New ADODB.Command 'Database Query

Dim tDataBaseFile As String 'Name of database, assumed stored in same folder as this spreadsheet
Dim tQueryParameters As String 'eg " 'qp1','qp2','qp3' " etc
Dim lRecCount As Long 'Number of policies
Dim tCurrentSheetName As String

Application.Calculate

tCurrentSheetName = ActiveSheet.Name
Worksheets(tTargetSheetName).Select
Range("A2:L65536").ClearContents

'OPEN DATABASE
util_OpenMyDBConnection Application.ActiveWorkbook.Path & "\" & tDataBaseFile, objConnect, objQuery, objRecSet, ""

'RUN QUERY
objQuery.CommandText = "Execute " & tQueryName & " " & tQueryParameters

'POPULATE SHEET WITH RecordSet data
queries_PopulateSheetFromRecSet tTargetSheetName & "!A1", lRecCount, objRecSet, objQuery

'CLOSE DATA BASE CONNECTION
util_CloseMyDBConnection objConnect, objQuery, objRecSet

Worksheets(tCurrentSheetName).Select

End Sub

Sub queries_PopulateSheetFromRecSet(tStartCell As String, lRecCount As Long, objRecSet As ADODB.Recordset, objQuery As ADODB.Command)

'WRITES THE RECSET TO DESIRED TAB

Dim objField As ADODB.Field 'Field object for database record
Dim lRow As Long 'Count rows of data
Dim iCol As Integer 'Count columns for fields

'OPEN RECORD SET
objRecSet.Open objQuery, , ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly

'PRINT FIELD NAMES AT TOP OF SHEET
iCol = 0
For Each objField In objRecSet.Fields
Range(tStartCell).Offset(0, iCol).Value = objField.Name
iCol = iCol + 1
Next

'POPULATE SHEET WITH DATA FROM RECSET
lRow = 1
Do Until objRecSet.EOF
iCol = 0
For Each objField In objRecSet.Fields
Range(tStartCell).Offset(lRow, iCol).Value = objField.Value
iCol = iCol + 1
Next
objRecSet.MoveNext
lRow = lRow + 1
Loop
lRecCount = lRow - 1

'CLEAN UP
objRecSet.Close
Set objField = Nothing

End Sub




Sub util_OpenMyDBConnection(strDBName As String, objConnect As ADODB.Connection, objQuery As ADODB.Command, objRecSet As ADODB.Recordset, tPassword As String)

Dim strConnect As String
Dim strDataSource As String

strDataSource = "Data Source=" & strDBName & ";"

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Persist Security Info=False;" _
& "User ID=admin;" _
& strDataSource _
& "Jet OLEDBatabase Password=" & tPassword

With objConnect
.ConnectionString = strConnect
.ConnectionTimeout = 10
.Open
End With

Set objQuery.ActiveConnection = objConnect
objQuery.CommandType = ADODB.CommandTypeEnum.adCmdText
objRecSet.CursorLocation = ADODB.CursorLocationEnum.adUseServer

End Sub

Sub util_CloseMyDBConnection(objConnect As ADODB.Connection, objQuery As ADODB.Command, objRecSet As ADODB.Recordset)

objConnect.Close

Set objRecSet = Nothing
Set objConnect = Nothing
Set objQuery = Nothing

End Sub
Reply With Quote
  #3  
Old 09-18-2017, 10:25 AM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,249
Default

The range object in Excel VBA has a function called CopyFromRecordset which will replace all of this

Spoiler:
Code:
'POPULATE SHEET WITH DATA FROM RECSET
lRow = 1
Do Until objRecSet.EOF
iCol = 0
For Each objField In objRecSet.Fields
Range(tStartCell).Offset(lRow, iCol).Value = objField.Value
iCol = iCol + 1
Next
objRecSet.MoveNext
lRow = lRow + 1
Loop
lRecCount = lRow - 1
Reply With Quote
  #4  
Old 09-18-2017, 05:54 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 824
Default

Thanks for the comment, Dumples.

Do you have some VBA code using CopyFromRecordset that you wouldn't mind sharing?
Reply With Quote
  #5  
Old 09-18-2017, 06:01 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 824
Default

Quote:
Originally Posted by Chuck View Post
I modified slightly to generalize, so it may need some debugging and improvements. But the Excel VBA code below runs parameter queries from Access and writes them to a tab in your Excel worksheet. I always find it easier to store parameter queries in Access and run them (rather than directly running SQL). I'm sure code could be modified to do that too though.

Not claiming it is great code, but it works.

Excel VBA must add reference to use ADO. I am using:

Microsoft ActiveX Data Objects 6.1 Library.

I reference other things, but I am pretty sure this is the one that needs adding.
Thanks for the code, Chuck.

It looks from the code as if your Access database has a password. Can I just leave that line out if our database does not have a password?

Also, pardon my ignorance, but I am not familiar with parameter queries in Access. All the queries I will be importing are select queries. Does that make any difference with your code?
Reply With Quote
  #6  
Old 09-19-2017, 01:19 PM
Eburnine's Avatar
Eburnine Eburnine is offline
Member
SOA
 
Join Date: Feb 2012
College: University of Waterloo
Posts: 37
Default

Put this in a VBA in the Access database to export all queries in database to excel:


Public Sub QueryToExcel()

'Declarations
Dim obj As AccessObject
Dim dbs As Object

'Set object
Set dbs = Application.CurrentData

'Loop all queries
For Each obj In dbs.AllQueries
'Transfer to spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, "C:\Folder1\Folder2\Ouput.xls", True
Next obj

End Sub
Reply With Quote
  #7  
Old 09-19-2017, 02:57 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 824
Default

Quote:
Originally Posted by Eburnine View Post
Put this in a VBA in the Access database to export all queries in database to excel:
Thanks, Eburnine. That looks like some useful code, but I really only need to import 3 queries from a particular Access database into Excel.

In other words, going the other way with only 3 queries; i.e., importing into Excel, not exporting from Access.

Do you have any code that does this?
Reply With Quote
  #8  
Old 09-19-2017, 08:16 PM
Chuck Chuck is offline
Member
SOA AAA
 
Join Date: Oct 2001
Location: Illinois
Posts: 4,518
Default

Quote:
Originally Posted by Freebird View Post
Thanks for the code, Chuck.

It looks from the code as if your Access database has a password. Can I just leave that line out if our database does not have a password?

Also, pardon my ignorance, but I am not familiar with parameter queries in Access. All the queries I will be importing are select queries. Does that make any difference with your code?
Yes, just leave the password as blank ('' I think).

A select query is fine. A parameter query is just one where you define one or more "parameters" to run the query. So if I have a query called "query_GetPolicyData" and I want to run it for a particular policy number, say "P123456" I create a parameter called PolNum (in the Design tab).

When you run the query manually, you get prompted for PolNum. When you run it in the code, you run:

Execute query_GetPolicyData "P123456"
Reply With Quote
  #9  
Old 09-19-2017, 10:47 PM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,249
Default

Quote:
Originally Posted by Freebird View Post
Thanks for the comment, Dumples.

Do you have some VBA code using CopyFromRecordset that you wouldn't mind sharing?
Not trying to be a jerk, but it would take me longer to put it here than it would take for you to google "Excel VBA CopyFromRecordSet example"
Reply With Quote
  #10  
Old 09-20-2017, 05:10 PM
Eburnine's Avatar
Eburnine Eburnine is offline
Member
SOA
 
Join Date: Feb 2012
College: University of Waterloo
Posts: 37
Default

Quote:
Originally Posted by Freebird View Post
Thanks, Eburnine. That looks like some useful code, but I really only need to import 3 queries from a particular Access database into Excel.

In other words, going the other way with only 3 queries; i.e., importing into Excel, not exporting from Access.

Do you have any code that does this?
I'm not too sure I see the difference? The goal is to export from Access into Excel, right?

You can call Access VBA from Excel VBA (https://support.microsoft.com/en-us/...icrosoft-excel or https://www.mrexcel.com/forum/excel-...excel-vba.html).

If you just want 3 queries, throw an if condition wrapper around the code and say only transfer if query name is X, Y, or Z.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 06:13 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.34424 seconds with 9 queries