Excel VBA Numberformat="@" not working

Question

I am writing a piece of VBA in Access 2010 to Link data to an Excel file. I am trying to set the format of all the cells to "text" format instead of "general". I want Access to see all of the columns as "Text" and not "Number" depending on what data is in the first few rows. 

I have tried several different ways of doing the selection, only selecting certain columns or specific cells and they all seems to work fine. When I open the Excel file up in raw Excel, the selection that I specified shows on the screen (for example everything in columns B through N). Everything seems to work except the file that is saved still has all of the cells as "General". I have put a breakpoint right before the SaveAs and the Format is "General" at that point - not Text.

Any ideas what i am doing wrong?

Thanks,

Steve

Sub OpenRMAFile(strPathToFile As String)

    Dim objXL As Excel.Workbook

    On Error Resume Next

    DisplayAlerts = False

    Set objXL = Workbooks.Open(strPathToFile)

    objXL.Sheets("RMAFix2011-11-15").Select

    objXL.Sheets("RMAFix2011-11-15").Selection.NumberFormat = "@"

    objXL.Application.Visible = True

    objXL.Parent.Windows(1).Visible = True

    objXL.SaveAs FileName:= _

        "\\Server\sys\DataFile.xls", FileFormat:=xlExcel8, _

        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _

        CreateBackup:=False

    objXL.Close

End Sub

Answer 1

Any ideas what i am doing wrong?

Nothing, this behavior is normal. The change of a cell format did not change the current value of the cell, it changes only the style that this value is shown.

You must store each value in each cell again. Change the lines

  objXL.Sheets("RMAFix2011-11-15").Select

  objXL.Sheets("RMAFix2011-11-15").Selection.NumberFormat = "@"

to

  with objXL.Sheets("RMAFix2011-11-15").UsedRange

    .NumberFormat = "@"

    .Value = .Value

  end with

That converts anything to text values in that sheet (and removes formulas too).

Andreas.