Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access 2007 report to Excel
I have a complex report with grouping, sub reports and subtotals that I would
like to export to an Excel Spreadsheet. Access has never been able to export the report to Excel satisfactorily, and I always exported it as a text file and then imported the text file to excel, using the text to columns wizard. I don't mind losing the subtotals, but I want to retain the grouping and columns in the report. Having to go through the import process every time with the wizard is a pain, the columns of the report are standard but the rows vary. I used to be familiar with parsing files to Lotus 123 by inserting a line of characters in the first row that identified characters as text, numeric etc and identified when a new cell was to start. I have never done this in Excel without a wizard, so have no idea what an Excel parse line looks like, if such a creature exists. Ideally I would like a command that runs the report, exports it to a text file and then imports the text file to a spreadsheet. Alternatively if someone could direct me to a numpty guide to importing data to excel without using a wizard that would be nice. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access 2007 report to Excel
No Problem - I recorded a Macro that solved it.
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access 2007 report to Excel
You can write a VB code (in Excel) with reference to the Microsoft
Access objects to access the data in your Access DB. Is your data on your report from multiple tables? Faisal... On Nov 1, 11:55 am, Heather wrote: I have a complex report with grouping, sub reports and subtotals that I would like to export to an Excel Spreadsheet. Access has never been able to export the report to Excel satisfactorily, and I always exported it as a text file and then imported the text file to excel, using the text to columns wizard. I don't mind losing the subtotals, but I want to retain the grouping and columns in the report. Having to go through the import process every time with the wizard is a pain, the columns of the report are standard but the rows vary. I used to be familiar with parsing files to Lotus 123 by inserting a line of characters in the first row that identified characters as text, numeric etc and identified when a new cell was to start. I have never done this in Excel without a wizard, so have no idea what an Excel parse line looks like, if such a creature exists. Ideally I would like a command that runs the report, exports it to a text file and then imports the text file to a spreadsheet. Alternatively if someone could direct me to a numpty guide to importing data to excel without using a wizard that would be nice. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access 2007 report to Excel
You can write a VB code (in Excel) with reference to the Microsoft
Access objects to access the data in your Access DB. Is your data on your report from multiple tables? Faisal... On Nov 1, 11:55 am, Heather wrote: I have a complex report with grouping, sub reports and subtotals that I would like to export to an Excel Spreadsheet. Access has never been able to export the report to Excel satisfactorily, and I always exported it as a text file and then imported the text file to excel, using the text to columns wizard. I don't mind losing the subtotals, but I want to retain the grouping and columns in the report. Having to go through the import process every time with the wizard is a pain, the columns of the report are standard but the rows vary. I used to be familiar with parsing files to Lotus 123 by inserting a line of characters in the first row that identified characters as text, numeric etc and identified when a new cell was to start. I have never done this in Excel without a wizard, so have no idea what an Excel parse line looks like, if such a creature exists. Ideally I would like a command that runs the report, exports it to a text file and then imports the text file to a spreadsheet. Alternatively if someone could direct me to a numpty guide to importing data to excel without using a wizard that would be nice. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access 2007 report to Excel
Yes, the report is from multiple tables and has six supreports, showing the
data for each project up to the current year, the total for the cusrrent year and subtotals for each group of data. I exported the report to a text file and recorded a macro that imports the text file and strips out the blank lines. From this point I can work on the file. Do you think this excel routine could be attached to the Access code that runs the report? "Faisal..." wrote: You can write a VB code (in Excel) with reference to the Microsoft Access objects to access the data in your Access DB. Is your data on your report from multiple tables? Faisal... On Nov 1, 11:55 am, Heather wrote: I have a complex report with grouping, sub reports and subtotals that I would like to export to an Excel Spreadsheet. Access has never been able to export the report to Excel satisfactorily, and I always exported it as a text file and then imported the text file to excel, using the text to columns wizard. I don't mind losing the subtotals, but I want to retain the grouping and columns in the report. Having to go through the import process every time with the wizard is a pain, the columns of the report are standard but the rows vary. I used to be familiar with parsing files to Lotus 123 by inserting a line of characters in the first row that identified characters as text, numeric etc and identified when a new cell was to start. I have never done this in Excel without a wizard, so have no idea what an Excel parse line looks like, if such a creature exists. Ideally I would like a command that runs the report, exports it to a text file and then imports the text file to a spreadsheet. Alternatively if someone could direct me to a numpty guide to importing data to excel without using a wizard that would be nice. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access 2007 report to Excel
If you want to run it from access then use the following vb codes
(update appropriately) to export to a text file: DoCmd.OutputTo acOutputReport, "REPORT NAME", "Text Files (*.txt)", "c: \YOURFOLDER\OUTPUT.txt" If you want to run it further as to import it to your spreadsheet, then include reference to Microsoft Excel Object (ToolReference) The add something like this: Dim Excel_File As Excel.Workbook Set Excel_File = Excel.Workbooks.Open("c:\YOURFOLDER\OUTPUT.txt") Then you can add excel_vb. Example: MsgBox (Excel_File.Sheets(1).Cells(5, 2)) On 5 Nov, 08:21, Heather wrote: Yes, the report is from multiple tables and has six supreports, showing the data for each project up to the current year, the total for the cusrrent year and subtotals for each group of data. I exported the report to a text file and recorded a macro that imports the text file and strips out the blank lines. From this point I can work on the file. Do you think this excel routine could be attached to the Access code that runs the report? "Faisal..." wrote: You can write a VB code (in Excel) with reference to the Microsoft Access objects to access the data in your Access DB. Is your data on your report from multiple tables? Faisal... On Nov 1, 11:55 am, Heather wrote: I have a complex report with grouping, sub reports and subtotals that I would like to export to an Excel Spreadsheet. Access has never been able to export the report to Excel satisfactorily, and I always exported it as a text file and then imported the text file to excel, using the text to columns wizard. I don't mind losing the subtotals, but I want to retain the grouping and columns in the report. Having to go through the import process every time with the wizard is a pain, the columns of the report are standard but the rows vary. I used to be familiar with parsing files to Lotus 123 by inserting a line of characters in the first row that identified characters as text, numeric etc and identified when a new cell was to start. I have never done this in Excel without a wizard, so have no idea what an Excel parse line looks like, if such a creature exists. Ideally I would like a command that runs the report, exports it to a text file and then imports the text file to a spreadsheet. Alternatively if someone could direct me to a numpty guide to importing data to excel without using a wizard that would be nice.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column widths change with each new import from Access 2007 | New Users to Excel | |||
Import data from Access 2007 | Excel Discussion (Misc queries) | |||
Import Access data into Excel 2007 | Excel Discussion (Misc queries) | |||
Import Access query to Excel 2007 | Excel Discussion (Misc queries) |