Results 1 to 5 of 5

Thread: Access to Excel

  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Red face Unanswered: Access to Excel

    Can anyone help with this?

    I have an Excel spreadsheet template that I want to have populated with information from MS Access. I want the data to come from the tables and queries. Any examples of how to do this or sites to visit which explain in detail???

    THANKS

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's pretty straightforward depending on what you want to do. Design your query in access then use import data.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Well, I want this to be an automatic process for the user.

    If they are in Access and the select a button that I have to export to Excel.. that is what I need to know, how to do that step.. Right now, if I export to Excel it just puts the data in table format in Excel...

    I want to have it open Excel and place selected data on selected cells in the Excel Spreadsheet...

  4. #4
    Join Date
    Sep 2004
    Posts
    36

    Access to Excel

    You are probably looking for something like this.....

    Create a table (tbltest) with the fields (field1,field2,field3) and put this code under a button
    Obs MS excel object lib. must be selected under tools-references
    To format the excel sheet as it should be... you can learn about how to by

    Hint!
    Go to excel - record a macro - do stuff - end record
    click alt+11 and go to module... there is the vba code for what you just did..

    Just step to the code and modify as you see fit....


    Dim objXL As Object
    Dim objActiveWkb As Object
    Dim db As DAO.Database 'Might have to remove the DAO.
    Dim rs As DAO.Recordset 'Might have to remove the DAO.
    Dim Row As Integer

    Row = 3 'This is a counter for calculations

    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True
    objXL.Application.workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)

    With objActiveWkb
    'Heading for meter info
    .Worksheets(1).Cells(2, 3) = "Heading1"
    .Worksheets(1).Cells(2, 4) = "Heading2"
    .Worksheets(1).Cells(2, 5) = "Heading3"

    'lookup values on meter

    'Deletes the 2 other sheets
    .Application.DisplayAlerts = False
    .Worksheets(3).Delete
    .Worksheets(2).Delete
    .Application.DisplayAlerts = True

    'Names the sheet and sett some page propertie values
    .Worksheets(1).PageSetup.Orientation = xlLandscape
    .Worksheets(1).PageSetup.RightFooter = "&D"


    While rs.EOF <> True
    .Worksheets(1).Cells(Row, 3) = rs!field1
    .Worksheets(1).Cells(Row, 4) = rs!field2
    .Worksheets(1).Cells(Row, 5) = rs!field3
    rs.MoveNext
    Row = Row + 1
    Wend
    End With


    'Save file
    'objActiveWkb.SaveAs FileName:="C:\TEST.xls"
    'Print File
    'objActiveWkb.Worksheets(1).PrintOut Copies:=1, Collate:=True

    'objActiveWkb.Close savechanges:=True

    Set objActiveWkb = Nothing
    Set objXL = Nothing
    Set Ctl = Nothing

  5. #5
    Join Date
    Dec 2003
    Posts
    20
    Does anyone know of an attachment to perform this function. I also would like to take data from an access form, table or query and export automatically into excel and use excel as a form to email and get information and have it returned. The reason I would like to go with excel is so I can lock the field that have the data in it from access then the emailed copy can not be modified.

Posting Permissions

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