Solved

Query export also exports headers?

Posted on 2003-10-23
15
483 Views
Last Modified: 2008-02-01
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"
 Else
   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, _
                              HasFieldNames:=False
    'excel variables
    Dim xlApp As Excel.Application
    Set xlApp = New Excel.Application
    xlApp.Visible = True
    xlApp.Workbooks.Open strFileName

Thanks,

Jeremy
0
Comment
Question by:Jeremyw
  • 7
  • 7
15 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9607928
Hey Jeremyw!

> HasFieldNames:=False

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

regards
Jack
0
 
LVL 3

Author Comment

by:Jeremyw
ID: 9607947
I have tried it using True & False.  Both export the headers, which I don't want.
0
 
LVL 3

Author Comment

by:Jeremyw
ID: 9607953
Jack, What do you use instead of transferspreadsheet?

Jeremy
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9607972
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
0
 
LVL 32

Accepted Solution

by:
jadedata earned 50 total points
ID: 9607997
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
  tbook.Close
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9608056
BTW:  CopyFromRecordset will get you around the 20,000 record limit in TransferSpreadsheet.

0
 
LVL 3

Author Comment

by:Jeremyw
ID: 9608107
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 3

Author Comment

by:Jeremyw
ID: 9608429
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?

Also,

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?


Jeremy
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9609474
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.
0
 
LVL 3

Author Comment

by:Jeremyw
ID: 9629822
Jack, I think I'm going to have to come back to this a little later....... might still be a little over my head.

Thanks,

Jeremy
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9629835
check back here within two weeks to keep the question open so the CleanUp crews see it as still active.
0
 
LVL 3

Author Comment

by:Jeremyw
ID: 9690151
Hey jack, I left a question specifcally for you at

http://www.experts-exchange.com/Databases/MS_Access/Q_20789103.html

Jeremy
0
 
LVL 39

Expert Comment

by:stevbe
ID: 9991044
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

stevbe
EE Cleanup Volunteer
0
 
LVL 3

Author Comment

by:Jeremyw
ID: 10010396
Jack.  

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

Thanks,

Jeremy
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10010552
If you want to pick this up later when you have time,..ping this thread.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now