Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Exclamation Unanswered: Excel Spreadsheet sent from Access to Blackberry Cannot be opened.

    I am running Access 2003 on XP Professional Version 2002 SP 3.

    A Macro runs as a scheduled task every AM creating sales data from the day before.

    The final step in the Macro is to send a spreadsheet to upper management as an attachment to an email using Outlook .

    Output format is Microsoft Excel 97-2003 (*.xls).

    The problem is that it cannot be viewed using a Blackberry. The BB uses 'Sheet to Go' to open and view spreadsheets. The error is unrecognizable file format.

    It will work if I open the spreadsheet using Excel 2003 and send it as an attachment from Excel yet not from Access.

    I need help with the solution. I am somewhat new to VB.
    Is there a way VBA code could do this instead of the macro?
    Am I using the correct output format?
    Will upgrading to Access 2007 help?

    Thanks very much for your help.
    This is my first time using dBforums.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Welcome to dbforums!

    If you're sure that the file can be opened using Excel 2003 you could use VBA and Automation (formerly OLE, COM+, etc.) in order to "pilot" Excel, and possibly Outlook to create the document and send it to the Blackberry.

    See, for instance:
    VBA Tips & Tricks: VBA Email Automation / VBA Mail Automation
    Basic information about OLE automation using VBA in Microsoft Excel
    An Introduction to OLE Automation with Visual Basic 6 — Developer.com
    Have a nice day!

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    Thank you for pointing me in the direction I should take.
    I am going to try to implement something similar to the VBA Email automation link that you provided.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    One thing I would suggest doing is to open Excel and record a macro. Start recording the macro before sending the attachment, then stop recording the macro. If you go into Tools/Macro/Visual Basic Editor you should see the VBA that can be used to do what you want. For me, it is the easiest way to automate Excel.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For exporting to Excel, here is a function that does the job:
    Code:
    Function ExportToExcel(ByVal DataObject As String, ByVal FileName As String, ByVal IncludeHeader As Boolean) As Long
    
    ' Purpose:      Export a data set to an Excel file using OLE.
    ' -------
    '
    ' Dependencies: Must have a reference to the Microsoft Excel Objects Library.
    ' ------------
    '
    ' Parameters:   DataObject:    Can be the name of a table or query, or a valid SQL SELECT statement.
    ' ----------    FileName:      The name of the .xls file to create.
    '               IncludeHeader: If True, the first line of the Excel sheet contains the columns name.
    '
    ' Usage:        Table: ExportToExcel "Tbl_Customers", "Customers.xls", True
    ' -----         Query: ExportToExcel "Qry_Customers", "Customers.xls", True
    '               SQL:   ExportToExcel "SELECT * FROM Tbl_Customers ORDER BY Name;", "Customers.xls", True
    '
    ' To do:        - Handle errors.
    ' -----         - Properly handle the case when the are more than 26 columns in the data set.
    '
        Dim appXl As Excel.Application
        Dim rst As DAO.Recordset
        Dim fld As DAO.Field
        Dim intRow As Integer
        Dim strcell As String
        
        Set rst = CurrentDb.OpenRecordset(DataObject, dbOpenSnapshot)
        Set appXl = New Excel.Application
        With appXl
            .Visible = True ' Optional: don't use in "quiet" mode.
            .Workbooks.Add
            .Sheets.Add
            .ActiveSheet.Name = DataObject
            intRow = 1
            If IncludeHeader = True Then
                For Each fld In rst.Fields
                    Select Case fld.OrdinalPosition
                        
                        ' Should use a more generalist algorithm
                        ' that would be able to handle all cases.
                        '
                        Case 0 To 25    ' Cells "An" to "Zn"
                            strcell = Chr(65 + fld.OrdinalPosition) & CStr(intRow)
                        Case 26 To 51   ' Cells "AAn" to "AZn"
                            strcell = "A" & Chr(65 + fld.OrdinalPosition) & CStr(intRow)
                        Case Else ' etc.
                    End Select
                    .Range(strcell).Select
                    .ActiveCell.FormulaR1C1 = fld.Name
                Next
                intRow = intRow + 1
            End If
            Do Until rst.EOF
                For Each fld In rst.Fields
                    Select Case fld.OrdinalPosition
                        
                        ' See above.
                        '
                        Case 0 To 25
                            strcell = Chr(65 + fld.OrdinalPosition) & CStr(intRow)
                        Case 26 To 51
                            strcell = "A" & Chr(65 + fld.OrdinalPosition) & CStr(intRow)
                    End Select
                    .Range(strcell).Select
                    .ActiveCell.FormulaR1C1 = fld.Value
                Next
                rst.MoveNext
                intRow = intRow + 1
            Loop
            rst.Close
            Set rst = Nothing
            .ActiveWorkbook.SaveAs FileName
            .Quit
        End With
        Set appXl = Nothing
    
    End Function
    The priciple should be similar with Outlook, though I'm not familiar with Outlook object model.
    Have a nice day!

Posting Permissions

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