Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Excel to Access- Access to excel

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Servicenet
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a Macro in Excel that trims some data in the spreadsheet, and I want this Excel macro to open another macro in Access, paste the data in the Access table, run the access macro and from that macro, that is a query, copy the records of that query and paste them in the same workbook but in a different spreadsheet.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Brampton
    Posts
    328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To access another Office application from Excel, it requires to have a basic knowledge of the other application (Word, Access, Powerpoint, Outlook)objects, methods and properties. There are many sites explaining object models in MS Office, only time to have to spend.
    With regard to what you need specifically to do, I would suggest to implement one Excel macro from where to access MS Access. Take it as an example that could open the road for your specific tasks to be performed:

    Option Explicit
    Dim appACC As Access.Application

    Sub XLAccess()
    Set appACC = CreateObject("Access.Application")
    With appACC
    .OpenCurrentDatabase "D:db2.mdb"
    .DoCmd.TransferSpreadsheet _
    acImport, _
    acSpreadsheetTypeExcel9, _
    "tblEmployee", _
    "D:Employee.xls", _
    True, _
    "A1:D3"
    .DoCmd.TransferSpreadsheet _
    acExport, _
    acSpreadsheetTypeExcel9, _
    "tblEmployee", _
    "D:Employee.xls", _
    True
    .CloseCurrentDatabase
    End With
    Set appACC = Nothing
    End Sub

    Make use of Access VBE Help menu to get specific information about DoCmd object and TransferSpreadsheet method. In order to use the same workbook for both import and export and data, I assume that you have at least Excel 97, otherwise you are going to overwrite the data you imported in the first instance, when you proceed to the export of the query data from Access to Excel.

  3. #3
    New Member
    Join Date
    Dec 2003
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel to Access- Access to excel

    Conni,
    you suggestion below works great!!!!!!! However, since I want to give the user the chance to save a template on their C-Drive, how can I manipulate the code to import the CURRENT spreadsheet. (Which is open and hosts the Macro). Otherwise, my users have to open the spreadsheet on their server and you know how some people like to work on their own C-Drive rather then accessing server hosted files. Any idea or help is appreciated.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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