AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Friday, July 07, 2006

Export to Excel range

Ken Snell, Access MVP, has made some investigations on exporting data from Access to Excel spreadsheet, specifying range argument. He was very kind and allowed me to post results here. Thank you Ken!

BTW – Access help states that this Range argument is only for importing, and for export it will fail. Thanks to Bill Mosca, Access MVP, who mentioned this in our discussin!

Ok, here a story from Ken:

Having read some posts in newsgroups about using the Range argument for exporting queries/tables to EXCEL file, I decided to do some testing today to figure out what actually works and what doesn't work when using this argument (note that this use is an undocumented feature in ACCESS).
Here are the results of my tests for others' info/entertainment.

EXCEL FILE DOES NOT ALREADY EXIST
If the EXCEL file will be created by TransferSpreadsheet, the Range argument can be used to create a range in the new file that describes the cells that contain the exported data on the worksheet. This Range argument also is used to name the worksheet onto which the exported data are written. This overrides the normal operation of TransferSpreadsheet, which is to name the worksheet using the name of the table or query being exported. For example, this action:
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "QueryName", _

"C:\Test.xls",, "MyName"

will create the file "C:\Test.xls" and the data will be written onto the first worksheet, which will be named MyName (the Worksheet.Name property, not the Worksheet.CodeName property); and the cells into which the data are written will be a Range named MyName in the new file. This range will include the field names that are exported as the first row of data, and the range will begin in cell A1.

EXCEL FILE ALREADY EXISTS
The Range argument can be used to identify the actual Range into which the exported data are written. TransferSpreadsheet ignores worksheet names when looking for the Range in the workbook file. It looks specifically for a defined Range of cells.
However, it is in this situation where I believe many posters have run into problems with this undocumented feature.
If the Range exists (cell range, that is) AND if that range encompasses more than a single cell (at least two cells), the data are exported to that range of cells. If the number of records and/or fields are more or fewer than the "size" of the range (number of rows and columns), the data are correctly exported and the Range is redefined to match the size of the exported data in terms of width and depth of the range (number of rows and number of columns). Note that any formatting in the cells within this range is retained (e.g., Bold, Highlight color, font color, etc.).

1. If the Range does not exist in the workbook file, TransferSpreadsheet creates a new worksheet, names it with the Range argument value, writes the data onto that worksheet, and creates a new Range (also named with the Range argument value)to define the cells that contain the exported data. If a worksheet with the same name as what is in the Range argument already exists in the workbook file, the new worksheet that is created is named using standard EXCEL process, namely, the Range argument name followed by a 1. Thus, if I use MyName as the Range argument and export to an existing file, I can get one of the following results:


  • File already contains a worksheet named MyName but does not contain a Range named MyName: A new worksheet named MyName1 is created, the data are written onto that worksheet, and a new Range named MyName is defined for the cells that received those exported data.

  • File does not contain a worksheet named MyName and does not contain a Range named MyName: A new worksheet named MyName is created, the data are written onto that worksheet, and a new Range named MyName is defined for the cells that received those exported data.
2. If the Range exists (cell range, that is) AND if the Range consists of a single cell (e.g., A1), then strange things happen -- note that it doesn't matter if the Range starts in cell A1 or not. And because of these strange things, this is where the feature is unusable for exporting. I haven't defined exact "rules" to describe what happens (although it appears that how far the range is moved appears to be "the original row number plus 93" columns (if the Range was originally in column A), but here are my observations in this situation (I won't guarantee that you won't see different behaviors):


  • If the worksheet name is the same name as the Range name, and the Range begins in cell A1, the exported data are written to the worksheet that contains the Range specified in the TransferSpreadsheet action, and these data begin at cell A1 (with the field names row) -- BUT the existing range is moved to cell CQ1 (94 columns to the right), and there is no Range created for the cells that contain the exported data. Any further attempt to export to this worksheet using the same Range argument generates an error because the "move" of the range will extend beyond the column limit of the worksheet.

  • If the worksheet name is the same name as the Range name, and the Range begins in cell A5, the exported data are written to the worksheet that contains the Range specified in the TransferSpreadsheet action, and these data begin at cell E5 (with the field names row) -- BUT the existing range is moved to cell CU5 (98 columns to the right), and there is no Range created for the cells that contain the exported data. Any further attempt to export to this worksheet using the same Range argument generates an error because the "move" of the range will extend beyond the column limit of the worksheet.

  • If the worksheet name is not the same as the Range name, and the Range begins in cell A1, the exported data are written to a new worksheet that is named the same as the Range argument value, and the existing Range is then moved to cell IV1 (the last column in the sheet) on that new worksheet, and there is no Range created for the cells that contain the exported data.

  • If the worksheet name is not the same as the Range name, and the Range begins in cell A30, the exported data are written to the existing worksheet that contains the named Range but are written into the cell block where the left top anchor is cell AD150 (29 columns to the right), the existing Range is then moved to cell DT30 on that worksheet (123 columns to the right), and there is no Range created for the cells that contain the exported data.

  • If the worksheet name is not the same as the Range name, and the Range begins in cell A150, the exported data are written to the existing worksheet that contains the named Range but are written into the cell block where the left top anchor is cell ET150 (149 columns to the right), the existing Range is then moved to cell IJ150 on that worksheet (243 columns to the right, and there is no Range created for the cells that contain the exported data.

  • If the worksheet name is not the same as the Range name, and the Range begins in any column except column A (e.g., the Range is defined as cell B1), an error occurs because the "move" of the range will extend beyond the column limit of the worksheet, and no data are exported.

Update: This article on Japanese, thanks to Yu Tang!

18 Comments:

Anonymous Anonymous said...

!@#$%^&*() is what I think of undocumented "Features".

Thank you for your research and analysis. It explains why I had to do the workaround that I installed. Frustrating that I have to do it. My memory (which is subject to random acts of forgetfullness) says it was not always this way. But since we need to work in the present, it does not matter.

Thank you again for your research.

Ron

6:43 PM  
Anonymous Anonymous said...

This works brilliantly. THANKS!

8:46 PM  
Anonymous Anonymous said...

Also consider: DoCmd.RunCommand 175 with the source table/query open. It exports a more formatted spreadsheet, but doesn't offer the range capabilities.

12:40 PM  
Anonymous Anonymous said...

I have a problem which is related to this and was wondering if you had any suggestions.

I have a table which lists all the managers for various departments. I have twenty-five other tables which list users, user IDs and access levels in each of the departments.

I need to create a spreadsheet for each manager with tabs/workbooks for each of the twenty-five departments filtered only for their users.

Is there a way to do this in Access?

Thanks for any help you're able to offer!

12:33 AM  
Blogger Alex Dybenko said...

Hi,
i think its better to use excel authomation then TransferSpreadsheet in this case. look at sample code at www.mvps.org/access and support.microsoft.com

8:38 AM  
Blogger Unknown said...

Thanks Alex, I'll use this.

Bonnie
http://www.dataplus-svc.com

6:46 AM  
Anonymous Anonymous said...

Thank you Alex! I have been stuck on this for 2 days now and this worked perfectly!

8:46 PM  
Blogger gnash said...

Was just able to export to Excel from Access 2007 to specific range using:
Call Application.DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel8, Access_Table, FullPathToExcelWorkbook.xls, False, Sheet1$A3:BA3)

The only caveat was that headers were exported, which were not wanted.

The code exported starting on row 3 and continued on from there.

2:24 AM  
Anonymous Anonymous said...

I'm trying to export data and it works until I add a chart to the workbook.

Call Application.DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel8, "temp", varReportPath, True, "DATA_1320$a1:f71")

The above code works well without any charts.

5:38 PM  
Anonymous Damien said...

Thanks a lot for this!

It helped me a lot!

Damien

6:47 PM  
Blogger Unknown said...

The headers are also getting copied.. Can anybody help me out for this error? My code is as follows :

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Q", "c:\5\ImportExport.xlsx", 0, "Rng"

I dont know how to avoid headers while exporting the data?

4:35 PM  
Blogger Alex Dybenko said...

Hi,
you can open excel file after export with automation and delete heade line using VBA code

3:09 PM  
Anonymous Anonymous said...

I learned all about the MS Excel from Excel Training NYC and now i am doing job in a multinational firm. Do visit to them.

3:32 PM  
Anonymous Anonymous said...

I've been using the following:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qdfQRY.Name, strResult, True
where the name of the Access 2010 query is to be used as the name of the worksheet. The problem I have is that the query names contain spaces i.e. ALMDB Param Chk which appear as an Excel worksheet ALMDB_Param_Chk. Is there a way to prevent a "space" to be replaced with a "_"?

10:14 PM  
Blogger Alex Dybenko said...

Hi,
you can exoport it as is, and after export open excel file with automation to rename worksheet to desired name.

4:13 PM  
Blogger maverick said...

Hi
I notice a wierd problem, not sure why this happens, When i do a DoCmd.TransferSpreadsheet acExport to publish my query to an excel report, there is inconsistency. Sometimes the file gets refreshed with the latest data and sometimes it doesnt. The last modified date of the file does change, so there is no way to find out if the file is accurate without having to go into the file and comparing the query results and the excel sheet manually.
Do you know why this is happening and have any workaround around this?

8:28 PM  
Blogger Alex Dybenko said...

Hi,
Try to delete file, before you run export

4:29 PM  
Anonymous Anonymous said...

Hi,

This is very very useful. Thank you very much!

Orlando

4:13 PM  

Post a Comment

<< Home