Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Access to Excel (2k)

    I have muddled through some access to word data transfers where I plop data from access to a word table. I would like to try this with Excel. Is there a way to get data to appear in a specific cell within excel. I use bookmarks to do the transfer to word and imagine the process would be similar. Is there anything different that I might need to know about?

    Thank You
    Kevin

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access to Excel (2k)

    There are several ways to accomplish a transfer to Excel. One is a simple cut and paste to a specific cell or range. Another is to use the Export option on the menu to do the trick. A third way is to use the TransferSpreadsheet command either as a macro or in VBA, though in that case, you cannot specify a specific cell in Excel. You can also do the trick using Automation from Access to populate specific cells, create charts, text boxes, or anything you can do in Excel. Finally, you could link to the Excel workbook, making it appear as a table - but in at least some situations you may not be able to update the Excel workbook. If you decide to try one of these approaches and encounter problems, please post back here and we'll try to help.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (2k)

    Thank You, I am going to try to use automation. When I do this with word, I usually create a query of the data I am going to transfer to word then with a word macro within a template grab each piece of data and plop in a table or within text using bookmarks. If I were to try this with Excel, I think(?) that I would just need to include the cell reference in the macro to drop in the data. Does this sound doable? The macro tends to be long though.

    Thanks
    Kevin
    Kevin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access to Excel (2k)

    If DoCmd.TransferSpreadsheet (to export an entire table or query to Excel) doesn't suit your purpose, you can use Automation, just like you did with Word. You can write values to specific cells, you can refer to them by their cell address. Instead of with Document objects, you are working with Workbook and Worksheet objects; cells and ranges are represented by the Range object.

    You must set a reference to the Microsoft Excel 9.0 Object Library.

    Example:

    Sub Export2XL()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet
    Dim blnStartExcel As Boolean

    On Error Resume Next

    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
    MsgBox "Cannot open Excel.", vbCritical
    Exit Sub
    End If
    blnStartExcel = True
    End If

    On Error GoTo ErrHandler

    Set xlWbk = xlApp.Workbooks.Add
    Set xlWsh = xlWbk.Worksheets(1)

    xlWsh.Range("A1") = ...
    ...

    xlWbk.SaveAs "C:ExcelTest.xls"

    ExitHandler:
    On Error Resume Next
    Set xlWsh = Nothing
    xlWbk.Close SaveChanges:=False
    Set xlWbk = Nothing
    If blnStartExcel Then
    xlApp.Quit
    End If
    Set xlApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access to Excel (2k)

    As Hans points out, you can use automation from Access, but you can also use automation from Excel to manipulate the Access object if you are more comfortable using VBA with Excel. As Hans example indicates, it is a fairly lengthy piece of code to do that trick, but we do it pretty regularly and it works quite well if you follow all of the guidelines for using Automation.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (2k)

    Thank You both

    Very helpful.

    Kevin
    Kevin

Posting Permissions

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