Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    TransferSpreadsheet (Access XP)

    I have a query that I would like to transfer to an EXCEL spreadsheet. The query is also used with in the Access application as a report record source. One of the columns of the query contains postal address information which has been formated in to multiple lines using vbCrLf. This turns out to cause an "unexpected error ... 3275" within TransferSpreadsheet.

    As I've been able to use vbCr and vbLf without getting a TransferSpreadsheet error, I'm assuming that if I were to send an ALT+ENTER combination in lieu of the vbCrLf , the problem would be eliminated and I would still get the multiple lines in EXCEL that I want. Since I perform the formating by a function I've written, it would be simple to switch between vbCrLf for Access and ALT+Enter for EXCEL. Does anyone know how to insert an ALT into a character string in VBA? I've been unable to find it. Or, if anyone has a suggestion on a different approach I'd be very interested.

    Thanks much,
    Marty

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: TransferSpreadsheet (Access XP)

    I tested this with following query using Northwind Customers table:

    SELECT Customers.CustomerID, Customers.Address, Customers.City, Customers.PostalCode, Customers.Country, [Address] & Chr$(13) & Chr$(10) & [City] & Chr$(13) & Chr$(10) & [PostalCode] & Chr$(13) & Chr$(10) & [Country] AS FullAddress
    FROM Customers
    ORDER BY Customers.CustomerID;

    Tested export to Excel with following methods:

    Public Sub TestTransferSpreadsheet()
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    "qryCustomerAddresses", "C:AccessCustomerAddresses.xls"
    End Sub

    Sub TestOutputToExcel()
    ' Note: will export to Excel 5.0/95 format:
    DoCmd.OutputTo acOutputQuery, "qryCustomerAddresses", acFormatXLS, "C:AccessCustomerAddresses2.xls", True

    End Sub

    I did not get an error with either sub, but when using TransferSpreadsheet the CR/LF's (Chr(13) & Chr(10)) were replaced with ugly square boxes. When using the more primitive OutputTo method, the query was exported w/o error to Excel, with CR/LF's properly represented in the resulting spreadsheet. Recommend try using OutputTo in place of TransferSpreadsheet & see what happens. NOTE: Am using ACC 2K, not XP, so don't know if that may be a factor. Check VBA Help for more info on TransferSpreadsheet & OutputTo methods.

    HTH

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: TransferSpreadsheet (Access XP)

    Hi Marty,
    I'm using Ac2K, and don't get the error you report.
    I get a nearly perfect result if I export the query with Tools, Office Links, Analyse it with Excel. The only issue is that the row heights of the resulting spreadsheet are not 'best fit', so I have to manually fix that. But the cr/lfs come across the same as Alt-Enter would have created them in Excel.
    If I use File, Export then the cr/lfs appear as non-printing characters (thick vertical bars). If I edit the cell (F2 then enter) they turn into square boxes and a cr/lf.
    If I use an Access macro to TransferSpreadsheet, I get the same result as File, Export.

    For your needs I'd suggest playing with these techniques. Maybe File, Export to a different version of Excel may work. I'd also looks into maybe doing a VBA Replace function in the query field to convert the vbcrlf to chr$(10) or 13 or a mixture thereof.

    As for embedding and ALT keystroke, I don't know of anyway to do that. There is a representation for ALT in SendKeys, but that's not what you want. Also note that the ALT key is only special here to Excel - Access doesn't know about Alt-Enter.

    God speed and let us know how you go!

    Peter

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: TransferSpreadsheet (Access XP)

    My guess is OutputTo is mimicking File, Export that also seems to work.
    Peter

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: TransferSpreadsheet (Access XP)

    As far as I can tell, OutputTo is also pretty much the equivalent of the Tools - OfficeLinks "Analyze It with MS Excel" menu command.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: TransferSpreadsheet (Access XP)

    Mark:

    Thanks for the quick repsponse and work-around. The OutputTo works just fine, although it would be nice if that command provided for a more current XLS format than 95. But hey, at least its now out in a spread sheet.

    Thanks again,
    Marty

  7. #7
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: TransferSpreadsheet (Access XP)

    Peter,

    Thanks for your input. Turns out the OutputTo command accomplishes the task.

    Marty

Posting Permissions

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