Excel Automation - Export Txt

[Guest]

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

 

[Peter T]

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

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", _

 

[sam]

Hey Peter, I have a similar problem like this when I try to connect the excem
form I created to Access database. On my excel user form, Once a user clicks
submit button the data would be populated in the access database. I am
getting the error "User-Defined type not defined" Here is my code to connect
to the Access DB(Code is in Submit_Click() section of the excel form vba
script)

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\spatel\Desktop\Demo;"

' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Demo_Table", cn, adOpenKeyset, adLockOptimistic, adCmdTable

' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0

' repeat until first empty cell in column A
With rs

.AddNew ' create a new record
' add values to each field in the record

.Fields("Loan Officer Name") = Range("A" & r).Value
.Fields("Cost Center") = Range("B" & r).Value
.Fields("Loan Account #") = Range("C" & r).Value
.Fields("Loan Officer Phone #") = Range("D" & r).Value
.Fields("Contact Person Name") = Range("E" & r).Value
.Fields("Contact Person Phone #") = Range("F" & r).Value

.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Please Help.

Thanks in Advance.

 

[Patrick Molloy]

i think you need to add
rs.Update
immediately before
rs.close

at which line do you get the error?

 

[sam]

Hey Patrick, I am getting an error If I put rs.Update immediately before
rs.close

Error message: Either BOF or EOF is true, or the current record has been
deleted.
Requested operation requires a current operation.

Thanks in Advance.