Excel to Access- Access to excel

[ogonzalez]

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.

 

[Corni]

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.

 

[HolgerP]

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.