I need to export data from an access query into and premade excel document. i want the access data to specific cells in the excel doument.

I have a query which creates a form depending on what number I type in. I have created a button to export the data to excel. when I click this button I want it to export and fill the data into specific excel boxes of a premade excel document. how can I do this?
CdhirstAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
that will all depend on the format of the "premade Excel document"

see the sample codes from Using Automation to Transfer Data to Microsoft Excel
0
CdhirstAuthor Commented:
i can show you the excel document if that would help ??
0
CdhirstAuthor Commented:
The excel doc look like this.. My query creates a report that looks the same as this but i cant to convert the report/query into this excel doc
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CdhirstAuthor Commented:
here is the doc
Premade-excel.xlsx
0
Rey Obrero (Capricorn1)Commented:
what is the record source of the report?
copy and paste here.
0
CdhirstAuthor Commented:
I think this is what you need?

L:\My Documents\Premade excel.xlsx
0
CdhirstAuthor Commented:
sorry I miss read
the record source from the report if found from running a query to find a specific record in a table
0
Rey Obrero (Capricorn1)Commented:
try these codes for a starter, continue the codes where i placed 'etc..


sub CopyToExcel()
dim xlObj as object, rs as DAO.recordset

set rs=currentdb.openrecordset("NameOfQuery")
if rs.eof then exit sub

set xlObj=createobject("excel.application")
      xlObj.workbooks.open "L:\My Documents\Premade excel.xlsx"
      with xlObj
           .worksheets("sheet1").select
           .range("b2").value=rs!partnumber
           .range("e2").value=rs!Rev
           .range("b6").value=rs!partnumber

         '.. etc....

         .activeworkbook.save  
      end with
          xlObj.quit
end sub
0
CdhirstAuthor Commented:
Here is the access database I have made
when u go to the switchboard click on 'ncm report' that will then open a box type in number 7605
this will then open a report. on the report I need a button that will send this report to excel in the format of the above excel document.
with the part number matching part number on the excel document and so on...
NCM-Data.accdb
0
CdhirstAuthor Commented:
I will try you code now quickly
0
CdhirstAuthor Commented:
I couldn't get it to work I am not very experienced with VBA
0
Rey Obrero (Capricorn1)Commented:
post here, which field from the query goes to which cell in the excel file.



.
0
CdhirstAuthor Commented:
ID from the query goes to NCM number
0
Rey Obrero (Capricorn1)Commented:
what about the other fields????
0
Rey Obrero (Capricorn1)Commented:
try this
Note : place the db and the excel file in the SAME FOLDER
            name of the Excel file is  Premade-excel.xlsx
NCM-Data-rev.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CdhirstAuthor Commented:
Part number goes to part number
Supplier goes to company..
I can probably work out how to get the rest in if I have a few code lines in..
I have left my computer for today and won't be back till Thursday.. Can't really match them up till I am back.
Is it possible to contact again on Thursday?
0
CdhirstAuthor Commented:
Thank I will check it out ASAP
0
CdhirstAuthor Commented:
Thank you very much that worked really well
0
CdhirstAuthor Commented:
Works really well thank you. Might have a few more problems but I will raise another question if I do
0
CdhirstAuthor Commented:
HI
I have one more question about it. the company box in my report shows as text but when in exports to excel it shows up as a number. do you know how to change this as when it goes to excel I want it to show the company name not a number. when I tried it with your version it worked fine. cant understand why mine isn't
0
Rey Obrero (Capricorn1)Commented:
did you look at the sample db i uploaded?
0
CdhirstAuthor Commented:
yes I did I managed to solve it thank you.

I was woundering if you knew how to get access to close itself automatically after a period of inactivity?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.