Solved

Query export also exports headers?

Posted on 2003-10-23
15
494 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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
 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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