Macro to populate data from three tables in access 2003 into three worksheets - same workbook

route217
route217 used Ask the Experts™
on
Hi experts

I have the following partly completed vba code for access 2003 to populate data from three tables into three worksheet(s)...

How every I need to complete the vba in order for it to function correctly...I.e take the data from each of the access tables and paste it into the following worksheets Same workbook.

The range in each worksheet(s) is as follows:-
Worksheet 1: range a1:f12
Worksheet 2: range b1:g8
Worksheet 3: range a1:h14

Vba code is:

Private Sub export_data()

docmd set warnings false
docmd.transferspreadsheet acexport, 8, "xxx1","file path\file name1.xls",true
docmd.transferspreadsheet acexport, 8, "xxx2","file path\file name2.xls",true
docmd.transferspreadsheet acexport, 8, "xxx3","file path\file name3.xls",true

Dim xlapp as excel.application
Dim xlbook as excel.application
Dim xlsheet as excel.application
Dim filepath as string

Filepath ="filepath\file name1.xls" this is the final destination of the template workbook housing the worksheets.

Set xlapp = new excel.application
Xlapp.visible = false
Xlapp.displayalerts = false
Set xlbook = xlapp.workbook.open(filepath)

Not sure what goes here to populate the data into the specified range as above...



Xlbook.save
Xlbook.close
Xlapp.quit
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
just to be clear,
you have three tables,
you want to populate a workbook ,
      * table1 data goes to Sheet 1 range a1:f12
      * table2 data goes to Sheet 2 range b1:g8
      * table3 data goes to Sheet 3 range a1:h14

 is this correct ?

are you sure that records from any of the tables will not exceed the ranges you specified?

does the excel file already exists?

what is the name of the excel file ?
do we include the Field Names from table as the Column headers of the excel file?
route217Junior

Author

Commented:
Yes and apologies for the late reply back...
And no to the last question asked..
route217Junior

Author

Commented:
Name of excel file- template is final data.xls I.e excel workbook
Where the final results should end up.

Thanks
Top Expert 2016

Commented:
Open the excel file  "final data.xls"

1. select Sheet 1
    - select the cells  a1:f12
    - type in the name box  ( > the drop down box on top Column A)
       MyRange1

1. select Sheet 1
    - select the cells  a1:f12
    - type in the name box  ( > the drop down box on top Column A)
       MyRange1
2. select Sheet 2
    - select the cells  b1:g8
    - type in the name box  ( > the drop down box on top Column A)
       MyRange2

3. select Sheet 3
    - select the cells  a1:h14
    - type in the name box  ( > the drop down box on top Column A)
       MyRange3

save the excel file and close

run this codes


docmd.transferspreadsheet acexport, 8, "xxx1","file path\final data.xls",false,"MyRange1"
docmd.transferspreadsheet acexport, 8, "xxx2","file path\final data.xls",false,"MyRange2"
docmd.transferspreadsheet acexport, 8, "xxx3","file path\final data.xls",false,"MyRange3"
route217Junior

Author

Commented:
Hi cap1

Firstly, thanks for the feedback but confused... On how to fully complete the vba code required.
route217Junior

Author

Commented:
Also my mistake in reading your question the tree table are in three different worksheets same workbook...
Top Expert 2016

Commented:
when responding, copy each  question and place your answer below the question.. ok

do you have an access application right now ?  
are the tables in  your access application ?
if they are, what are the names of the tables?
route217Junior

Author

Commented:
Firstly, apologies....

I have the access application and the tables are in the access db and finally the names of the three tablets are:-

1. Sa_table1
2. Mid_sa_table1
3. Final_sa_table1

The results from the three above access tables needs to be populated into the three worksheets.

Thanks once again
Top Expert 2016

Commented:
did you follow my instructions 1 to 3 i posted above at http:#a37846326  about the excel file ?
route217Junior

Author

Commented:
Hi Cap1

Yes I did and I beileve I have made a complete mess of the situation...I was using the code I had and incorporating your steps into my first attempt....

But no luck.....

Many thanks for the excellent feedback.
route217Junior

Author

Commented:
Hi Cap1

I am running the macro from the access -using a sun form...

Thanks
route217Junior

Author

Commented:
Hi Cap1 - this was my attempt.....

Vba code is:

Private Sub export_data()

docmd set warnings false
docmd.transferspreadsheet acexport, 8, "xxx1","\\file path\final data.xls",true
docmd.transferspreadsheet acexport, 8, "xxx2","\\file path\final data.xls",true
docmd.transferspreadsheet acexport, 8, "xxx3","\\file path\final data.xls",true

Dim xlapp as excel.application
Dim xlbook as excel.application
Dim xlsheet as excel.application
Dim filepath as string
Dim my range as string

Filepath ="\\filepath\final data.xls"

Set xlapp = new excel.application
Xlapp.visible = false
Xlapp.displayalerts = false
Set xlbook = xlapp.workbook.open(filepath)
Set xlsheet = xlbook.worksheet(1).range"a1:f12"
Set xlsheet = xlbook.worksheet(2).range"b2:g10"
Set xlsheet = xlbook.worksheet(3).range"a2:h11"

Docmd.echo true,"exporting, please wait......."

Xlbook.save
Xlbook.close
Xlapp.quit
NorieAnalyst Assistant

Commented:
Why are you opening Excel?

The code and instructions capricorn1 provided should work, provided you follow them, and you shouldn't need to open Excel.
route217Junior

Author

Commented:
Hi imnorie

Maybe I did not explain the question clearly and apologies...

What I am trying to do is run the vba from a subform in acces db and paste the data held in the three tables into a template workbook in excel....

Thanks for the feedback
Top Expert 2016
Commented:
first correct the location of the excel file

              "\\file path\" >> does not look right

where exactly is the excel file located ?

assuming the file is at  "C:\NameOfFolder\final data.xls"


use this codes

Private Sub export_data()

docmd.transferspreadsheet acexport, 8, "xxx1", "C:\NameOfFolder\final data.xls",false,"MyRange1"

docmd.transferspreadsheet acexport, 8, "xxx2", "C:\NameOfFolder\final data.xls",false,"MyRange2"

docmd.transferspreadsheet acexport, 8, "xxx3", "C:\NameOfFolder\final data.xls",false,"MyRange3"
 
end sub
NorieAnalyst Assistant

Commented:
If you follow the instructions from capricorn you can do that with simple DoCmd.TransferSpreadsheet.

The most important part, as far as I can see, is naming the ranges where the data is to go in the Excel workbook.
route217Junior

Author

Commented:
Hi Cap1

Many thanks for the excellent feedback...and apologies fr being a pain....

So if am reading the question correctly then I need to define the ranges in the excel worksheets/workbook as myrange1,myrange2 etc...with the specified ranges and run the above code as provided by you?????

Thanks
Top Expert 2016

Commented:
omg.. are you reading my instructions ?


did you follow my instructions 1 to 3 i posted above at http:#a37846326  about the excel file ?
route217Junior

Author

Commented:
Many thanks works fine...

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