The macro in MS Access run from a Sub-form.....
I am currently trying to use the DoCmd.TransferSpreadsheet acExport, 8, "Apple", FilePath, True, "Apple"
to do the transfer from Access to excel but not luck the macro is creating a new workbook and pasting the data into row1 and column1...as opposed to the template....
How would you copy and paste the data from an MS Access table(s) and paste the values from the Access table into three MS excell worksheet(s).
The excel workbook template is kept on the c: drive
So i need the macro to open the access table and the excel spreadsheet and paste the data into excel and close the excel spreadsheet.
so Open Access table Apple and paste the data into template final and worksheet "apples"
open Access table Banana and paste the data into template final and worksheet "banana".
here is my vba -
Private Sub Export_Weekly_SLA___RPO_Click()
DoCmd.Echo True, "Exporting, Please Wait.........."
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim RootMIdir As String
Dim FilePath As String
RootMIdir = "C:\Output Files\Report Templates"
FilePath = RootMIdir & "\Template final" & ".xls"
DoCmd.TransferSpreadsheet acExport, 8, "Apple", FilePath, True, "Apple"
DoCmd.TransferSpreadsheet acExport, 8, "banana", FilePath, True, "Banana"
Set xlApp = New Excel.Application
xlApp.Visible = False
xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Open(FilePath)
Set xlSheet = xlBook.Worksheets(1)
Set xlSheet2 = xlBook.Worksheets(2)
Set xlSheet3 = xlBook.Worksheets(3)
Set xlSheet4 = xlBook.Worksheets(4)
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
DoCmd.Close acForm, "Subform - Fruit Menu"