Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Automation - Export Txt
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Automation - Export Txt
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Automation - Export Txt
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. "Peter T" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Automation - Export Txt
i think you need to add rs.Update immediately before rs.close at which line do you get the error? "sam" wrote in message ... 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. "Peter T" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Automation - Export Txt
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. "Patrick Molloy" wrote: i think you need to add rs.Update immediately before rs.close at which line do you get the error? "sam" wrote in message ... 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. "Peter T" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export Excel tuncating leading zeros while export to excel from da | Setting up and Configuration of Excel | |||
export re-order input fields to export file [csv] | Excel Worksheet Functions | |||
Supressing the ctrl-c and other keys during word automation in automation | Excel Programming | |||
Export Automation error | Excel Programming | |||
Excel Com Automation | Excel Programming |