ArnoldJohn
asked on
convert excel into text file
Hi
I am trying to write a VBA that would read an Excel document and convert into text file.
The problem I have is the excel file that I am trying to read is not created with a sheet object(doesnt have a worksheet) .So I am not able to select an active worksheet to select columns of data to read thro and I dont have methods that I can select off a workbook instead of a worksheet.Is it possible to save a workbook as another file
assigning a worksheet to it.
Thanks
Arn
I am trying to write a VBA that would read an Excel document and convert into text file.
The problem I have is the excel file that I am trying to read is not created with a sheet object(doesnt have a worksheet) .So I am not able to select an active worksheet to select columns of data to read thro and I dont have methods that I can select off a workbook instead of a worksheet.Is it possible to save a workbook as another file
assigning a worksheet to it.
Thanks
Arn
You may have to change to the following (not 100% sure)
appXL.ActiveWorkbook.SaveA s Filename:="C:\file.txt", FileFormat:=xlText
appXL.ActiveWorkbook.SaveA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Alon.
But is there a way to associate this workbook object with a worksheet.This is because I am trying to read through
the workbook , copy the data from columns from the range I specify ,into a text file.For this I need a sheet object to select a range .The excel file I have is just a workbook and doesnot have a sheet onto which the data is laid out and I donot have methods to select a range of a workbook object.For eg I would like to do the foll
Sheets("New customers").Select
Range("A2:A200").Select
Selection.Copy
Sheets("newextract").Selec t
Range("A2").Select
ActiveSheet.Paste
But I dont have sheet like New Customers in my work book
Arnold
But is there a way to associate this workbook object with a worksheet.This is because I am trying to read through
the workbook , copy the data from columns from the range I specify ,into a text file.For this I need a sheet object to select a range .The excel file I have is just a workbook and doesnot have a sheet onto which the data is laid out and I donot have methods to select a range of a workbook object.For eg I would like to do the foll
Sheets("New customers").Select
Range("A2:A200").Select
Selection.Copy
Sheets("newextract").Selec
Range("A2").Select
ActiveSheet.Paste
But I dont have sheet like New Customers in my work book
Arnold
Alon_H:
Why use late binding if you can use early binding? You can change this:
Set xlApp = CreateObject("Excel.Applic ation")
to
Set xlApp = New Excel.Application
early binding is always faster than late binding I have heard...
Why use late binding if you can use early binding? You can change this:
Set xlApp = CreateObject("Excel.Applic
to
Set xlApp = New Excel.Application
early binding is always faster than late binding I have heard...
ASKER
Thanks Alon.
But is there a way to associate this workbook object with a worksheet.This is because I am trying to read through
the workbook , copy the data from columns from the range I specify ,into a text file.For this I need a sheet object to select a range .The excel file I have is just a workbook and doesnot have a sheet onto which the data is laid out and I donot have methods to select a range of a workbook object.For eg I would like to do the foll
Sheets("New customers").Select
Range("A2:A200").Select
Selection.Copy
Sheets("newextract").Selec t
Range("A2").Select
ActiveSheet.Paste
But I dont have sheet like New Customers in my work book
Arnold
But is there a way to associate this workbook object with a worksheet.This is because I am trying to read through
the workbook , copy the data from columns from the range I specify ,into a text file.For this I need a sheet object to select a range .The excel file I have is just a workbook and doesnot have a sheet onto which the data is laid out and I donot have methods to select a range of a workbook object.For eg I would like to do the foll
Sheets("New customers").Select
Range("A2:A200").Select
Selection.Copy
Sheets("newextract").Selec
Range("A2").Select
ActiveSheet.Paste
But I dont have sheet like New Customers in my work book
Arnold
ASKER
Thanks Alon.
But is there a way to associate this workbook object with a worksheet.This is because I am trying to read through
the workbook , copy the data from columns from the range I specify ,into a text file.For this I need a sheet object to select a range .The excel file I have is just a workbook and doesnot have a sheet onto which the data is laid out and I donot have methods to select a range of a workbook object.For eg I would like to do the foll
Sheets("New customers").Select
Range("A2:A200").Select
Selection.Copy
Sheets("newextract").Selec t
Range("A2").Select
ActiveSheet.Paste
But I dont have sheet like New Customers in my work book
Arnold
But is there a way to associate this workbook object with a worksheet.This is because I am trying to read through
the workbook , copy the data from columns from the range I specify ,into a text file.For this I need a sheet object to select a range .The excel file I have is just a workbook and doesnot have a sheet onto which the data is laid out and I donot have methods to select a range of a workbook object.For eg I would like to do the foll
Sheets("New customers").Select
Range("A2:A200").Select
Selection.Copy
Sheets("newextract").Selec
Range("A2").Select
ActiveSheet.Paste
But I dont have sheet like New Customers in my work book
Arnold
Here is a post from a recent question, Made By DennisBorg
Simple example of adding a WorkSheet to the ActiveWorkbook and specifying its name:
ActiveWorkbook.Worksheets. Add().Name = "TestMe"
To see if a worksheet exists with a certain name, you can use the following function:
Public Function WorkSheetExists(ByVal wsName As String) As Boolean
Dim ws As Worksheet
Dim rv As Boolean 'Return Value
For Each ws In ActiveWorkbook.Worksheets
If StrComp(wsName, ws.Name, vbTextCompare) = 0 Then
rv = True
Exit For
End If
Next 'ws
Set ws = Nothing
WorkSheetExists = rv
End Function
Sample code to delete a WorkSheet:
ActiveWorkbook.Worksheets( "Sheet2"). Delete
If you want a single sub to delete a worksheet if it exists, you could use the following along with
the WorkSheetExists given earlier:
Public Sub KillWorkSheet(ByVal SheetName As String)
If WorkSheetExists(SheetName) Then
ActiveWorkbook.Worksheets( SheetName) .Delete
End If
End Sub
Simple example of adding a WorkSheet to the ActiveWorkbook and specifying its name:
ActiveWorkbook.Worksheets.
To see if a worksheet exists with a certain name, you can use the following function:
Public Function WorkSheetExists(ByVal wsName As String) As Boolean
Dim ws As Worksheet
Dim rv As Boolean 'Return Value
For Each ws In ActiveWorkbook.Worksheets
If StrComp(wsName, ws.Name, vbTextCompare) = 0 Then
rv = True
Exit For
End If
Next 'ws
Set ws = Nothing
WorkSheetExists = rv
End Function
Sample code to delete a WorkSheet:
ActiveWorkbook.Worksheets(
If you want a single sub to delete a worksheet if it exists, you could use the following along with
the WorkSheetExists given earlier:
Public Sub KillWorkSheet(ByVal SheetName As String)
If WorkSheetExists(SheetName)
ActiveWorkbook.Worksheets(
End If
End Sub
Hi ArnoldJohn,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:
Split points between: Dave_Greene and Alon_h
ArnoldJohn, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you. DO NOT accept this comment as an answer.
EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:
Split points between: Dave_Greene and Alon_h
ArnoldJohn, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you. DO NOT accept this comment as an answer.
EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
Per recommendation, force-accepted.
Netminder
CS Moderator
Dave_Greene: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20328913
Netminder
CS Moderator
Dave_Greene: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20328913
'Set appXL = CreateObject("Excel.Applic
appXL.Workbooks.Open "C:\file.Xls"
appXL.ActiveWorkbook.SaveA
appXL.Quit