Export to Range from Access with TransferSpreadsheet
 

I need to export a series of different Access tables into a single Excel
worksheet with
DoCmd.TransferSpreadsheet.

The problem is positioning each table of data properly in the worksheet:
there needs to be 40 rows between each table.

I'm creating each table on the fly, then dumping it out to Excel like this:

i = 10
For i = 1 To bytNumOfTables
db.Execute ("DROP TABLE tblExcelData"), dbFailOnError
db.Execute ("qryMakeNewTable"), dbFailOnError
db.Execute ("qryPopulateExcelData"), dbFailOnError
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadSheetType:=acSpreadsheetTypeExcel5, _
TableName:="tblExcelData", _
FileName:="C:\PathToWorkbook", _
HasFieldNames:=True, _
Range:="P" & i
i = i + 40
Next

So I'm expecting a contiguous block of rows and columns (about 15 rows and
20 columns) starting at P10, then another at P50, another at P90, and so on.
To the left of each contiguous block will be a graph of the data.

Am I specifying the Range parameter correctly?

Why isn't

Range:="P" & i

working?

Is there a better way to do this?

Thanks in advance.

Export to Range from Access with TransferSpreadsheet
 

The Range argument only applies when importing.
Perhaps you could dump each copy of the table to a different sheet, e.g.:

Dim i As Integer
Dim bytNumOfTables As Integer
Dim strTable
bytNumOfTables = 2
i = iStart
strTable = "tblExcelData"

For i = 1 To bytNumOfTables
CurrentDb.TableDefs(strTable).Name = strTable & i
db.Execute ("DROP TABLE tblExcelData"), dbFailOnError
db.Execute ("qryMakeNewTable"), dbFailOnError
db.Execute ("qryPopulateExcelData"), dbFailOnError
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadSheetType:=acSpreadsheetTypeExcel9, _
TableName:=strTable & i, _
FileName:="C:\PathToWorkbook", _
HasFieldNames:=True
CurrentDb.TableDefs(strTable & i).Name = strTable
Next


deko wrote:
I need to export a series of different Access tables into a single Excel
worksheet with
DoCmd.TransferSpreadsheet.

The problem is positioning each table of data properly in the worksheet:
there needs to be 40 rows between each table.

I'm creating each table on the fly, then dumping it out to Excel like this:

i = 10
For i = 1 To bytNumOfTables
db.Execute ("DROP TABLE tblExcelData"), dbFailOnError
db.Execute ("qryMakeNewTable"), dbFailOnError
db.Execute ("qryPopulateExcelData"), dbFailOnError
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadSheetType:=acSpreadsheetTypeExcel5, _
TableName:="tblExcelData", _
FileName:="C:\PathToWorkbook", _
HasFieldNames:=True, _
Range:="P" & i
i = i + 40
Next

So I'm expecting a contiguous block of rows and columns (about 15 rows and
20 columns) starting at P10, then another at P50, another at P90, and so on.
To the left of each contiguous block will be a graph of the data.

Am I specifying the Range parameter correctly?

Why isn't

Range:="P" & i

working?

Is there a better way to do this?

Thanks in advance.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Export to Range from Access with TransferSpreadsheet
 

The Range argument only applies when importing.

Sorry, that's not correct. Let this be a warning to others: Usenet contains
lots of bad advice.

Private Const P As Long = 80
r = 10 'starting row
cc = 5 'column count
rc = 24 'row count
For b = 1 To bytNumberOfTables
strRange = strSheetName & "!" & Chr(P) & r & ":" & Chr(P + cc) & rc
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadSheetType:=acSpreadsheetTypeExcel8, _
TableName:="tblExcelData" & b, _
Filename:=strXlsPath, _
HasFieldNames:=True, _
Range:=strRange
r = r + 50
rc = rc + 50
Next

This works like a charm for exporting multiple tables (tblExcelData1,
tblExcelData2, etc.) from Access into the same Excel worksheet, separated by
50 rows. The only catch is that the worksheet must already exist.

Export to Range from Access with TransferSpreadsheet
 

Thanks for reporting how you got it to work, and I'm glad you were able
to incorporate some of my suggested changes.

The bad advice comes from Microsoft:


http://msdn.microsoft.com/library/de...HV05186520.asp

so it's good to know the workaround.

deko wrote:
The Range argument only applies when importing.


Sorry, that's not correct. Let this be a warning to others: Usenet contains
lots of bad advice.

Private Const P As Long = 80
r = 10 'starting row
cc = 5 'column count
rc = 24 'row count
For b = 1 To bytNumberOfTables
strRange = strSheetName & "!" & Chr(P) & r & ":" & Chr(P + cc) & rc
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadSheetType:=acSpreadsheetTypeExcel8, _
TableName:="tblExcelData" & b, _
Filename:=strXlsPath, _
HasFieldNames:=True, _
Range:=strRange
r = r + 50
rc = rc + 50
Next

This works like a charm for exporting multiple tables (tblExcelData1,
tblExcelData2, etc.) from Access into the same Excel worksheet, separated by
50 rows. The only catch is that the worksheet must already exist.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Export to Range from Access with TransferSpreadsheet
 

The bad advice comes from Microsoft:


http://msdn.microsoft.com/library/de...HV05186520.asp


Thanks for your gracious response, especially after I invalidated your
suggestion.

I've been testing for a while now and it seems to be working fine. But my
development workstation has all the latest and greatest software. It will
be interesting to see what happens on older/unpatched systems. This is the
first "reverse bug" (where something works that's not supposed to) that I've
ever discovered. Dare I include this code in a production release?
Hmmm......

Export to Range from Access with TransferSpreadsheet
 

I've tested in Office XP and Office 97, and it worked, so you should be
okay. There are other undocumented "features" but I guess you assume a
risk in using them.

deko wrote:
The bad advice comes from Microsoft:



http://msdn.microsoft.com/library/de...HV05186520.asp


Thanks for your gracious response, especially after I invalidated your
suggestion.

I've been testing for a while now and it seems to be working fine. But my
development workstation has all the latest and greatest software. It will
be interesting to see what happens on older/unpatched systems. This is the
first "reverse bug" (where something works that's not supposed to) that I've
ever discovered. Dare I include this code in a production release?
Hmmm......




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Export to Range from Access with TransferSpreadsheet
 

I've tested in Office XP and Office 97, and it worked, so you should be
okay. There are other undocumented "features" but I guess you assume a
risk in using them.

Thanks for the validation.

I tested on 3 different machines: one with WinXP/Office2003, one with
WinXP-SP2/Office2003, and one with Win2000/Office2002 (all at current patch
levels). All worked without incident.

Still, there must be some reason why MS said not to do this.

But if I can't get it to break, I'm going to use it.

The only other way to accomplish what I'm trying to do would be to export
each Access table into a temporary worksheet (as you suggested) and then use
automation to open the workbook and move each table of data to it's
respective range in a new worksheet, and then delete the temporary
worksheets.

Export to Range from Access with TransferSpreadsheet
 

Perhaps there was a reason in earlier versions of Excel, that's no
longer a problem.

You may want to add some error checking so it adds the sheet if it's not
already in the workbook (if possible).

deko wrote:
I've tested in Office XP and Office 97, and it worked, so you should be
okay. There are other undocumented "features" but I guess you assume a
risk in using them.


Thanks for the validation.

I tested on 3 different machines: one with WinXP/Office2003, one with
WinXP-SP2/Office2003, and one with Win2000/Office2002 (all at current patch
levels). All worked without incident.

Still, there must be some reason why MS said not to do this.

But if I can't get it to break, I'm going to use it.

The only other way to accomplish what I'm trying to do would be to export
each Access table into a temporary worksheet (as you suggested) and then use
automation to open the workbook and move each table of data to it's
respective range in a new worksheet, and then delete the temporary
worksheets.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Export to Range from Access with TransferSpreadsheet
 

There are a few posts in the Access newsgroups that use the Range
argument in an export, e.g.:


http://groups.google.ca/group/comp.d...a03535ad?hl=en

Debra Dalgleish wrote:
Perhaps there was a reason in earlier versions of Excel, that's no
longer a problem.

You may want to add some error checking so it adds the sheet if it's not
already in the workbook (if possible).

deko wrote:

I've tested in Office XP and Office 97, and it worked, so you should be
okay. There are other undocumented "features" but I guess you assume a
risk in using them.



Thanks for the validation.

I tested on 3 different machines: one with WinXP/Office2003, one with
WinXP-SP2/Office2003, and one with Win2000/Office2002 (all at current
patch
levels). All worked without incident.

Still, there must be some reason why MS said not to do this.

But if I can't get it to break, I'm going to use it.

The only other way to accomplish what I'm trying to do would be to export
each Access table into a temporary worksheet (as you suggested) and
then use
automation to open the workbook and move each table of data to it's
respective range in a new worksheet, and then delete the temporary
worksheets.






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html