Solved

Query export also exports headers?

Posted on 2003-10-23
15
491 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Any Way to Print an Import Spec? 3 28
Access on Mouse move 5 29
deduplicating based on criteria 2 19
Access Open Report with SQL Parameter 11 28
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

816 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

7 Experts available now in Live!

Get 1:1 Help Now