Solved: copy Access data into EXCEL ?

[DaveK]

Hi,

This is my 1st post. I am new to this.

I am in EXCEL VBA, and want to run an ACCESS query, obtain the results from the desired query, select all (or specific records/fields), and then
PASTE the selected data into specific EXCEL workbook and worksheet.

Then of course, I'll choose another query, and repeat the process as needed.

I can get the ACCESS database opened, select the desired query, and then I dont know how to get that data into Excel.

Very basic stuff... I know... but I can't find any examples in the forums.

Here is my example VBA. See my QUESTIONS in the VBA.


Sub Access_test()
'
' Access_test Macro
Dim aApp As Access.Application

Dim path_to_AccessDataBase As String

Dim Year_Month As String
Dim fullfilepathname As String
Dim MonthlyOpsReport_filename As String

Set aApp = CreateObject("Access.Application")

aApp.DoCmd.SetWarnings False
path_to_AccessDataBase = "C:\ADB Files\DATABASES\2008.mdb"

aApp.OpenCurrentDatabase path_to_AccessDataBase

' HERE IS WHERE I have to manually activate the just-opened Access application,
' and click the ALLOW users...
' HOW TO AVOID THIS from happening when running this program?
aApp.Visible = True

aApp.DoCmd.OpenQuery "Raw Data_Blogs_2008", acViewNormal, acEdit

' IS THE ABOVE line the proper way to 1) select the desired query,
' and 2) run the query?
' it seems to work okay, but not sure if this is the best way?
'
' HERE IS WHERE I NEED HELP...
' HOW TO SELECT from the just opened Access results from the above query?

' How to select the entire results?
' or, also, how to select only certain columns and rows?

' and then, HOW TO 'copy' the desired data, and then go back to EXCEL,
' in the workbook and worksheet and specific place on that sheet
' to PASTE this data??


Workbooks.Open filename:=Ops_Review_Data_Feed_filename

Worksheets("Blogs").Activate
' NOW HERE IS WHERE I NEED TO PASTE the data. HOW to do it?


aApp.DoCmd.SetWarnings True
aApp.Quit
Set aApp = Nothing
End Sub



THANK YOU so much.
Dave

[CreganTur]

Welcome Dave! It's always good to see new members.

There's no need to run the query to accomplish this- specifically no need to use DoCmd.RunQuery.

Access has something called the TransferSpreadsheet method. It automatically exports the results of a query into an Excel spreadsheet.
aApp.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Raw Data_Blogs_2008", Filepath, True

The value of the variable Filepath needs to be the exact filepath where you want the spreadsheet to be written. This will need to include the spreadsheet's name and the .xls extension.

If you plan on running this multiple times a day, and do not want to overwrite an existing spreadsheet, then you will need to find a way to create an unique name for every iteration of the program. There are a lot of ways to do this (I think there's a kb article on this).

[CreganTur]

I stated thinkining about your question again and realized that some of your issues could be solved by using a DAO Connection. Using a connection like this will keep you from having to manually activate the Access application.

This example was written in Access, but I did test it in Excel and it worked. You just need to be sure to set a Reference (Tools -> Reference) to the Microsoft DAO Object Library (select the latest version number you have available)

Testing the code:

C&P this code into a new module in an Excel workbook. Put your cursor anywhere in it, and press F5 (After putting in a valid filepath for writting your excel spreadsheet).

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim xlApp As Object
Dim wkb As Object
Dim rng As Object
Dim strExcelFile As String
Dim strDB As String
Dim strTable As String
Dim iCol As Integer
Dim rowsToReturn As Integer
Dim objSheet As Object

strDB = "C:\ADB Files\DATABASES\2008.mdb"
strTable = "Raw Data_Blogs_2008"
strExcelFile = 'Filepath where you want Excel spreadsheet written
'if excel file already exists delete it
If Dir(strExcelFile) <> "" Then Kill strExcelFile '<<<Will delete if file exists!
Set db = OpenDatabase(strDB) '<<<Opens database via DAO Connection
Set rst = db.OpenRecordset(strTable) '<<<Opens query recordset via DAO

'get number of records in recordset
rowsToReturn = rst.RecordCount
'set reference to Excel to make Excel visible
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = True '<<<Make False if you don't want to see data write to Excel
'set references to workbook and worksheet
Set wkb = xlApp.Workbooks.Add
Set objSheet = xlApp.ActiveWorkbook.Sheets(1)
objSheet.Activate

'write column names to the first worksheet row
For iCol = 0 To rst.Fields.count - 1
objSheet.Cells(1, iCol + 1).Value = rst.Fields(iCol).Name
Next
'specify cell range to recieve data
Set rng = objSheet.Cells(2, 1)

'copy specified number of records to worksheet
rng.CopyFromRecordset rst, rowsToReturn '<<<Gets all records in recordset
'autofit columns to make data fit
objSheet.Columns.AutoFit

'close the workbook
wkb.SaveAs FileName:=strExcelFile
wkb.Close

'quit excel and release object variables
Set objSheet = Nothing
Set wkb = Nothing
xlApp.Quit
Set xlApp = Nothing
db.Close
Set db = Nothing

HTH :thumb

[DaveK]

Hi Randy,
THANK YOU for your excellent help.

I've taken your advice and now structured my access test VBA using DAO.

It DOES WORK actually, kinda. We are definitely on the right track for this beginner. But I still have some basic help needed.

Here is the code so far, and questions to follow the code.


Sub Access_test_via_DAO()
' set reference to Microsoft DAO 3.6 Object Library
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim xlApp As Object
Dim wkb As Object
Dim rng As Object
Dim strExcelFile As String
Dim strDB As String
Dim strTable As String
Dim iCol As Integer
Dim rowsToReturn As Integer
Dim objSheet As Object
strDB = "C:\ADB Files\DATABASES\ADB_2008.mdb"

strTable = "Raw Data - Blogs" 'the exact name of the query

strExcelFile = "C:\Documents and Settings\dk\Desktop\2008.06_data_feed_TEST.xls"

Set db = OpenDatabase(strDB) '<<<Opens database via DAO Connection
Set rst = db.OpenRecordset(strTable) '<<<Opens query recordset via DAO


'get number of records in recordset
rowsToReturn = rst.RecordCount

Workbooks.Open filename:=strExcelFile
Worksheets("blogs").Activate
Set objSheet = ActiveWorkbook.Sheets("blogs")

'write column names to the first worksheet row
For iCol = 0 To rst.Fields.Count - 1
objSheet.Cells(1, iCol + 1).Value = rst.Fields(iCol).Name
Next

'specify cell range to recieve data
Set rng = objSheet.Cells(2, 1)

'copy specified number of records to worksheet

rng.CopyFromRecordset rst, rowsToReturn '<<<Gets all records in recordset

'autofit columns to make data fit
objSheet.Columns.AutoFit

ActiveWorkbook.SaveAs filename:=strExcelFile
Workbooks.Close

db.Close
Set db = Nothing


End Sub



First of all, this kinda works, except I need some help on

ActiveWorkbook.SaveAs filename:=strExcelFile

how to saveas when the file already exists, and overwrite it automatically. A simple Q, but I cant get it yet.

Also, the feed from Access db works a little, it gets the first row of names, and then the first record of data. (row 2 in the excel spreadsheet). Then its done. How to get ALLL the records.

Maybe the TransferSpreadsheet method is better if I want the entire query results. This way above maybe seems nice for getting only what I want and not the entire results. So I'm still thinking on that.

ALSO... how to specify a complete filepath name... the file name and sheet name .xls? ie
"C:\Documents and Settings\dk\Desktop\2008.06_data_feed_TEST.xls"

is a filepath for a workbook, but please edit it and show me how to have it include the worksheet name as well...so I can play with the TransferSpreadsheet method .

THANKS!
Dave

[DaveK]

I figured out how to do the SaveAs properly, so forget that Q.

Also, the query results for above has 39 records, yet rowsToReturn
gets a value of = 1. So that is why only 1 record is being written into Excel. Not sure why rowsToReturn = 1 ??

For the TransferSpreadsheet method, I still need an example of a fullpathfilename with sheetname.

THANKS!

[CreganTur]

Also, the query results for above has 39 records, yet rowsToReturn
gets a value of = 1. So that is why only 1 record is being written into Excel. Not sure why rowsToReturn = 1 ??

Your coding exactly matches mine, as far as the rowsToReturn and rng.CopyFromRecordset portions. When I run mine it outputs the entire query to an Excel spreadsheet.

The only think I can think of right now, is that it might have something to do with your query's name. It's a very big no-no to use spaces in database names, for a lot of reasons. You should either smash the words together, or use the underscore to add space between words, but never use spaces. So you should rename your Query to 'RawDataBlogs' or 'Raw_Data_Blogs'

Try that and see if it makes a difference.


I still need an example of a fullpathfilename with sheetname.

To be honest, I don't know if you can specify a sheetname with either of these methods. I've never needed to do so, and the little bit of research I did with Google came up dry :think:

[Dr.K]

I usually choose my VBA environment based on the final output.

In other words, if the final output is an Excel Workbook, I would write my macro in Excel VBA, and connect to the Access DB using ADO. However, if you are just dumping a Recordset to a spreadsheet, or you need to do a lot heavy lifting in Access, then the TransferSpreadsheet and/or CopyFromRecordset Methods from Access VBA are defintely the way to go.

Personally, I NEVER use underscores in VBA Object names, becuase this can cause problems with some Events. I had this one Workbook that used a lot of Events, and I had endless bugs until I went through and removed the underscores from all of my UserForm Objects and Sub/Function names.

However, underscores seem to work just fine for data base Object names. This will save you from typing a lot of Brackets [].

[DaveK]

OK, here is the status update. It still doesn't work.

I changed the Access query name to eliminate blank spaces... it is now:

strTable = "RawData_Blogs" 'the exact name of the query

Yet rowsToReturn is still = 1

Not sure what else to do.....???

FWIW I'm running Office 2003. Visual Basic 6.3

I also would like my VBA to be within EXCEL, since all my work is in Excel and my output is EXCEL.

Any help much appreciated.

THANKS
Dave

[DaveK]

More update news.

I got the DoCmd.TransferSpreadsheet method to work fine.

I also now understand that you do not need to specify a worksheet name in the target EXCEL file... in fact you can't specify it... as this method creates a new worksheet in the workbook and names it the same as the query name. Perfect!

I do still wish to understand why the DAO way doesnt't seem to work correctly....? So any help on that would be much appreciated!

THANKS FOR YOUR WONDERFUL HELP!

Dave

[Dr.K]

I have an Excel workbook that I use for general SQL testing. It has a lot of specialized functions in it, but here is a stripped down version that will dump the results of an SQL Query directly to a new Excel Workbook. (Note:this is a "pass-through" query, not a stored query/view/whatever...)

You should be able to modify this to create what you are looking for.

'DataBase SQl tester
Option Explicit 'Delete this line if you get "Undeclared Variable" errors
Option Private Module

'Database contstants
Private Const DB_Path As String = "\\10.2.56.24\shared\ (file://\\10.2.56.24\shared\)"
Private Const DB_Name As String = "DB.mdb"

Private Const AccDBConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DB_Path & DB_Name

Private strSQL As String
Public conAcc As Object
'

Private Sub psubTestingSQL()


Dim rst As Object

OpenAccConn

Set rst = CreateObject("ADODB.RecordSet")
rst.CursorLocation = adUseClient


strSQL = "SELECT stuff" _
& "FROM stuff" _
& "WHERE stuff" _
& "ORDER BY stuf"


rst.Open strSQL, conAcc, adOpenStatic, adLockReadOnly
Workbooks.Add
ActiveCell.CopyFromRecordset rst

rst.Close
Set rst = Nothing

conDM.Close
Set conDM = Nothing

End Sub

Private Sub OpenAccConn()
'Set up Object, open DB connection
Set conAcc = CreateObject("ADODB.Connection")

conAcc.Open AccDBConnStr

End Sub


If you need to open an Access DB that has user security, you'll have to change your connection string to this:

'DataBase SQl tester
Option Explicit 'Delete this line if you get "Undeclared Variable" errors
Option Private Module

'Database contstants
Private Const DB_Path As String = "\\10.2.56.24\shared\ (file://\\10.2.56.24\shared\)"
Private Const DB_Name As String = "DB.mdb"
Private Const MDW_Name As String = "DB.mdw"
Private Const DB_LoginID As String = "Login name"
Private Const DB_Password As String = "Password"

Private Const AccDBConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "User ID=" & DB_LoginID & ";Password=" & DB_Password _
& ";Data Source=" & DB_Path & DB_Name & ";" _
& "Jet OLEDB:System Database=" & DB_Path & MDW_Name _
& ";Persist Security Info=True"

Private strSQL As String
Public conAcc As Object

Hope that helps!

[DarkSprout]

Try this for a simpler function to export a Table or Query:

Exports an Access Data Object Into and Existing Excel Workbook
Throws an error if Sheet(NewSheetName) already exists

Sub InsertIntoExcel(PathAndFileName As String, ObjectName As String, Optional NewSheetName As String = "NewSheet")
'// Darryl S. Drury March08
Dim sql As String
'// Does File Exist?
If Len(Dir(PathAndFileName, vbDirectory + vbHidden + vbSystem)) = 0 Then
MsgBox "File:" & PathAndFileName & vbCrLf & "Doesn't Exist", 64, "File Error"
Exit Sub
End If
'// Does Object Exist?
If DCount("*", "MSysObjects", "[Name] = '" & ObjectName & "'") = 0 Then
MsgBox "Object:" & PathAndFileName & vbCrLf & "Doesn't Exist", 64, "Object Error"
Exit Sub
End If
sql = "SELECT * INTO [Excel 8.0;Database=" & PathAndFileName & "].[" & NewSheetName & "] FROM [" & ObjectName & "];"
DoCmd.RunSQL sql
End Sub