Results 1 to 4 of 4
  1. #1
    yoyo46 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    6

    export query with parameter combo box to excel using excel templated

    dear.. guys.


    need help for my problem..I have query need to be export to excel using excel templated.. thanks a lot for advice...

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Can you explain what you mean by 'using excel templated' ?

    Two more questions:
    1. Will a simple manual export of the query to Excel in design time work?
    2. Will exporting using a Macro work for you?

    Have you already tried 1. or 2. ?

    I have found it easy to get formatting into excel from Access queries and reports.
    As a result - I started exporting most of my data to pdf.

    BUT - if you need excel then I guess that doesn't help you much.

    Let me know exactly what you need.

  3. #3
    yoyo46 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    6
    yes i have tried both.
    1. i use
    Code:
    DoCmd.OutputTo acOutputQuery
    Code:
     DoCmd.TransferSpreadsheet acExport
    its going well but in that both cmd i can't use i.e Test.xls as my templated
    2 also is use
    Code:
    Public Function ExportRequest() As String
       On Error GoTo err_Handler
       
       ' Excel object variables
       Dim appExcel As Excel.Application
       Dim wbk As Excel.Workbook
       Dim wks As Excel.Worksheet
    
       Dim sTemplate As String
       Dim sTempFile As String
       Dim sOutput As String
       
       Dim dbs As DAO.Database
       Dim rst As DAO.Recordset
       Dim sSQL As String
       Dim lRecords As Long
       Dim iRow As Integer
       Dim iCol As Integer
       Dim iFld As Integer
       
       Const cTabTwo As Byte = 3
       Const cStartRow As Byte = 11
       Const cStartColumn As Byte = 1
       
       DoCmd.Hourglass False
       
       ' set to break on all errors
       Application.SetOption "Error Trapping", 0
       
       ' start with a clean file built from the template file
       sTemplate = CurrentProject.Path & "\SHOCTemplate.xls"
       sOutput = CurrentProject.Path & "\SHOC.xls"
       If Dir(sOutput) <> "" Then Kill sOutput
       FileCopy sTemplate, sOutput
       
       ' Create the Excel Applicaiton, Workbook and Worksheet and Database object
       Set appExcel = Excel.Application
       Set wbk = appExcel.Workbooks.Open(sOutput)
       Set wks = appExcel.Worksheets(cTabTwo)
    
          
       sSQL = "select * from Technical"
       Set dbs = CurrentDb
       Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
       If Not rst.BOF Then rst.MoveFirst
       
       ' For this template, the data must be placed on the 4th row, third column.
       ' (these values are set to constants for easy future modifications)
       iCol = cStartColumn
       iRow = cStartRow
    
    'Stop
       Do Until rst.EOF
          iFld = 0
          lRecords = lRecords + 1
          Me.lblMsg.Caption = "Exporting record #" & lRecords & " to SHOC.xls"
          Me.Repaint
          
          For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
             wks.Cells(iRow, iCol) = rst.Fields(iFld)
             
             If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
                wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
             End If
             
             wks.Cells(iRow, iCol).WrapText = False
             iFld = iFld + 1
          Next
          
          wks.Rows(iRow).EntireRow.AutoFit
          iRow = iRow + 1
          rst.MoveNext
       Loop
       
       ExportRequest = "Total of " & lRecords & " rows processed."
       Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."
       
    exit_Here:
       ' Cleanup all objects  (resume next on errors)
       On Error Resume Next
       Set wks = Nothing
       Set wbk = Nothing
       Set appExcel = Nothing
       Set rst = Nothing
       Set dbs = Nothing
       DoCmd.Hourglass False
       Exit Function
       
    err_Handler:
       ExportRequest = Err.Description
       Me.lblMsg.Caption = Err.Description
       Resume exit_Here
       
    End Function
    work well for the the templated but this function cant run with my query which contain some parameter
    Code:
     SELECT tblSHOC_Log.SN, tblSHOC_Log.SHOCRefNo, tblSHOC_Log.ObsDate, tblSHOC_Log.Zone, tblSHOC_Log.Facility, tblSHOC_Log.Project, tblSHOC_Log.SHOCReportedby, tblSHOC_Log.[Which Org?], tblSHOC_Log.[Type of Observation], tblSHOC_Log.[Type of Behaviour or Condition], tblSHOC_Log.[Describe the Intervention], tblSHOC_Log.[Describe the follow up action], tblSHOC_Log.[Follow up by:], tblSHOC_Log.[Event Report raised?], tblSHOC_Log.[Update JSA/RA?], tblSHOC_Log.SHOCStatus
    FROM tblSHOC_Log
    WHERE (((tblSHOC_Log.Zone)=[forms]![frmSHOC_Log]![CboZone])) OR (((tblSHOC_Log.SHOCStatus) Like "*" & [forms]![frmSHOC_Log]![CboStatus] & "*"));
    and i got error too low parameter.
    thanks for help me

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Why can't you use that query?
    Can you post a screen shot of the error message?

    Do you think it would help if you pulled these two parameters:

    [forms]![frmSHOC_Log]![CboZone]
    [forms]![frmSHOC_Log]![CboStatus]

    into variables in your code and plugged the variables into the SQL?

    Something like this [just a rough idea - not actual code]:

    Dim intCboZone, intStatus

    WHERE (((tblSHOC_Log.Zone)= intCboZone OR (((tblSHOC_Log.SHOCStatus) Like "*" & intCboStatus & "*"))

    ?

    Also - I think there may be a problem with this SQL of yours:

    Code:
     
    WHERE (((tblSHOC_Log.Zone)=[forms]![frmSHOC_Log]![CboZone])) OR (((tblSHOC_Log.SHOCStatus) Like "*" & [forms]![frmSHOC_Log]![CboStatus] & "*"))
    Shouldn't the Like statement in Access look like this:
    Where FieldName Like "*Value*" . . .
    . . . i.e. with double quotes . . .
    ?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export table to excel using excel template
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 1
    Last Post: 06-27-2011, 02:10 AM
  2. Replies: 3
    Last Post: 04-27-2011, 06:34 PM
  3. Export a pass-through query to Excel
    By broon in forum Import/Export Data
    Replies: 0
    Last Post: 03-07-2011, 03:43 PM
  4. Export query to Excel
    By dev82 in forum Queries
    Replies: 15
    Last Post: 02-10-2011, 11:15 AM
  5. Export Query to Various Excel Files
    By dalet in forum Import/Export Data
    Replies: 7
    Last Post: 11-09-2009, 09:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums