[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

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"
 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
Jeremyw
Asked:
Jeremyw
  • 7
  • 7
1 Solution
 
jadedataMS Access Systems CreatorCommented:
Hey Jeremyw!

> HasFieldNames:=False

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

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

Jeremy
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
0
 
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
  tbook.Close
0
 
jadedataMS Access Systems CreatorCommented:
BTW:  CopyFromRecordset will get you around the 20,000 record limit in TransferSpreadsheet.

0
 
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.
0
 
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?

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

Thanks,

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

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

Jeremy
0
 
stevbeCommented:
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
 
JeremywAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
If you want to pick this up later when you have time,..ping this thread.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now