[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Convert into text format

Posted on 2007-03-20
3
Medium Priority
?
199 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 2000 total points
ID: 18759382
Hi!

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

Matti
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
Suggested Courses

656 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