Excel Automation - Export TXT

[Guest]

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

 

[Ken Snell \(MVP\)]

It appears that this is strictly an EXCEL question -- you'll get more
complete assistance if you post in an EXCEL newsgroup. This newsgroup is for
ACCESS.

 

[Guest]

Hello Ken

I am trying to execute this code from within Access.
the code at the bottom works in Excel but the main code is not working in my
Access form. I was wondering if I didn't enter it quite right on the Access
side.

I have posted this in the excel newsgroup just in case this situation
doesn't make a difference to your comment.

Thanks
David

 

[RoyVidar]

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

You're quitting/releasing the xlApp variable within the routine, so it
should be declared within the routine too.

Dim xlApp as object ' or as excel.application

Copy/paste operations are rather slow, you could probably better do
somthing like

..Sheets(2).Range("C11:C122").Value = Sheet1.Range("C11:C122").Value
..Sheets(2).select
....saveas...

Then, when you do the export, be sure the location actually exists and
is ready. I will often create a file locally, then copy afterwards, if
it succeeded. So - first try with "C:\test.txt"...

I would usually do an explicit close of the workbook, too.

..ActiveWorkbook.Close

There might also be other reasons, for instance
http://support.microsoft.com/kb/199268
or other stuff (sharing...)

 

[Guest]

Hello

None of this worked to fix the problem, however, I looked up the constants
for xlText and found that if I entered that (20), it worked.
Why is that?

I even changed over to Early Binding and it would only accept the constant
value.
Any clues?

David

 

[Ken Snell \(MVP\)]

Yes, it is important to know that you're doing this code in ACCESS. Thanks.

You're using Late Binding for the EXCEL-related objects. As such, ACCESS
will not understand what the xl___ constants' values are -- ACCESS cannot
see the EXCEL library unless you've set a Reference to that library in the
ACCESS database file.

You need to replace those xl____ constants with their actual numeric values.
xlText has the value of -4158
xlLocalSessionChanges has the value of 2

 

[RoyVidar]

Hello

None of this worked to fix the problem, however, I looked up the constants
for xlText and found that if I entered that (20), it worked.
Why is that?

I even changed over to Early Binding and it would only accept the constant
value.
Any clues?

David

I can see it didn't work, as it was more than my usual amount of typos,
sorry.

.Sheets(2).Range("C11:C122").Value = _
Sheets(1).Range("C11:C122").Value
.Sheets(2).select

would probably work, and be faster than using the clipboard.

Since you used two excel constants, I was sure you intended to do early
binding, so I didn't comment on that. Both constants worked as they
should on my setup with early binding. Using late binding, you would
need the literals.

Why it didn't work even when you were using early binding, I don't know
but - xlText is not listed among the available fileformats (even if
that's what the excel wizard produces). The literal value of xlText is
-4158. You use 20, which is xlTextWindows (try that with early
binding?).

On my setup xlText worked just fine, perhaps because -4158 is also the
same as the format constant called xlCurrentPlatformText.

Perhaps this (-4158) wasn't possible with your setup? I don't know.

Glad you got it working!