Solved

convert excel into text file

Posted on 2001-07-24
10
459 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:ArnoldJohn
10 Comments
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
 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
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
You may have to change to the following (not 100% sure)

appXL.ActiveWorkbook.SaveAs Filename:="C:\file.txt", FileFormat:=xlText
0
 

Accepted Solution

by:
Alon_h earned 50 total points
Comment Utility
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
 

Author Comment

by:ArnoldJohn
Comment Utility
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
 
LVL 6

Expert Comment

by:JonFish85
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ArnoldJohn
Comment Utility
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
 

Author Comment

by:ArnoldJohn
Comment Utility
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
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
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
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
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
 
LVL 5

Expert Comment

by:Netminder
Comment Utility
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now