Hi
Please can anyone help. For a week now I have been trying to work out how to export data from my Access report into a range of cells in Excel. I do not want the whole report exported into Excel, only data from one of the fields in the report, and I am trying to make it export only to a specified range of cells in Excel. So far nothing has worked successfully.
I have tried the DDESend function in the control source of the field in the report which I want to export. But this for some reason only sends one set of data (normally the last value on the first page of my report) to the specified cell in Excel. This function wont allow me to enter a range of cells...only one cell in the format R1C1, for 'row' and 'column'.
I also created a query which would only extract the data I needed to export, and I created a macro in Access using the 'Output To Action' option when I created the macro, and this worked really well, except there is no option to enter a range of cells in Excel for where you want the data to go. The data is only exported the first column as default. The spreadsheet I am exporting to has data already in it so this option was no good, as I need the data to be exported only to a specified range of cells in my spreadsheet.
I tried creating another macro in Access using the 'TransferSpreadsheet Action' option, but when i select 'export' as the 'Transfer type' there isn't the option to specify a range of cells in the Excel spreadsheet for the destination.
Lastly, I have tried creating some code in VBscript as an event procedure for the button of a form that is used to open the report, but I am totally new to VB Script so do not really know what I am doing - I have been merely pasting in example code extracts from the MS Help, and adjusting the names of some of the variables. But so far this has not worked.
I have managed to create a link from my query (which I mentioned earlier to extract only the required data I wanted to export) to my spreadsheet. But the next step would be to create a button or import 'wizard' in Excel to actually IMPORT from Access - so effectively doing the same thing, getting the data from my query into the spreadsheet. Ideally I'd like to have a link from my report as opposed to the query. But the main problem with all of this latter idea is that I dont know how to create an 'import wizard' in Excel.
If anyone can help at all with any of this it will be really appreciated, as well as any example code to try out.
Thank you again for reading all of this.
Matt
Please can anyone help. For a week now I have been trying to work out how to export data from my Access report into a range of cells in Excel. I do not want the whole report exported into Excel, only data from one of the fields in the report, and I am trying to make it export only to a specified range of cells in Excel. So far nothing has worked successfully.
I have tried the DDESend function in the control source of the field in the report which I want to export. But this for some reason only sends one set of data (normally the last value on the first page of my report) to the specified cell in Excel. This function wont allow me to enter a range of cells...only one cell in the format R1C1, for 'row' and 'column'.
I also created a query which would only extract the data I needed to export, and I created a macro in Access using the 'Output To Action' option when I created the macro, and this worked really well, except there is no option to enter a range of cells in Excel for where you want the data to go. The data is only exported the first column as default. The spreadsheet I am exporting to has data already in it so this option was no good, as I need the data to be exported only to a specified range of cells in my spreadsheet.
I tried creating another macro in Access using the 'TransferSpreadsheet Action' option, but when i select 'export' as the 'Transfer type' there isn't the option to specify a range of cells in the Excel spreadsheet for the destination.
Lastly, I have tried creating some code in VBscript as an event procedure for the button of a form that is used to open the report, but I am totally new to VB Script so do not really know what I am doing - I have been merely pasting in example code extracts from the MS Help, and adjusting the names of some of the variables. But so far this has not worked.
I have managed to create a link from my query (which I mentioned earlier to extract only the required data I wanted to export) to my spreadsheet. But the next step would be to create a button or import 'wizard' in Excel to actually IMPORT from Access - so effectively doing the same thing, getting the data from my query into the spreadsheet. Ideally I'd like to have a link from my report as opposed to the query. But the main problem with all of this latter idea is that I dont know how to create an 'import wizard' in Excel.
If anyone can help at all with any of this it will be really appreciated, as well as any example code to try out.
Thank you again for reading all of this.
Matt
