Export data from MS Access table to MS Excel Template workbook

route217
route217 used Ask the Experts™
on
Hi Experts

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"
range b10:g17
then

open Access table Banana and paste the data into template final and worksheet "banana".
range b10:f17

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)

xlBook.Save

xlBook.Close
xlApp.Quit

Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing

DoCmd.Close acForm, "Subform - Fruit Menu"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Route217,

I thought the answer you just accepted would resolve your issue.  Is the issue that you just don't understand how to implement that?
route217Junior

Author

Commented:
Yes and I tried it and it did not work as I plan it to,

Need further assistance please....
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

route217Junior

Author

Commented:
Hi Cap

I tried the method you posted and no luck.

The export do cmd creates a new workbook and does not paste the data from
The access table into the template in excel..

Understood the method - but no luck on my part.

Thanks
Top Expert 2016

Commented:
upload a copy of your db and the excel file..
route217Junior

Author

Commented:
Apologies cannot confidential data - really sorry.

And do not have the access to do so.

Thanks
route217Junior

Author

Commented:
The alternative is to format the xlsheet by moving the data to
The correct row and column and apply the correct headers
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I agree with C1, post a "sample" database with sanitized data, so you are not posting any proprietary or personal information here.

All we really need is the table(s) actually just need a subset of the data, queries, and forms that you want to use to implement the solution.
route217Junior

Author

Commented:
Hi experts

Give me an hour and I'll post a sample db and .xls file
route217Junior

Author

Commented:
Hi Experts

Please find attached sample MS Acces db and Ms excel file.....

I need the data from the 3 Access tables to be populated across to the three tables as shown in the MS Excel file...

Assume the file path of the excel file is: C:\Documents and Settings\All Users\Documents\microsoft\Sample_File.xls

so the table Sample_Data_1 gets populated across to MS Excel worksheet Records_1 C11:F14 and so on...

In the original file I have a sub form which runs the macro.....
Sample-Data-.mdb
Sample-File.xls
route217Junior

Author

Commented:
Hi Experts

Did the attached file assist? or help?

thanks
Top Expert 2016
Commented:
place this two files in the SAME FOLDER, then open the db and click on the command button
 in the form

then open the excel file
Sample-Data-rev.mdb
Sample-File.xls
route217Junior

Author

Commented:
Hi Cap1

Firstly, thanks for the excellent feedback....just one quick question how do you change the file path of the excel workbook?

thanks
route217Junior

Author

Commented:
Hi Cap1

Worked that one out....just another question say if the excel file and the mdb are located in two different folders would the macro still work...

It works 100% - prefect...

MANY THANKS
Top Expert 2016

Commented:
yes, just change the path to the excel file.
route217Junior

Author

Commented:
Apologies but what part of the code?

Thanks
Top Expert 2016

Commented:
xlPath="C:\Documents and Settings\All Users\Documents\microsoft\"
route217Junior

Author

Commented:
Hi Cap1

Can you shed any light of the following please:-

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27680984.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial