Solved

Convert into text format

Posted on 2007-03-20
3
183 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
  • 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
The purpose of this article is to demonstrate how we can use conditional statements using Python.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

808 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