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
ArnoldJohnAsked:
Who is Participating?
 
Alon_hConnect With a Mentor Commented:
Hi ArnodJohn.

Try this :

1. Save excel file with name 'Test' in 'c:\'.

2. Open new vb project.

3. Add References : Project -> References ->
     'icrosoft Excel 8.0 Object Library'

4. Add command1 and this code :


Private Sub Command1_Click()
Dim xlApp As New Excel.Application
Dim xlWBS As Excel.Workbooks
Dim xlWB As New Excel.Workbook
   
    Set xlApp = CreateObject("Excel.Application")
    Set xlWBS = xlApp.Workbooks
   
    xlWBS.Open "C:\Test.xls"
   
    Set xlWB = xlWBS(1)

    xlWB.SaveAs FileName:="C:\Test.txt", _
        FileFormat:=xlText, CreateBackup:=False

    xlApp.Quit
   
    Set xlWB = Nothing
    Set xlWBS = Nothing
    Set xlApp = Nothing
   
End Sub

This code get the file 'c:\Test.xls' and
  Save it like text file in 'c:\Test.txt'

0
 
Dave_GreeneCommented:
 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
0
 
Dave_GreeneCommented:
You may have to change to the following (not 100% sure)

appXL.ActiveWorkbook.SaveAs Filename:="C:\file.txt", FileFormat:=xlText
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ArnoldJohnAuthor Commented:
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
0
 
JonFish85Commented:
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...
0
 
ArnoldJohnAuthor Commented:
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
0
 
ArnoldJohnAuthor Commented:
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
0
 
Dave_GreeneCommented:
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

0
 
DanRollinsCommented:
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
0
 
NetminderCommented:
Per recommendation, force-accepted.

Netminder
CS Moderator

Dave_Greene: points for you at http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20328913
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.