Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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...... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TransferSpreadsheet to colored fields | Excel Worksheet Functions | |||
Access 2003: transferspreadsheet writes over existing spreadsheet | Excel Discussion (Misc queries) | |||
How do I Export range of row/column data to Access | Excel Discussion (Misc queries) | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
how to rename sht after TransferSpreadsheet method? | Excel Programming |