INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Log In
Come Join Us!
Are you a Computer / IT professional? Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden. Students Click Here
|
VBA Visual Basic for Applications (Microsoft) FAQ
Excel How To
How can I use VBA to get my Access into Excel? by 01Patrik
Posted: 29 Apr 05
|
The following lets you define your query as a string variable, creates a connection to an Access database you define (with or without username and password), opens a recordset to the open database, returns the result of your query (including headers) to the active sheet in excel, closes and resets the recordset and connection, and autofits the column width.
Insert the code into a module in an Excel workbook of your choice. The red color indicates code you have to adjust to your settings.
CODE Sub Connect_to_Access_DB_and_execute_SQL_Query()
Dim Conn As New ADODB.Connection Dim RS As New ADODB.Recordset Dim SQL As String Dim MyDB As String Dim R As Long, C As Long Dim ConnStr As String Dim User As String, PW As String ' ' Enter your SQL Query as string, like this example SQL = "SELECT * FROM MyTable"
' ' Enter path to your Access Database as string, like ' this example MyDB = "C:\MyDB.mdb"
' ' Enter Username and password to the database ' (empty string ("") if there is no user / pw set) User = "" PW = "" ' ' Don't mess around with this code, unless you know what ' you are doing ' It defines the connectionstring, and opens a ' connection to the database stated above (MyDB)
ConnStr = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & MyDB & ";" & _ "Persist Security Info=False" Conn.Open ConnStr, User, PW ' ' This opens a recordset with the query defined as SQL, ' using the connection opened previously RS.Open SQL, Conn
' ' Inserts headers and data
Do While Not RS.EOF R = R + 1 For C = 1 To RS.Fields.Count If R = 1 Then ActiveSheet.Cells(R, C) = RS.Fields(C - 1).Name Else ActiveSheet.Cells(R, C) = RS.Fields(C - 1).Value End If Next If Not R = 1 Then RS.MoveNext Loop
' ' Closes the database and resets the connection and ' recordset variables RS.Close Conn.Close Set RS = Nothing Set Conn = Nothing
' ' Autofits the column widths ActiveSheet.Columns.AutoFit End Sub
All the variables are defined in order to get an overview of the process for people not used to ADODB connections and recordsets.
The code can easily be adjusted to fit Microsoft SQL Server, by adjusting the connection string (ConnStr).
There are (of course) other ways of achieving the same things, like using .execute on the connection. However, defining a recordset opens up the possibility to adjust the code to fit other purposes.
|
Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum |
|
|
|
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close