Solved

Convert into text format

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
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…

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now