Access 2007 - Export to Excel - Text Cell Question

mmoralespr
mmoralespr used Ask the Experts™
on
I prepared a software in Microsoft Access 2007 that export a query result to Excel. The data in Excel is fine, but I need a text column that includes the apostrophe at the beginning of the field but that apostrophe should not be visible in the cell but it should be visible in the formula bar.

If I try to add the apostrophe in the query (creating an expression field and concatenating the apostrophe with the value of the field) before exporting to Excel, the apostrophe appears in the formula bar and also in the cell. I cant permit that because this spreadsheet will be imported in a system which requires what I looking for.

Thanks in advance.


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
What method are you using to export the file to Excel?  Is it VBA-Excel native or CSV via manual export etc?

Author

Commented:
I am using a Macro to Export the file, using Microsoft Excel 5.0/95 Workbook type, including field names.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Are you able to post the code in the Macro?
Does it interact with Excel directly or does it just use the Access built-in export function?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
it just use the Access built-in export function.
Top Expert 2010
Commented:
I suspect you will be better off simply setting the number formatting for that column to Text, and getting out of the apostrophe business.The code below assumes that Col A needs that formatting, but that is easily changed.
Sub ExportToExcel()
    
    ' uses late binding for Excel
    
    Dim rs AS DAO.Recordset
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object
    Dim Counter As Long
 
    Const SaveToPath As String = "c:\Results\Report_"
    Const QueryName As String = "NameOfQuery"
 
    Set rs = CurrentDb.OpenRecordset(QueryName)
    
    ' instantiate Excel object
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.DisplayAlerts = False
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets(1)
    With xlWs
        ' write recordset headings
        For Counter = 0 To rs.Fields.Count - 1
            .Cells(1, Counter + 1) = rs.Fields(Counter).Name
        Next
        .[a:a].NumberFormat = "@"
        .Cells(2, 1).CopyFromRecordset rs
    End With
    
    ' Excel 2007/2010 requires the file format to be specified, so check
    ' for application version.  see for more info:
    ' http://www.dailydoseofexcel.com/archives/2006/10/29/saveas-in-excel-2007/
    
    If Val(xlApp.Version) < 12 Then
        xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xls"
    Else
        ' to use XLSX format:
        xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xlsx", 51
        ' or to use XLSM format:
        'xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xlsm", 52
        ' or to use XLSB format:
        'xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xlsb", 50
        ' or to use good old XLS format:
        'xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xls", 56
    End If
    
    xlWb.Close False
    xlApp.DisplayAlerts = True
    Set xlWs = Nothing
    Set xlWb = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    rs.Close
    Set rs = Nothing
 
    MsgBox "Done"
 
End Sub

Open in new window

Patrick,

The method CopyFromRecordset expects an ADO recordset up to Excel 2003. Does it work with DAO for later versions? In any case, if you want this function to be generic over versions, as indicated by the test below, you should open an ADO recordset.

mmoralespr,

Patrick's method above works as you want need it. If the query contains fields prefixed with single quotes, they will force Excel to treat the values as text, they will appear in the formula bar, but not in the cells. No need to format the column as text.

As you noticed, and as ciberkiwi hinted, this is not the case with the built-in export to Excel feature.

PS: no points, please, I'm just confirming what's been said (and asking a question of my own...)
(°v°)
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Okay, now that we know what you are using (Access built-in), here goes
You are right, when exporting using that method to Excel, it recognizes the ' as part of the data.
A workaround is required, which is to reopen the file and set it as the .Formula.
Have a look at the below.
I'm 99% sure that acSpreadsheetTypeExcel8 is 5.0/95.  Otherwise go for acSpreadsheetTypeExcel7.

The following exports a table named "tblMaterials" (something I tested with) and the offending column was #7.
You could also use .Value = .. .Value.
Sub export()
Dim strFilename As String
strFilename = "C:\temp\FromAccess.xls"
DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel8, TableName:="tblMaterials", FileName:=strFilename

Dim excel As Object
Dim book As Object
Dim sheet As Object
Set excel = CreateObject("Excel.Application")
excel.DisplayAlerts = False
Set book = excel.workbooks.Open(strFilename)
Set sheet = book.worksheets(1)
sheet.Columns(7).Formula = sheet.Columns(7).Formula
book.Save
excel.Quit
End Sub

Open in new window

Top Expert 2010

Commented:
harfang,Excel will still take a DAO or an ADO recordset in 2007/2010.  But you made a good point about checking.Patrick

Author

Commented:
Thanks for all your help. Regards.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial