transferspreadsheet export to named range in excel

[ragtopcaddy via AccessMonster.com]

I've done this before but I can't remember exactly how to do it.

Here's the line of code:

docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Op_Com", "T:\
Storage\1.Reporting\DB\ReportPivots.xls", False, "A2"

I have a query, "Op_Com" and a corresponding worksheet, "Op_Com". This is
just the latest edit to the code. Nothing I've tried has worked. I have a
named range, "OpCom" on the sheet "Op_Com", but addressing that causes other
errors. The error I'm getting with this iteration is: '3011' Could not find
the object "A2".

I've tried leaving headers in the named range on the worksheets, no headers,
using the range name, and many other attempts, without success.

 

[Alex Dybenko]

Hi,
have a look here:
http://accessblog.net/2006/07/export-to-excel-range.html

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[ragtopcaddy via AccessMonster.com]

Alex,

Thanks for the elucidation!

However, I know I have successfully transferred query results to an existing
named range in a spreadsheet, and that range has expanded to accommodate the
new data. The technique I used, from what I can remember, was to create a
named range of at least 2 rows and a number of columns sufficient to
accommodate the query fields, on an existing sheet. I seem to remember
something about the names of the sheet and range having to match, or having
to not match. I must have come accross this technique in this workgroup.

Do you have any further insight into this?

I was using 97 at the time. Maybe that's why it worked?

Regards,

Bill

Hi,
have a look here:
http://accessblog.net/2006/07/export-to-excel-range.html

I've done this before but I can't remember exactly how to do it.

[quoted text clipped - 15 lines]

headers,
using the range name, and many other attempts, without success.

 

[ragtopcaddy via AccessMonster.com]

Alex,

I'm still having great difficulties in doing this.

Do the query name, worksheet name, and range name all have to be the same? Or
is it important that they all, or 2 of them, be different? I also have a
field in the query with the same name as the other 3. I keep getting "cannot
find the object" msgs when I run my code:

With xlObj
Set wkBk = Workbooks.Open("T:\Storage\1.Reporting\DB\ReportPivots.xls")
Set shOpCom = wkBk.Sheets("OpCom")
Set rngOpCom = shOpCom.Range("OpCom")
End With

strSQL = "SELECT Full_Name, IIf(IsNull([Op_Com]),"""",[Op_Com]) AS OpCom,"
strSQL = strSQL & " IIf(IsNull([Sub_Op_Comm]),"""",[Sub_Op_Comm]) AS
SubOpCom" & vbCrLf
strSQL = strSQL & "FROM dbo_v_Storage_Qtree_App" & vbCrLf
strSQL = strSQL & "WHERE created=#" & dtRpt & "# AND tier_type Like ""*Tier
1*"""

qdfOpCom.SQL = strSQL

With DoCmd
.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Op_Com", _
"T:\Storage\1.Reporting\DB\ReportPivots.xls", False
.DeleteObject acQuery, "Op_Com"
End With

Thanks

Hi,
have a look here:
http://accessblog.net/2006/07/export-to-excel-range.html

I've done this before but I can't remember exactly how to do it.

[quoted text clipped - 15 lines]

headers,
using the range name, and many other attempts, without success.

 

[ragtopcaddy via AccessMonster.com]

I've modified my code as follows and now I get the msg "Run-time error '3010'
- Table 'OpCom' already exists" upon execution of the transferspreadsheet
line.

Sub ExportPivots()
Dim xlObj As Excel.Application
Dim shGCRS As Worksheet
Dim shOpCom As Worksheet
Dim wkBk As Workbook
Dim strSQL As String
Dim rngOpCom As Range
Dim rngGCRS As Range
Dim dtRpt As Date
Dim qdfOpCom As QueryDef
Dim qdfGCRS As QueryDef

With dbLocal
' Set qdfOpCom = .CreateQueryDef("OpCom")
Set qdfOpCom = .QueryDefs("OpCom")
Set qdfGCRS = .QueryDefs("GCRS")
End With

dtRpt = #3/9/2008#

If ExcelIsRunning Then
Set xlObj = GetObject(, "Excel.Application")
Else
Set xlObj = CreateObject("Excel.Application")
End If

With xlObj
Set wkBk = Workbooks.Open("T:\Storage\1.Reporting\DB\ReportPivots.xls")
Set shOpCom = wkBk.Sheets("OpCom")
Set shGCRS = wkBk.Sheets("GCRS")
Set rngOpCom = shOpCom.Range("OpCom")
Set rngGCRS = shGCRS.Range("GCRS")
End With

strSQL = "SELECT Full_Name, IIf(IsNull([Op_Com]),"""",[Op_Com]) AS OpCom,"
strSQL = strSQL & " IIf(IsNull([Sub_Op_Comm]),"""",[Sub_Op_Comm]) AS
SubOpCom" & vbCrLf
strSQL = strSQL & "FROM dbo_v_Storage_Qtree_App" & vbCrLf
strSQL = strSQL & "WHERE created=#" & dtRpt & "# AND tier_type Like ""*Tier
1*"""

qdfOpCom.SQL = strSQL

With DoCmd
.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "OpCom", _
"T:\Storage\1.Reporting\DB\ReportPivots.xls", False,
"OpCom"
End With

Unfortunately, with so many things named 'OpCom', it's difficult to say which
of them is referred to by the error msg, although I'm assuming it's the range
argument.

Thanks,

Hi,
have a look here:
http://accessblog.net/2006/07/export-to-excel-range.html

I've done this before but I can't remember exactly how to do it.

[quoted text clipped - 15 lines]

headers,
using the range name, and many other attempts, without success.

 

[Alex Dybenko]

Hi,
suggest to rename either sheet or range to something else, perhaps VBA also
get confused.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I've modified my code as follows and now I get the msg "Run-time error
'3010'
- Table 'OpCom' already exists" upon execution of the transferspreadsheet
line.

Sub ExportPivots()
Dim xlObj As Excel.Application
Dim shGCRS As Worksheet
Dim shOpCom As Worksheet
Dim wkBk As Workbook
Dim strSQL As String
Dim rngOpCom As Range
Dim rngGCRS As Range
Dim dtRpt As Date
Dim qdfOpCom As QueryDef
Dim qdfGCRS As QueryDef

With dbLocal
' Set qdfOpCom = .CreateQueryDef("OpCom")
Set qdfOpCom = .QueryDefs("OpCom")
Set qdfGCRS = .QueryDefs("GCRS")
End With

dtRpt = #3/9/2008#

If ExcelIsRunning Then
Set xlObj = GetObject(, "Excel.Application")
Else
Set xlObj = CreateObject("Excel.Application")
End If

With xlObj
Set wkBk = Workbooks.Open("T:\Storage\1.Reporting\DB\ReportPivots.xls")
Set shOpCom = wkBk.Sheets("OpCom")
Set shGCRS = wkBk.Sheets("GCRS")
Set rngOpCom = shOpCom.Range("OpCom")
Set rngGCRS = shGCRS.Range("GCRS")
End With

strSQL = "SELECT Full_Name, IIf(IsNull([Op_Com]),"""",[Op_Com]) AS OpCom,"
strSQL = strSQL & " IIf(IsNull([Sub_Op_Comm]),"""",[Sub_Op_Comm]) AS
SubOpCom" & vbCrLf
strSQL = strSQL & "FROM dbo_v_Storage_Qtree_App" & vbCrLf
strSQL = strSQL & "WHERE created=#" & dtRpt & "# AND tier_type Like
""*Tier
1*"""

qdfOpCom.SQL = strSQL

With DoCmd
.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "OpCom", _
"T:\Storage\1.Reporting\DB\ReportPivots.xls",
False,
"OpCom"
End With

Unfortunately, with so many things named 'OpCom', it's difficult to say
which
of them is referred to by the error msg, although I'm assuming it's the
range
argument.

Thanks,

Hi,
have a look here:
http://accessblog.net/2006/07/export-to-excel-range.html

I've done this before but I can't remember exactly how to do it.

[quoted text clipped - 15 lines]

headers,
using the range name, and many other attempts, without success.