Query export also exports headers?

In the following code, I am exporting a query to Excel.  I have HasFieldNames:=False under Transferspreadsheet, but it is still exporting the headers.  Am I missing something else?

Private Sub cmdSearchByDescription_Click()
 Dim rs As DAO.Recordset
 Dim ssql As String
 Dim TargetName As Variant
 Dim TargetName2 As Variant
 Dim qd As DAO.QueryDef
 Dim strFileName As String
 Dim PathStr As String
 On Error GoTo Err_cmdSearchByDescription_Click
    PathStr = "C:\ProductDescriptionSearch.xls"
    If Len(Dir(PathStr)) > 0 Then
       Kill PathStr
    End If
 TargetName = InputBox("Enter B* for Blank or R* for Decorated", "Product Type Needed")
 If Len(TargetName) = 0 Then Exit Sub '<--user selected cancel
 TargetName2 = InputBox("Enter Description", "Description Needed")
 If Len(TargetName2) = 0 Then Exit Sub '<--user selected cancel
'build filename
 strFileName = ("c:\ProductDescriptionSearch.xls")

 'Build the sql on the fly with the user selected date embedded in the sql string.
    ssql = "SELECT "
    ssql = ssql & "tblWarehouseInvDaily.ProductNumber, "
    ssql = ssql & "tblWarehouseInvDaily.ProductDescription, "
    ssql = ssql & "tblWarehouseInvDaily.ProductDescription2, "
    ssql = ssql & "tblWarehouseInvDaily.ProductLine, "
    ssql = ssql & "tblWarehouseInvDaily.SubProductLine, "
    ssql = ssql & "tblWarehouseInvDaily.ProductColor, "
    ssql = ssql & "tblWarehouseInvDaily.ProductSize, "
    ssql = ssql & "tblWarehouseInvDaily.ActivityDate, "
    ssql = ssql & "tblWarehouseInvDaily.TotalOnHand, "
    ssql = ssql & "tblWarehouseInvDaily.TotalAllocated, "
    ssql = ssql & "tblWarehouseInvDaily.TotalOnCustomerOrders, "
    ssql = ssql & "tblWarehouseInvDaily.TotalQuantityOnWO, "
    ssql = ssql & "tblWarehouseInvDaily.QuantityOnOrder, "
    ssql = ssql & "tblWarehouseInvDaily.UnitPrice, "
    ssql = ssql & "tblWarehouseInvDaily.ExtendedPrice, "
    ssql = ssql & "tblWarehouseInvDaily.MTDSalesPieces, "
    ssql = ssql & "tblWarehouseInvDaily.YTDSalesPieces, "
    ssql = ssql & "tblWarehouseInvDaily.MTDCOG, "
    ssql = ssql & "tblWarehouseInvDaily.YTDCOG, "
    ssql = ssql & "tblWarehouseInvDaily.MTDSalesDollars, "
    ssql = ssql & "tblWarehouseInvDaily.YTDSalesDollars "
    ssql = ssql & vbCrLf
 ssql = ssql & "FROM tblWarehouseInvDaily" & vbCrLf
 ssql = ssql & "WHERE "
   ssql = ssql & "(tblWarehouseInvDaily.ProductNumber LIKE '" & TargetName & "') and "
   ssql = ssql & "(tblWarehouseInvdaily.ProductDescription LIKE '" & TargetName2 & "');"

  Set rs = CurrentDb.OpenRecordset(ssql, dbopensnapshot)
 If rs.BOF And rs.EOF Then
   MsgBox "No data for the Product Description specified", vbOKOnly + vbInformation, "Notice"
   Set qd = CurrentDb.QueryDefs("qselWarehouseSearchByDescription")
 qd.SQL = ssql '<--update the sql property of the persistant query
 Set qd = Nothing
    'export query
    DoCmd.TransferSpreadsheet TransferType:=acExport, _
                              SpreadsheetType:=acSpreadsheetTypeExcel9, _
                              Tablename:="qselWarehouseSearchByDescription", _
                              FileName:=strFileName, _
    'excel variables
    Dim xlApp As Excel.Application
    Set xlApp = New Excel.Application
    xlApp.Visible = True
    xlApp.Workbooks.Open strFileName


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jadedataMS Access Systems CreatorCommented:
Hey Jeremyw!

> HasFieldNames:=False

shouldn't that be True??
(this is one of the reason I don't use transferspreadsheet....)

JeremywAuthor Commented:
I have tried it using True & False.  Both export the headers, which I don't want.
JeremywAuthor Commented:
Jack, What do you use instead of transferspreadsheet?

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

jadedataMS Access Systems CreatorCommented:
You could resolve this by:
 opening a new Excel object
 loop thru the field names in the recordset and send each to the appropriate range in the worksheet
   (this also give you an opportunity to "rearrange" the spreadsheet a little.)
 use CopyFromRecordset to past the data below the new headers
jadedataMS Access Systems CreatorCommented:
Generic Code for example:

  Set xl = New Excel.Application
  xl.Application.Visible = True
  xl.Application.WindowState = acXLSNormal
  xl.DisplayAlerts = False
  Set tbook = xl.Application.Workbooks.Add
  Set tsheet = tbook.Worksheets(1)
  tsheet.Rows(1).RowHeight = 25
  tsheet.Range("A1:M1").MergeCells = True
  tsheet.Range("A1") = "Filter Condition: " & vMasterFilter
  For n = 0 To ors.Fields.Count - 1
    tsheet.Cells(2, (n + 1)) = ors.Fields(n).Name
    If InStr("AuthFee;CopayPaid;PaymentAmount;", ors.Fields(n).Name) > 0 Then
      CurrCol = zColumnToLetter((n + 1))
      tsheet.Cells(1, (n + 1)) = "=subtotal(9," & CurrCol & "3:" & CurrCol & "20000)"
      tsheet.Range(CurrCol & "1").EntireColumn.NumberFormat = "$#,##0.00"
    End If
    CurrCol = zColumnToLetter((n + 1))
    tsheet.Cells(2, (n + 1)).Interior.Color = C_BrtGrey
  Next n
  tsheet.Range("A2:" & CurrCol & "2").AutoFilter
  tsheet.Range("A3").CopyFromRecordset ors      '<<<fills the sheet below the headers.
  tsheet.Range("A:" & CurrCol).WrapText = False
  tsheet.Range("A:" & CurrCol).Columns.AutoFit
  tsheet.Range("A1:M1").MergeCells = False
  tbook.SaveAs SaveAsName

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jadedataMS Access Systems CreatorCommented:
BTW:  CopyFromRecordset will get you around the 20,000 record limit in TransferSpreadsheet.

JeremywAuthor Commented:
Hmm.  Didn't know there was a 20,000 limit using TransferSpreadsheet.  Good thing I found that out now, otherwise I'd go crazy trying to find out where my data is.  ;)

Haven't dealt with the CopyFromRecordset at all.  Guess I need to look into that some more.
JeremywAuthor Commented:
OK, still learning about defining variables.  from your code above, is this correct?

Dim xl As Excel.Application
Dim tbook As Excel.Workbook
Dim tsheet As Excel.Worksheet

what does n need to be defined as?


had to change  xl.Application.WindowState = acXLSNormal TO  xl.Application.WindowState = xlNormal

I get variable not defined on vMasterFilter on line   tsheet.Range("A1") = "Filter Condition: " & vMasterFilter

    If InStr("AuthFee;CopayPaid;PaymentAmount;", ors.Fields(n).name) > 0 Then
      CurrCol = zColumnToLetter((n + 1))
      tsheet.Cells(1, (n + 1)) = "=subtotal(9," & CurrCol & "3:" & CurrCol & "20000)"
      tsheet.Range(CurrCol & "1").EntireColumn.NumberFormat = "$#,##0.00"

Are (AuthFee;CopayPaid;PaymentAmount) field names in your query?

jadedataMS Access Systems CreatorCommented:
n can be an integer since its used to count table fields which is an integer value.  Sorry that appears earlier in the routine I cut that from.

vMasterFilter can be removed if you like or dimmed so that you can pass a value to the spreadsheet header rows showing what filter is on your recordset for this report.

Yes, you can drop those or change them to ones in your recordset.  They were there for demo on how to "swap" column names or find the fields that will require summary values above the header in the spreadsheet.
Notice that what follows is the special formatting of those columns to currency format.
JeremywAuthor Commented:
Jack, I think I'm going to have to come back to this a little later....... might still be a little over my head.


jadedataMS Access Systems CreatorCommented:
check back here within two weeks to keep the question open so the CleanUp crews see it as still active.
JeremywAuthor Commented:
Hey jack, I left a question specifcally for you at


No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: jadedata {http:#9607997}

Please leave any comments here within the next seven days.

EE Cleanup Volunteer
JeremywAuthor Commented:

Went ahead and gave you the points for this.  I've been so busy, I haven't had time to mess with it.  


jadedataMS Access Systems CreatorCommented:
If you want to pick this up later when you have time,..ping this thread.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.