Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
export access query to excel
- Thread starter Brettgons
- Start date
WithCorrectionsBold said:
Hopefully that'll help for anyone else attempting to assist.
![[wink]](/data/assets/smilies/wink.gif "[wink] [wink]")
There are a couple of different ways to handle this. If you are going to use the same Workbook/Worksheet/Access Query combination each time, you could set up the Excel workbook to import the data from Access upon opening the workbook.
Or, you can do this within Access, using VBA.
What are your preferences?
--
"If to err is human, then I must be some kind of human!" -Me
- Thread starter
- #3
I am not a VBA person at all. As it was explained to me by the person i am trying to help out. The Excel spread sheet will always be the same name but the exported data would go to a specified sheet. I want to be able to highlight the data on the access table and have it export to that sheet on the excel spread sheet with a click of a button.
Is there a way after the data is exported to remove the highlighted data on the access table as well?
thanks for any help you can give me
Is there a way after the data is exported to remove the highlighted data on the access table as well?
thanks for any help you can give me
- Thread starter
- #5
Assuming you already have the data in the query in Access, you can put some code behind a button or other event. I don't have the time at the moment to post an example, but I'm sure someone can do that, or at least point you to the appropriate help files. I'll check back this afternoon (GMT - 5 hrs)
--
"If to err is human, then I must be some kind of human!" -Me
--
"If to err is human, then I must be some kind of human!" -Me
- Thread starter
- #7
I was able to create a button on the table and get the highlighted data to export to a spread sheet. The only things that i need assistance on is making sure the data goes on a pre determined sheet within the excel spread sheet and deleating the highlighted data.
You might be coming at this from the wrong way. If you are going to show the data in an excel workbook, on a specific sheet, starting at a specific cell, you should be using excel to get your information.
1st. Create the query in Access
2nd. From Excel, Data Menu - Import External Data - New Data Base query.
3rd. From the Choose Data Source screen - select MS Access Database. Find your MS Access Data base. Locate your query and select the fields you want to import. As you go through the wizzard you will have the option to save the query (and name it). Save the excel query. I usually put it where my spreadsheet is saved. The click Finish.
You will then be asked where you want to put the data.
Select the cell in Excel where you want the data to start. Only select the starting cell (upper right of where the data goes)
Your data will be put where you want it. You will now always be connected to the ACCESS DB. On the Excel View Menu - Tool Bars - select External Data. This will place a small toolbar in Excel. You can now click one button on that tool bar and update your data. Or set it to update the data automatically everytime you open the spreadsheet by using the External Data Range Properties button on the new tool bar.
This sounds complicated but it actually is easy and there is no code to write. The wizzard takes you through it.
Hope this helps.
1st. Create the query in Access
2nd. From Excel, Data Menu - Import External Data - New Data Base query.
3rd. From the Choose Data Source screen - select MS Access Database. Find your MS Access Data base. Locate your query and select the fields you want to import. As you go through the wizzard you will have the option to save the query (and name it). Save the excel query. I usually put it where my spreadsheet is saved. The click Finish.
You will then be asked where you want to put the data.
Select the cell in Excel where you want the data to start. Only select the starting cell (upper right of where the data goes)
Your data will be put where you want it. You will now always be connected to the ACCESS DB. On the Excel View Menu - Tool Bars - select External Data. This will place a small toolbar in Excel. You can now click one button on that tool bar and update your data. Or set it to update the data automatically everytime you open the spreadsheet by using the External Data Range Properties button on the new tool bar.
This sounds complicated but it actually is easy and there is no code to write. The wizzard takes you through it.
Hope this helps.
- Thread starter
- #9
Brettgons,
puforee's instructions will likely be the easiest and possibly most efficient way of accomplishing your end goal. Let us know how it goes, and if you run into any problems.
--
"If to err is human, then I must be some kind of human!" -Me
puforee's instructions will likely be the easiest and possibly most efficient way of accomplishing your end goal. Let us know how it goes, and if you run into any problems.
--
"If to err is human, then I must be some kind of human!" -Me
In Access VBA 2007 by Teresa Henning et al, Chapter 16 has examples of code that creates an Excel sheet and loops through every column in the worksheet. There is also a section in Ch 16 that deals with Using transferspreadsheet.
Chapter 19 in the Viesca book, Building Microsoft Acess applications published by Microsoft Press also discusses Exporting data to Excel.
Robert
Chapter 19 in the Viesca book, Building Microsoft Acess applications published by Microsoft Press also discusses Exporting data to Excel.
Robert
- Status
Similar threads
