Solved

convert excel into text file

Posted on 2001-07-24
10
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6313985
 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
ID: 6314023
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
ID: 6314647
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ArnoldJohn
ID: 6315353
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
ID: 6315361
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
 

Author Comment

by:ArnoldJohn
ID: 6315367
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
ID: 6315401
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
ID: 6315483
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
ID: 7161490
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
ID: 7182214
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

696 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