Solved

Save excel file as text(tab delimited) file programmaticly(c#)

Posted on 2011-09-14
7
1,201 Views
Last Modified: 2012-06-21
Hello,

I have an excel file, can I save it as text(tab delimited) file programmaticly(c#)?
And vice versa?

Thanks for your code snippet.
0
Comment
Question by:zhshqzyc
  • 3
  • 3
7 Comments
 
LVL 6

Expert Comment

by:yjchong514
ID: 36537066
Method you are looking for is Workbook.SaveAs with parameter FileFormat=xlText
0
 

Author Comment

by:zhshqzyc
ID: 36537111
But there is not such a type "xlText".
Please see here.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 36537242
i looked at the link you provided and there are several CSV and Text formats to choose from - is there something more specific you are looking for beyond:-

xlCSV  Comma separated value.  
xlCSVMac  Comma separated value.  
xlCSVMSDOS  Comma separated value.  
xlCSVWindows  

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:zhshqzyc
ID: 36537295
tab delimited
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 36537571
A text file can't contain a macro so I am assuming you are using one workbook to save another - in which case you could just write the output as a data stream and use whatever delimiter you want.


Another option is to open a tab delimited file with dummy data in it - this will set the default for that worksheet to be Text (tab delimited) then clear it, populate it and when you save it it will use the default - though you will probably have to deal with the warning dialog making sure you want to save as text

0
 

Author Comment

by:zhshqzyc
ID: 36537800
Well, I need some code anyway. Can we save it as csv then save csv to text(tab delimited)?
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
ID: 36549393
The .csv filetype is assumed to use comma's as a seperator (the clue is in the name) and excel will try and open it that way. You can create a dummy file with tabs and that will force the sheet to be 'text (tab delimited)' then when you save it replace the extension and it preserves the tab format.

so your macro would have to open the dummy file, deal with the text import dialog, populate the sheet, and save with the new filename.

The alternative is to open a text file from within the macro, write your tab delimited records to it and the close it. To get a tab into the record you use chr(9)

MyStr = "Hello" & chr(9) & " World"

Open "TESTFILE.csv" For Output As #1
write #1, Mystr
Close #1

will give you a tab delimited file with a .csv filetype




0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

896 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

12 Experts available now in Live!

Get 1:1 Help Now