Hi David,

It looks like you are using Late Binding, that is you have not set a
reference to the Excel object library in Access. Without it, Excel's named
constants xlText & xlLocalSessionChanges will not be recognised, instead
will be interpreted as undeclared, variables with of course no values
assigned to them.

In Excel's VBE press F2 and look up the constants in Object Browser. Or in
the immediate widow type ?xlText and hit enter (ditto
xlLocalSessionChanges), or simply Msgbox xlText (in an Excel module) .
Replace the named constants with their intrinsic values of
-4158 & 2 respectively.

In passing, it's rarely necessary to Select or Activate workbooks, sheets or
cells, eg
..Worksheets(1).Range("C11:C122").Copy .Worksheets(2).Range("A1")

Regards,
Peter T


"David" wrote in message
...
I am working with Excel automation in an Access form.
I have hit a dead end with exporting a worksheet out of excel as a tab
deliminated text file. I am just new with Excel Automation

My code looks like this

Private Sub Export_Click()
Set xlApp = GetObject(, "Excel.Application")

' Take range, put it into sheet2 and then save sheet2 as Tab
Deliminated
Txt file
With xlApp
.DisplayAlerts = False
.Sheets(1).Range("C11:C122").Select
.Selection.Copy
.Sheets(2).Select
.Sheets(2).Paste
'''''''The line below does not work?!''''''''
.ActiveWorkbook.SaveAs
Filename:="L:\Universal\Data\FlightData.txt", _
FileFormat:=xlText, CreateBackup:=False,
ConflictResolution:=xlLocalSessionChanges
End With

xlApp.Quit

Set xlApp = Nothing

End Sub

The saveas line that I have marked above does not work. It is giving me a
"Run-time error '1004' : SaveAs method of Workbook class failed"
I have tried running the following code in an excel macro and it works

ActiveWorkbook.SaveAs
Filename:="L:\Universal\Data\FlightData.txt", _
FileFormat:=xlText, CreateBackup:=False, _
ConflictResolution:=xlLocalSessionChanges

Is this not essentially the same code?
Why is this not working?

Thanks
David