Consulting

Results 1 to 11 of 11

Thread: Solved: copy Access data into EXCEL ?

  1. #1
    VBAX Regular
    Joined
    Jul 2008
    Posts
    38
    Location

    Solved: copy Access data into EXCEL ?

    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.

    [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

    [/VBA]

    THANK YOU so much.
    Dave

  2. #2
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    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.
    [vba]aApp.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    "Raw Data_Blogs_2008", Filepath, True
    [/vba]
    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).
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  3. #3
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    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).

    [vba]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[/vba]

    HTH
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  4. #4
    VBAX Regular
    Joined
    Jul 2008
    Posts
    38
    Location
    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.

    [vba]
    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

    [/vba]

    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

  5. #5
    VBAX Regular
    Joined
    Jul 2008
    Posts
    38
    Location
    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!

  6. #6
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    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
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  7. #7
    VBAX Contributor
    Joined
    Jun 2007
    Posts
    150
    Location
    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 [].

  8. #8
    VBAX Regular
    Joined
    Jul 2008
    Posts
    38
    Location
    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

  9. #9
    VBAX Regular
    Joined
    Jul 2008
    Posts
    38
    Location
    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

  10. #10
    VBAX Contributor
    Joined
    Jun 2007
    Posts
    150
    Location
    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.

    [vba]'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\"
    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
    [/vba]

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

    [vba]'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\"
    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[/vba]

    Hope that helps!

  11. #11
    VBAX Contributor DarkSprout's Avatar
    Joined
    Oct 2007
    Location
    Essex, England
    Posts
    144
    Location
    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
    [vba]
    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
    [/vba]
    =|)arkSprout=
    VBA | VBScript | C# Programmer

    "Give a person a fish and you feed them for a day; teach a person to use the Internet and they won't bother you for weeks."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •