Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Sep 2006
    Posts
    162

    Unanswered: Help w/ VBA exporting query to spreadsheet

    This is what I have so far. I'm wanting to export the query results to a spreadsheet.

    the query i'm wanting to export is the one that is open. DA to ATL Compare.
    I select the cell i'm starting at. ATM I'm only setting the cell to "My Value"
    How do I make my value the query results?

    Keep in mind I'm new to vba and learning as I go.

    TIA

    PHP Code:
    Private Sub Command1_Click()

    DoCmd.OpenQuery "DA to ATL Compare"acViewNormal

    Dim obj 
    As Object
    Set obj 
    GetObject("C:\Documents and Settings\Jeremiah\My Documents\ATL Payroll.xls")

    obj.Application.Visible True
    obj
    .Windows(1).Visible True

    obj
    .Application.Goto "DA_ATL"
    obj.Application.ActiveCell.FormulaR1C1 "MyValue"

    End Sub 

  2. #2
    Join Date
    Sep 2006
    Posts
    162
    I've got this and it gives me an error at teh set res =

    The error os 3141

    The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

    PHP Code:
    Private Sub Command1_Click()

    Set db Application.CurrentDb
    Set rs 
    db.OpenRecordset("SELECT Phone, FROM DA Tech")


    'DoCmd.OpenQuery "DA to ATL Compare", acViewNormal

    Dim obj As Object
    Set obj = GetObject("C:\Documents and Settings\Jeremiah\My Documents\Atlanta\ATL Payroll.xls")

    obj.Application.Visible = True
    obj.Windows(1).Visible = True

    obj.Application.Goto "DA_ATL"
    obj.Application.ActiveCell.FormulaR1C1 = rs



    End Sub 

  3. #3
    Join Date
    Dec 2006
    Posts
    53
    For a small lesson on how to access the data in tables in VBA, here is a snippet of some code from one of my databases:

    Code:
        Dim db As DAO.Database
        Dim rs1 As DAO.Recordset
        Dim rs2 As DAO.Recordset
        Dim strSQL1 As String
        Dim strSQL2 As String
        
        strSQL1 = "SELECT survey.SurveyID, survey.Status FROM survey ORDER BY survey.SurveyID;"
        strSQL2 = "SELECT ImportedData.SurveyID, ImportedData.Received, ImportedData.Invalid FROM ImportedData;"
        
        Set db = CurrentDb()
        Set rs1 = db.OpenRecordset(strSQL1)
        Set rs2 = db.OpenRecordset(strSQL2)
        rs1.MoveFirst
        rs2.MoveFirst
    When I do this, it's usually so I can cycle through something in my table using a For loop, which is what follows this code in my module. I start in the Access query section by adding the fields I want to work with to a new query (you can include sorts and filters). Then, in query design mode, I click View - SQL View. I copy the entire SQL and paste it as you see in the code, to a string (like strSQL1).

    Two things with your code. First, what it the "Tech" that follows the "DA" table name? Second, you certainly need to end your SQL string with a semicolon ( ; ).

    However, I do not believe you are going about this correctly. Exporting a query to an Excel spreadsheet should be much simpler than this. Actually, you could avoid VBA altogether in this case and just create a macro.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Here's the cause of your error:

    Code:
    Set rs = db.OpenRecordset("SELECT Phone, FROM DA Tech")
    Should read

    Code:
    Set rs = db.OpenRecordset("SELECT Phone FROM [DA Tech]")
    You had an unneeded comma. The brackets around the table name
    are there because you put a space in the table name. Try to stay away
    from spaces in table and query names. Use an underscore instead.

    Another option you have is to build a query from within excel, and have the data refresh every time you open the workbook. That way, you don't have to remember to run a macro in Access to get the most up to date info into excel.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Sep 2006
    Posts
    162
    Thanks for the info guys, I'll test it out asap. The reason I don't have it linked and refresh each time u open is cause i'm wanting to add info to excel file, save it with a unique name then email it.
    I wanna automate this all. So I'm doin it one piece at a time.

    Also I gotta get away form putting spaces in my table names. Noob mistake, but what can I say, I'm a noob :P

    Also if the field I'm pulling from DA Tech has a space in it do I use the [] there too?

    Thanks busy much appreciated.
    Last edited by Jay59; 12-28-06 at 12:20.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Yeah, use the [] around field names with spaces, too.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Sep 2006
    Posts
    162
    Also the reason i'm not using a macro is because I need to export to a particular cell or range of cells.

  8. #8
    Join Date
    Sep 2006
    Posts
    162
    One more question. Lets say I want to select all records in a query. Would I do it the same way. Also the last line is giving me error now. Do i need to recall rs in a differant way?

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Did you look at the TransferSpreadsheet action? It allows you to export to a range.
    Inspiration Through Fermentation

  10. #10
    Join Date
    Sep 2006
    Posts
    162
    The range will vary. I need to select cell and paste the query results because the results can very. Might be 80 then might be 100.

    Changes week to week.

  11. #11
    Join Date
    Sep 2006
    Posts
    162
    Quoted from help file.

    Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

  12. #12
    Join Date
    Dec 2006
    Posts
    53
    The transferSpreadsheet action can be called in VBA, which is great because you could then throw in a prompt to get input from the user, or some programming to determine which range you want to get.

  13. #13
    Join Date
    Sep 2006
    Posts
    162
    According to help file you can't export to a specified range.


    Set db = Application.CurrentDb
    Set rs = db.OpenRecordset("SELECT Phone FROM [DA Tech]")
    obj.Application.ActiveCell.FormulaR1C1 = rs

    shows bug on the third line

  14. #14
    Join Date
    Sep 2006
    Posts
    162
    Welch: How do you suggest I do it. I'll outline what I want to happen.

    Open a premade generic excel document(Payroll_we_.xls) and insert query results into a sheet a starting specified cell. The range will vary as the data will vary week to week.
    This will happen with 4 different queries and 4 different sheets.
    Save the excel sheet as Payroll_we_1/1/2007.xls The date will come from a field located in the main form where a command button will execute this macro.

    Keep in mind I'm just now learning VBA, but I'm willing to learn anything.

    TIA

  15. #15
    Join Date
    Dec 2006
    Posts
    53
    The code would belong in the OnClick event of the button. I am working on the VBA code that would allow for different names of the spreadsheets and queries, but you are right, you cannot export to a specified range. The only other way I can think of will be to drop the data cell-by-cell into the spreadsheet.

    Perhaps you could rethink your strategy? What's the special formatting you want in the spreadsheet and could you get by without using Excel, keeping it all in Access?

    Otherwise, I'll need to know many more specifics to get some code written, like where is the data going in the spreadsheet and what are the criteria for where it goes?

Posting Permissions

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