Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Question Unanswered: Export To specific cells in Excel

    Hello All,

    I am new to the whole DB world and I have been trying to get my Access DB to export specific data from a query to an existing Excel Form. I need to run a query to have the data exported from my access query and placed into specific cells in an exsisting excel spreadsheet.

    How do I do this, I have tried the export macro but that just replaces the whole spreadsheet.

    Please help. Also, Like I said I am new to this..so if you have crayons...please use them to explain.

    Thanks in advance

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You'll have to use automation to do this (ie. instantiate Excel in Access and control the instance of Excel from your Access Application).

    To do so, you first have to create a reference to the Microsoft Excel 11.0 Object Library (11.0 is for Office 2003, it can chage according to the version of Office).

    To create a reference, open a module, in the Tools menu select References, check the desired reference into the list and click OK.

    Here is a short example explaining how it works:

    Dim AppXL As Excel.Application
    Dim BookXL As Excel.Workbook
    Dim FilePath As String

    Set AppXL = New Excel.Application
    FilePath = "c:\myfile.xls"
    With AppXL
    .Workbooks.Open FileName:=FilePath
    .Sheets("MySheet").Select
    .Rows("1:1").Select
    ' do whatever you want to do with the selected sheet here
    .ActiveWorkbook.Save
    .ActiveWorkbook.Close
    .Quit
    End With
    Set AppXL = Nothing

    Have a nice day!

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    One thing that I do and I find quite useful is the CopyFromRecordset method. This allows you to load a recordset with the data you want then paste it (unformatted) into a specific cell on the worksheet.

    It's a fast way to dump data into a spreadsheet. If need be then you can have code go back and do the formatting for you.

    Just thought I'd throw that out there. Good luck.

    C

Posting Permissions

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