Excel 2007 :: Saving As Text Tab Delimited
Apr 18, 2013
Excel 2007 on windows 7 professional.
We've got an end user who opens a .dat file which is delimited with semi-colons, replaces some text in a couple of columns then saves as a text tab delimited file. She swears that before today, the saved file would retain the semi-colons. Now she says it's not. she generated a new .dat file for me and recreated the process, with the resultant semicolon-free file. I asked her to generate another one and send it to me. I opened it first in notepad, and saw the semi-colons.
Opened it in excel (I'm using 2010 on win 7 professional), which saw it as a delimited file and asked me to specify the delimiter. It shows up fine, all the columns are righteous. I made no edits and saved as a text tab delimited, and that saved file had no semi-colons in it when I opened it. This seems normal to me; there's no semi-colons in the excel file, so why would there be in the text tab delimited file. I asked her coworker to generate another .dat from his pc and let me know what he sees, but he hasn't reported back yet.
View 5 Replies
ADVERTISEMENT
Jun 18, 2008
I have a workbook with sheets of data that should be imported into another system. So i would like some code that does the following:
1.) make a copy of worksheet x and paste (paste special) the values in a new worksheet.
2.) Delete row 1 in new worksheet (the labels)
3.) Ask the user for a file name and path and save the worksheet as a tab delimited txt file.
4.) Delete the worksheet created in 1.)
5.) Leave the user with the original .xls file
I will assign this macro to a button so the experience for the user is:
1.) press button
2.) input file name and location and have a text file saved
3.) be left in the original xls document.
I tried with the following code but get stuck with this error : "Run-time error 91: Objeck variable or with block variable not set."
Sub Export_x_DK()
Dim w As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Export_2023"
Sheets("Depreciation x (DK)").Select
Cells.Select.........................
View 2 Replies
View Related
Sep 13, 2012
My set up win xp pro office 2007. Object is to save the text from a given cell to drive C naming the file with the value in another cell. E.g.
Contents of cell ("AA5") is a string and the content of ("D5") is a number
I have tried altering the code posted which i found on the forum, it works fine for the purpose as it was indented e.g. save the workbook and in my case saves it as the value in cell D5.
I guess I need to use some form of object to pass to the save as method but my knowledge of this is somewhat limited in addition as the is a possibility of either the cell being empty and the file name existing on the C drive, I need to disable the windows warnings and just go ahead and save it over writing what is on the C drive is of no importance as it will be the same data anyway, I can wrap the save in a "if Len ( AA5)= 0" to stop it trying to save if the cell if empty it the save the text string i am having problems with:
Option Explicit
Sub SaveAsExample()
Dim FName As String
Dim FPath As String
FPath = "C:"
FName = Sheets("Sheet1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & FName
End Sub
View 8 Replies
View Related
Sep 19, 2013
I am trying to save a worksheet as a txt file. The worksheet has a range of data with a variable amount of rows in column A only. It generally will look like:
A*B*C
A*B*C*D*E*F*G*H*I*J
A*B*C*D*E*F*G*H*I*J
A*B*C*D*E*F*G*H*I*J
where each row of data is in a single cell.Whenever I save it, however, it adds extra tab deliminations in each row after the data, and it adds rows with no data (sometimes just 1, sometimes many).
I am positive that no cells in columns B-n have data in them (even just a space) and no rows after the last intended have data.
Why are these extra rows and columns being inserted? How can I save a txt file that literally has no other spaces or rows or deliminations other than what's intended?
View 2 Replies
View Related
Oct 13, 2008
I have someone in the company that I work for that is using Excel 2007 in compatibility mode, that is trying to make changes to an Excel 2003 file.
The file in question is a large one. She can type in the rows and columns and anything that she types will save. However if she trys to make any changes to the file in terms on calculations or highlighting specific columns, then these changes are not saved.
I have found out though that if she saves this 2003 file in the 2007 format, then all over her changes will save. She can't save it in the 2007 format due to some people still on 2003 that edit this file as well.
View 10 Replies
View Related
Jul 14, 2014
When I converted excel to text file via VB code, the default text file is tab delimited. Why is it so?
I've done:
Code:
Sub ConvertToText()
ActiveWorkbook.SaveAs Filename:="E:EXCELTEST.txt", FileFormat:=xlCurrentPlatformText, CreateBackup:=False
End Sub
Can I change the default delimition to pipe, how can I do this? How can I make an excel file to pipe delimited text file in default?
View 3 Replies
View Related
Aug 30, 2013
I am trying to open up an asterisk delimited file then shrink the data, then after I want to save it back as an asterisk delimited text file. How would this be done in vba, as well as manually?
View 2 Replies
View Related
Dec 1, 2006
I have a work sheet with some names address, and phone number in it... I need to save it as a CVS file (comma delimited) easy right??? WRONG!!! everytime I save it, it takes the phone number column and shortens it AND turn the phone numbers into a mess
What it looks like NOW
9057926500
What is looks like after save
9.06E+09
If is make the column bigger it goes back to the "good" numbers, but when I save to a CVS it goes to the "bad" number.
View 9 Replies
View Related
Aug 29, 2012
I find, when performing a 'save as', xl puts in a default filename with a suffix (1), or (2), or (3) and so on, to the existing filename, after I have opened a file from MS Outlook. I had assumed it was because there was already an existing file in the default directory, and so it was creating another version, but no other files with the same name exist in the default directory being saved to, or on the remainder of my computer, so I don't understand why this occurs.
View 2 Replies
View Related
Dec 21, 2011
My set up office 2007 win xp pro ie8
is it possible using vba ideally with a button marked save, to save a single worksheet and name it, in CSV format
Then I would like to be able to do the reverse load a CSV file back in to a work sheet
Expanding upon this
1. Click a command button with caption “save”
2. Take the name of a given sheet, in my case DataToUpLoad and append it with a number to give a sheet name of DataToUpLoad 1 the first time it is save and DataToUpLoad 2 the next time and so on
3. Click another command button with the caption “load” interact with usual dialog box.
My current work around is to copy a sheet open a new work book past the sheet in the save that work book as CSV
The reason this is required is to mate up with the php on the server side
View 3 Replies
View Related
Apr 24, 2013
We are using Excel 2007.
I have a macro enabled spreadsheet and I need to save one sheet into a new file that is in xls format. I can do that --- is there anyway to suppress the Compatibility Checker box to make it that much easier?
The new file will not have macros as will just be a data in rows and columns so compatibility. Interesting is the compatibility checker box says there are links to the original spreadsheet, but I can't find them in the connections menu.
View 2 Replies
View Related
Apr 21, 2014
providing a macro to save an excel sheet to comma delimited txt file. Also, My sheet has 1st row as table columns and i dont want to export them in my txt file.
View 14 Replies
View Related
May 27, 2014
Macro to copy data from Excel sheet and creates a Pipe delimited text file.
View 10 Replies
View Related
Aug 16, 2012
I have a worksheet that has a number in cell K5 - the number is generated on "file open" code and is custom formatted as "TN"0000. Thus 1 appears as TN0001, 2 as TN0002 and so on. I am trying to save a copy of the workbook based on the this cells contents i.e. TN0001.xls, TN0002.xls etc. but the files are saved as 1.xls or 2.xls. The code I am using is
ActiveWorkbook.SaveAs Filename:="C:DataExcelFORMSDelivery Note" & Range("K5") & ".xls", _
FileFormat:=xlNormal, ReadOnlyRecommended:=True, CreateBackup:=False
I know I must make reference to format within the above....but how? if try something like
" & Format(Range("K5").Value, ("TN""0000")) & ".xls"
I get TN00000.xls
View 2 Replies
View Related
Aug 18, 2013
I am trying to find specific text throughout an excel document (2007) and replace it with the same text but in italics. I tried using the options/format function and selecting italics for the "replace" text but it replaces the entire cell text in italics instead.
View 5 Replies
View Related
Jan 16, 2014
I found this code that import TAB delimited text file. I would like to import space delimited text file instead.
VB:
Option Explicit
Sub ReadTxtFiles()
Const conSpath As String = "C:"
[Code]....
View 6 Replies
View Related
May 30, 2008
I need to create a comma delimited list based on variable start and end values for each row.
StartEndOutput List
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200420072004, 2005, 2006, 2007
200420082004, 2005, 2006, 2007, 2008
200520082005, 2006, 2007, 2008
200620082006, 2007, 2008
200820082008
I'm not a VBA expert, or I would have created a Do While or For Each loop.
View 4 Replies
View Related
Apr 29, 2009
I have a column of data consisting of Name and Number, for example:
ABC 123
ABC DEF 789
AB D EFG 567
Is there any solution that can parse the number from the Name, which returns:
ABC 123
ABC DEF 789
AB D EFG 567
View 2 Replies
View Related
Nov 5, 2011
Time spent on each page appears in row 18. Need to sum up all of these pagetimes for each site, to get the total time on site. So in the example below,
D19 would reflect the total time spent on GreenElectricalSupply.com sum of cells D18-G18(1:23.0)
and H19 would display the total time spent on Rexel.com (00:45.0)
So, how to do a conditional sum across row 18, until a new site begins (indicated by the appearance of text in the next cell of row 8).
Data
CDEFGHI8
greenelectricsupply.com
rexel.comnextag.com9WW ONLY: Arrived at site via
[Code] .....
Excel 2007
View 4 Replies
View Related
Jun 30, 2014
I have multiple items (Country Names) in cells exported from another database with what appear to be delimiters (semicolons) that cannot be counted. I want to count the number of items (Countries) separated by the delimiters. Is there a simple way to do this?
Column 1 fields:
TAIWAN, PROVINCE OF CHINA;
BELGIUM;HUNGARY;SWEDEN;ITALY;POLAND;SLOVAKIA;
CANADA;
BELGIUM;HUNGARY;SWEDEN;ITALY;POLAND;SLOVAKIA;CANADA;
INDIA;BRAZIL;DENMARK;GREECE;POLAND;MOLDOVA, REPUBLIC OF;HUNGARY;UNITED STATES;UNITED KINGDOM;
Result needed in Column 2:
2
6
1
7
9
What is the formula needed to get the Column 2 result?
View 3 Replies
View Related
Nov 8, 2009
Lets say I have in column A a series of cells populated with words or phrases delimited by ;; and I want them sorted within the cell. I.e.
This:
Word ;; Another word ;; This is a phrase
Should become
Another word ;; This is a phrase ;; Word
View 4 Replies
View Related
May 21, 2014
I've a word form that collects free text and tick boxes. I'm reading these into separate rows on an excel sheets. So far what works...
I've got excel vba converting the word to a delimited text file
I've got excel vba to remove the erroneous rtns that people have entered into the free text that was things up.
In the free text there are commas entered as well as the tick boxes and this is a csv. Example
"how do I do this, I don't know",1,1,0,1,"really seems to be a challenge!"
If I just use excel to open the text file then the columns work out ok and on a single row - I think the 'text qualifier' is playing a role here.
But I can't replicate this in VBA. If I record a macro it's a query table and I don't know how to amend the code to read into rows.
View 2 Replies
View Related
Sep 28, 2007
In my workbook I have 10 columns with data, starting at A12 and down. This could be several hundred rows.
I would like to export the first three columns only (Column A, B and C). The TXT file should have the following:
First line: "This file was exported from Excel"
Second line: The value of cell B4 of the worksheet
Third line: The value of cell B5 of the worksheet
Fourth line: Today's date ( as 27/Sep/2007)
Fifth line: No entries (Empty row)
Sixth line will be the first numeric transfer. This is cell A12 value, cell B12 value and cell C12 value.
Seventh line: Cell A13 value, cell B13 value and cell c13 value.
etc, until the end or selected amount of rows.
The values of the different columns should be separated by commas
Column A values could be 9 characters (5 numerics with 4 decimals - 12345.6789)
Column B values could be 10 characters (6 numerics with 4 decimals - 123456.7890)
Column C values could be 10 characters (6 numerics with 4 decimals - 123456.7890)
The text file therefor would look as follows:
This file was exported from Excel.
Company ABC
On this continent
27/Sep/2007
123.4567, 23.7654, 123.4567
1234.5678, 123.4567, 987.6543
12345.6789, 6.0000, 2.9876
I tried to adapt jindon's code but no luck.
I am currently doing it the long way.
='[Workbook1.xls]MySheet'!$A12&", "&'[Workbook1.xls]MySheet'!$B12&", "&'[Workbook1.xls]MySheet'!$C12
This works but I really liked what jindon did and wondered if it could be adapted to fit my needs.
View 9 Replies
View Related
Oct 11, 2008
In my excel file I have chinese and some other special characters and many cells have text with commas.
My problem starts when I try to save my file as a Text.
When I Save As with the Unicode option I can see the chinese characters in my text file but also I see a lot of quotes """ because I have commas in my xls file.
When I Save As with Tab Delimited option to solve the comma problem, the chinese characters become?
It seems that cannot be possible to do it manually cause there is no option to Save As with Unicode and Tab option together.
So I would deeply appreciate someone could give me a solution with VBA code to save my file as Text with Unicode and Tab Delimited option.
View 9 Replies
View Related
Dec 18, 2008
I am working with Excel 2003 which has a limit of 256 columns. I often import text files into excel using a very simple macro. I specify the text file's name and location in a cell and then run the macro - it opens the text file, copies the data and pastes it into the worksheet, then delimiting the columns using the semi-colons that seperated the data in the original text file.
However, I now have a text file that would convert into excel as around 1,000 columns and about 20 rows. This obviously causes a problem since my limit is 256 columns in Excel 2003.
Is there any way that Excel can transpose my data when it brings it into the spreadsheet from the text file so that I end up with 20 columns and 1,000 rows? Ideally I'd do this in VBA and add the code onto my existing little macro
View 9 Replies
View Related
May 4, 2009
am in a time crunch to write VBA code(not use the macro recorder) to do the following:
Input data from a tab delimited Text file into a pre-formatted sheet.
Each column of the text file is as follows (product code, buy/sell, quantity, price, date) Example row below.
YM Buy 50 12800 1/2/2008
Is there code I can use to do this? I need each individual piece of data to import into it's own cell while keeping the Row/column alignment of the original text document.
View 9 Replies
View Related
Feb 20, 2014
I export data into Excel format from a corporate reporting tool. 1 column includes a product description in text format, however, due to the many different products I need to count the number of cells based on a single word in the product description.
Hypothetical Example:
The report contains various information about vehicles. The product description exports to a single column and may include "Ford Fusion", "Ford Focus", "Chevrolet Malibu", "Chevrolet Impala", etc. I only need to count how many cells contain information about Fords and Chevrolets. The model detail is not needed.
I'm able to count if I enter the complete and exact make & model description, but want to avoid this due to the large quantity of products.
I'm using Excel 2007, on Windows 7, 64 Bit Enterprise
View 7 Replies
View Related
Nov 17, 2011
I am struggling with doing a sort, of a group of alpha characters within a cell (Excel 2007). My data was exported from Oracle 8 as a varchar2 and consists of letters.
Example of what I have
BAC
GHL
DFE
MEG
Example of what I want
ABC
GHL
DEF
EGM
All I could find were these complex looking solutions involving cursors, plsql, etc. I'm hoping there's a simple solution, maybe within Excel. I've tried different formatting, made sure no other characters were sharing the cells, and tried typing fresh data into a cell (instead of the exported data).
View 8 Replies
View Related
Jul 9, 2014
In Excel 2007, I would like to split Alpha Numeric Text to separate out Prefix portion of the text, example below represent Row no. 1, 2 , & 3
Original Text
Column A
Result A
[Code]....
In the same example, I would like to extract /Copy Result A or Result B into another cell.
View 2 Replies
View Related
Mar 1, 2013
Why can't I apply text functions on strings into the file? For example: can't apply "Text To Columns" delimited by space on "Status Entry Date" column.
View 3 Replies
View Related
