Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    68

    Unanswered: Need help with VB - Export to Excel

    Hello,
    I have two issues that I need some help with ...

    I am working on some VB that will launch a query, export the data into Excel and then open the Excel spreadsheet. So far I have this code:

    Dim ssPath As String

    ssPath = InputBox("Enter location where spreadsheet will be saved" + "(drive:\path\)", "Location of Spreadsheet")

    DoCmd.OutputTo acOutputQuery, "Closedtickets by Date", acFormatXLS, ssPath + "test.xls"

    Here are my issues:

    1. I was having a problem with the export truncating my output on a memo field I have. If I manually export the query results and select "Microsoft Excel 97-2000 (*.xls) as the "Save as Type", then it works. Can I get my code to export to this type as well?

    2. Can someone help me with code that will launch Excel and open the test.xls file so the user can then view it?

    Thank you for your help

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    This is a function I use to copy (export!) the results of cross tab query(s) depending on which query is opened.

    The function is run from a toolbar button via a macro. The same tool bar is used for all the cross tabs therefore I need to know which is open, but I assume this is not a problem for you.

    Code:
    Function ExportTrainingMatrix()
        Dim ExcelApp As Object
        Dim db As Database
        Dim rs As Recordset
        Dim ExcelFile As String
            
        ExcelFile = ""
        DoCmd.Hourglass True
        
        Set db = CurrentDb()
            
        If QueryIsLoaded("Training Dates - All Personnel") Then
            Set rs = db.OpenRecordset("Training Dates - All Personnel")
            ExcelFile = "M:\Templates\Training Matrix.xlt"
        ElseIf QueryIsLoaded("Training Dates - Livingston Personnel") Then
            Set rs = db.OpenRecordset("Training Dates - Livingston Personnel")
            ExcelFile = "M:\Templates\Training Matrix.xlt"
        ElseIf QueryIsLoaded("Training Dates - Mansfield Personnel") Then
            Set rs = db.OpenRecordset("Training Dates - Mansfield Personnel")
            ExcelFile = "M:\Templates\Training Matrix.xlt"
        ElseIf QueryIsLoaded("Training Dates - Northampton Personnel") Then
            Set rs = db.OpenRecordset("Training Dates - Northampton Personnel")
            ExcelFile = "M:\Templates\Training Matrix.xlt"
        ElseIf QueryIsLoaded("Training Dates - Technicians Personnel") Then
            Set rs = db.OpenRecordset("Training Dates - Technicians Personnel")
            ExcelFile = "M:\Templates\Training Matrix.xlt"
        Else
            DoCmd.Hourglass False
            db.Close
            Set db = Nothing
            MsgBox "Could not determine which Matrix is open." & vbCr & _
                    "Error in 'ExportTrainingMatrix' subroutine!", vbExclamation
            Exit Function
        End If
    
        On Error Resume Next
        Set ExcelApp = GetObject(, "Excel.Application")
    
        If Err.Number <> 0 Then
            Err.Clear
            Set ExcelApp = CreateObject("Excel.application")
    
        End If
        
        If ExcelFile = "" Then
            ExcelApp.Workbooks.Add
        Else
            ExcelApp.Workbooks.Open (ExcelFile)
        End If
        With ExcelApp
            Dim i As Integer
            Dim j As Integer
            
            For j = 0 To rs.Fields.Count - 1
                .cells(1, j + 1) = rs.Fields(j).Name
            Next j
            
            i = 2
            Do Until rs.EOF
                            
                For j = 0 To rs.Fields.Count - 1
                    .cells(i, j + 1) = rs(j)
                Next j
                i = i + 1
                rs.MoveNext
            Loop
            
            .Visible = True
        End With
            
        rs.Close
        db.Close
        Set rs = Nothing
        Set db = Nothing
        
        DoCmd.Hourglass False
    
    End Function
    This routine will use a currently open excel app if it exists or starts excel if not. It then opens a template from the specified location, which is the same for all queries, or a blank sheet if no file specified.

    I assumme that you are familiar with VBA in Excel !?

    Hope this will give you some ideas.

    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •