Solved

convert excel into text file

Posted on 2001-07-24
10
467 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

739 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