Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    24

    Unanswered: Export from access to excel from a parameter query into a specific column

    Hello,
    I do not know if what I am trying to do is even possible but I figure if it is you guys would know Ok so first I am using micsoft 2007 office products, access and excel. What we are trying to do is a month over month analysis of volumes based on their catergeory. For example Billing inquiry has 8900 calls in Jan, and 7800 in Feb, we want to show each month what the volume is and how or if it is changing. We have a real pretty spreadsheet that is set up with column headings as
    Category Names: Jan: Feb:Apr: Mar: and so on, I have my query set up to run the numbers by month with parameters. What I have so far is, user clicks button, and code runs;

    Private Sub Command7_Click()
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query2", "C:\Documents and Settings\harlest\My Documents\CTM\test.xlsx"

    Dim objXLBook As Object
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("C:\Documents and Settings\harlest\My Documents\CTM\test.xlsx")
    objXLApp.Application.Visible = True

    End Sub

    Right now this works fine to run my query, ask for the parameters (month and year), copies it into excel and then opens the excel file, the user then has to manually copy the apropriate column and paste it into our spreadsheet. Not that much work but I would like to be able to automatically run the query and ask for the paratmers (month and year) then paste into our spreadsheet in the correct column based on the month parameter, if 07 was entered for example the data would be pasted into the Jul column, any suggestions on how to do this, if possible, would be great.
    If you need any more info please ask, also my work computer can not open the zipfiles on here so I can not look at any of the ones previously post

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by tharless View Post
    Right now this works fine to run my query, ask for the parameters (month and year), copies it into excel and then opens the excel file, the user then has to manually copy the apropriate column and paste it into our spreadsheet.
    I don't understand why the user should copy and paste anything as the data are already exported to a spreadsheet by the command:
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query2", "C:\Documents and Settings\harlest\My Documents\CTM\test.xlsx"
    As you know how to use Automation, you could easily send the values computed by the query directly into the Excel worksheet. There are several entries in this forum explaining how to do so. The basic principle consists in:
    a) Open a RecordSet on the Query.
    b) Loop through it (Do Until rst.Eof... rst.MoveNext... Loop).
    c) For each found value in the recordset (based on the Field name + the line number, or any other relevant set of parameters) compute the name of the cell in the worksheet where the data must be written.
    d) Write the value into the proper cell.
    e) Close the RecordSet.
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    24
    Quote Originally Posted by Sinndho View Post
    I don't understand why the user should copy and paste anything as the data are already exported to a spreadsheet by the command:
    In order to get the data into our formated spreadsheet it has to be copied and pasted, the command puts it into a temporary spreadsheet, everytime the button is clicked and the code runs it rewrites the spreadsheet and we want to keep the data stored for future analysis.

    Quote Originally Posted by Sinndho View Post
    As you know how to use Automation, you could easily send the values computed by the query directly into the Excel worksheet.
    I don't actually know much about automation or vba for that matter, I know how to use the docmd.'s but that is about all. I will search the forums for more info on automation using some of the key words you gave me. Thank you very much for the response

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Now I understand the reason for the copy/paste.

    This code:
    Code:
    Dim objXLBook As Object
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("C:\Documents and Settings\harlest\My Documents\CTM\test.xlsx")
    objXLApp.Application.Visible = True
    uses Automation: it creates an instance of Excel from Access, this is called Automation (aka COM+, DCOM, formerly OLE2, etc...).

    Once you have the object variable objXLApp initialized, you can use it to issue commands to the instance of Excel it represents; for instance, that's what you're doing when you write:
    Code:
    objXLApp.Workbooks.Open("...
    Have a nice day!

  5. #5
    Join Date
    Apr 2011
    Posts
    24
    I see, I did not really understand that code I simply googled, how to open excel using access vba and copied and pasted it, it worked so I did not give it much more though, thanks to your explaination I think I can play around with that code and hopefully get my result, hopefully. Thanks Again

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    If you do not know which commands use, create a corresponding macro's in Excel using the Macro Recorder, then open the code of the macro produced by the Recorder: you can usually use it in your Access process to issue the equivalent commands to the instance of Excel. Here is a macro produced by the Recorder in Excel:
    Code:
    Sub Macro2()
    '
    ' Macro2 Macro
    ' Macro recorded on 08/31/2011 by Sinndho
    '
    
    '
        Sheets("Sheet1").Select
        Range("E2").Select
        ActiveCell.FormulaR1C1 = "8/31/2011"
        Range("E2").Select
        Selection.Font.Bold = True
    End Sub
    To perform the same actions from Access (I suppose here that objXLApp and objXLBook are already initialized), you use:
    Code:
    With objXLApp
        .Sheets("Sheet1").Select
        .Range("E2").Select
        .ActiveCell.FormulaR1C1 = "8/31/2011"
        .Range("E2").Select
        .Selection.Font.Bold = True
    End With
    Have a nice day!

Posting Permissions

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