Solved

Convert into text format

Posted on 2007-03-20
3
191 Views
Last Modified: 2010-04-30
Hi: Can any one please tell me how to i convert the .xls file into text format with comma delimaters

Thanks.
0
Comment
Question by:mustish1
[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
  • 2
3 Comments
 
LVL 14

Expert Comment

by:Matti
ID: 18759155
Hi!

Have a reference in the Vb project Microsoft Excel XX.0 Object Library

Private Sub Command1_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\x\My.xls")
Set xlSheet = xlBook.worksheets(1)
xlSheet.Activate
xlBook.SaveAs "C:\x\Test.txt", xlCSV
xlBook.Close SaveChanges:=False
xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Sub


Matti
0
 

Author Comment

by:mustish1
ID: 18759295
strDestination is the path and the filename in which i need the data as a comma delimated text file from temp032007.xls Can you please tell me how to i do that? I try to save at the end
xlWb.SaveAs strDestination, xlCSV
but it gives error:
Cannot access 'New New_Import_PCDI03_032007.txt'

vpath-->C:\Documents and Settings\khamu443\My Documents\temp032007.xls
strDestination-->C:\REMITandNSF\New_Import_PCDI03_032007.txt

Thanks.

ORIGINAL CODE
Private Sub excelfileprocess()
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim rngColumn As Excel.Range
Dim rngCell As Excel.Range
Dim strDestination As String, vpath As String, vfile As String
Dim WShell As Object
Dim mysheet As String
Dim lngRow As Long
Const xlCenter = -4108
Set WShell = CreateObject("wscript.shell")
strsource = InputFileText.Text
vpath = WShell.SpecialFolders("MyDocuments") & "\" & "temp" & Format(Date, "MMDDYY") & ".xls"
Debug.Print "vpath-->" & vpath
FileCopy strsource, vpath
strDestination = OutputFileText.Text
Debug.Print "strDestination-->" & strDestination
Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open(vpath)
mysheet = xlApp.Worksheets(1).Name
Set xlWS = xlWb.Worksheets(mysheet)
xlWS.Range("A:A,B:B,D:D,E:E,G:G,H:H,I:I,K:K,L:L,M:M,N:N,O:O,P:P,R:R,S:S,T:T,U:U,V:V,W:W").Delete Shift:=xlToLeft
xlWS.Columns("A:A").Insert Shift:=xlToRight
xlWS.Columns("E:E").Cut
xlWS.Range("A1").Select
ActiveSheet.Paste
xlWS.Columns("B:B").Insert Shift:=xlToRight
xlWS.Columns("E:E").Cut
xlWS.Range("B1").Select
ActiveSheet.Paste
xlWS.Columns("A:H").EntireColumn.AutoFit
xlWb.SaveAs strDestination, xlCSV
xlWb.Close SaveChanges:=True
Set xlWb = Nothing
Set xlWS = Nothing
xlApp.Quit
Set xlApp = Nothing
End
End Sub
0
 
LVL 14

Accepted Solution

by:
Matti earned 500 total points
ID: 18759382
Hi!

Try to write in My Documents folder, do you have privileges to C:\REMITandNSF

Matti
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This video teaches viewers about errors in exception handling.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

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