Link to home
Start Free TrialLog in
Avatar of ArnoldJohn
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
Avatar of Dave_Greene
Dave_Greene

 Dim appXL As New Excel.Application
 'Set appXL = CreateObject("Excel.Application")

  appXL.Workbooks.Open "C:\file.Xls"
  appXL.ActiveWorkbook.SaveAs "C:\file.txt", "Text"
  appXL.Quit
You may have to change to the following (not 100% sure)

appXL.ActiveWorkbook.SaveAs Filename:="C:\file.txt", FileFormat:=xlText
ASKER CERTIFIED SOLUTION
Avatar of Alon_h
Alon_h

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ArnoldJohn

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").Select
    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.Application")

to

Set xlApp = New Excel.Application


early binding is always faster than late binding I have heard...
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").Select
    Range("A2").Select
    ActiveSheet.Paste

But I dont have sheet like New Customers in my work book

Arnold
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").Select
    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

Avatar of DanRollins
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
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