Exporting data from Access to Excel

[BarkersIT]

Hello everyone,

I have been given an excel document which is an official company document created in excel.

I have a database which will populate the excel form by using the TransferSheet(acExport, 8, table name, filename, false, range) command in VB.

Part of the excel sheet has a table with four columns. The first column is made up of only the column A and has values from 1 to 10, The second column is made up from only the column B and is the first column populated from the database, the third column is made up of a merge from C to L and is the second column populated from the table, the fourth column is made up of a merge from M and N and is the last column populated from the database.

I was under the impression that by merging cells they became one cell. Therefore the range I have given in the VB command is B32:N41. Which spans what look like 3 cells.

When i run the command the first column made up of B is correct. The second column made up of the C to L merge is also correct but the last column made up of M and N is empty. However, if you click over the second cell at D you can see that the value that should be in the M and N merge is infact here. It says sum = 200 down where you can see numlock toggle if you keep pressing numlock. These are the correct values as I can go into the access table and look them up.

How can I get the value that is going to the cell in the D column to go to the M and N merged cell?

Thanks

[matthewspatrick]

Just one of the reasons I try to never, ever merge cells unless someone basically orders me to do it :whip

In your case, rather than trying TransferSpreadsheet, you may be happier opening three ADO or DAO recordsets, one for each column, and then using Excel's CopyFromRecordset method of the Range object to paste each column in turn into the worksheet.

[BarkersIT]

I thought there would be a better way to do it but basically I have been given this "official" document and been told to make it happen.

I will give your method a go. Thanks for the advice.

[geekgirlau]

You could also try just removing the formatting (or even just the merge) prior to importing the data.

[BarkersIT]

Which References do I need to add to be able to use the CopyFromRecordset function?

[Charlize]

Or export you data (or report) in Access to a delimited txt-file. From within Acces open a certain prepared template workbook where a macro in workbook_open event performs the import by calling a macro in a module (this template has a signature file which prevents that security warning about macros to pop up) ?

But first try to unmerge the cells where you want the data to be imported. It will make your life easier.

Charlize

[matthewspatrick]

CopyFromRecordset is a method of Excel's Range object. So, you would need a reference to the Excel library if you are using early binding.