Tab Delimited File Saves Commas As Dot

Jan 30, 2008

I'm using Excel 2000 swedish version, that is we use comma to seperate decimals not dot. I have a macro that saves a sheet as .txt file (tab delimited). However in the sheet (journal voucher) I need to enter a couple of numbers all with 2 decimal, that is 5 is 5,00 and so on. But then, when I save the sheet to a .txt file using a macro all the commas are saved as dots and since I'm importing the file to SAP it will not accept the dots. Is there some way ( changes in macro) I can prevent the commas from becoming dots when I save the .txt file?

The save macro is the commomly used

ActiveWorkbook.SaveAs Filename:= _
"C:Documents and Settings& strText & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate

View 4 Replies

Remove Inverted Commas In PIPE Delimited File Created From VBA

Jun 3, 2014

i have a Macro, its creating unwanted inverted commas at the start and end of the File.

File:

"SunilManual|123456790|DrivingLicence|908567543|Sunil|Manchan
|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|w|ww|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|55|55||4|SR-1161||1234567890"

Expected File:

|SunilManual|123456790|DrivingLicence|908567543|Sunil|Manchan
|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|w|ww|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|55|55||4|SR-1161||1234567890|

VBA:

Option Explicit
Sub txtt_export()
Dim buf As String, col As Long, r As Long, ws As Worksheet

[Code]....

View 2 Replies View Related

Little Data Saves As A Large File

Jan 9, 2008

I have some spreadsheets one worksheet little data and it saved as it a 5MB file when it should only save as something like 500Kb, what would cause this?

View 3 Replies View Related

After Macro Saves File Msgbox To Confirm Save As Completed

Jan 18, 2014

I have a macro which works well. All it does is save a file to a specified location on my network. However, after a file is saved there is no prompt telling the user that the file was saved successfully leaving them wondering whether the macro actually worked.

VB:
Public Sub SaveAs() ThisFile = Range("X2").Value
ActiveWorkbook.SaveAs Filename:="legdcAudits" & ThisFile & ".xlsm", FileFormat:=52
End Sub

View 1 Replies View Related

Saves A Template With Macro (XLTM) As A Normal File (XLSX)

Sep 10, 2009

When I prepare a file with macro's (to be used by other people), I save this file as a template with macro's with the extension *.xltm (template with macro).
Therefore no one can overwrite my file. These other people can open this template (e.g. double-click, but not file-open). But when these people save this file, Excel automatically saves it as a normal Excel-file with the extension *.xlsx.
Ok, a message is seen after pressing <enter> or clicking Save, but does normal users know, what they have to do then?

If I'm making a template with macro's, I want Excel to have this file saved as a file with macro's. Whatever did I make a file with macro's for? Is this a bug or is it done on purpose?

View 3 Replies View Related

Excel 2007 :: Macro That Saves To A Specific File But Has Spaces In It

Jun 24, 2013

I am using Excel 2007. I have pasted my code below. It works fine unless it has a space, in which case it inserts %20. In this case, sRange2 = ActiveSheet.Range("E11") will always have a space in it since this is where i store a job's name.

Sub Macro1()
Dim sRange1 As String
Dim sRange2 As String
Dim sRange3 As String
Dim sRange4 As String
Dim sFullPath As String

[code]....

View 4 Replies View Related

Excel 2010 :: Right Click (save As) Saves Inoperative XLSM File

Mar 22, 2012

Excel 2010, Win 7. I have attached a .xlsm file to an email. The contained macros run perfectly, and one of them is "public" so it shows up in "Developer - Macros", and "Customize Quick Access Toolbar" lists.

If the recipient clicks and drags the attachment to a folder, or copy/pastes the attachment, it works fine. (Macros are enabled, etc.)

However, if the recipient right clicks on the email attachment, and selects "SAVE AS" from the item list, the file saves, has the right name and extension, looks ok (the icon has the exclamation point, etc), and it is the correct size, but it simply will not load. You can double click, do a file open, etc. but it will not load. It's a hidden workbook, but if you "open it" and Alt-F11 to show the VBA editor, it isn't there!

It's not stopping the project - we simply tell them to click/drag, etc. but I totally do NOT understand why that happens.

View 5 Replies View Related

Convert Text Delimited File To .xls File Using Vba

Nov 28, 2008

how to word it but if someone understands then please help. I have two excel data files namely Book1.xls & Book2.xls. Both files have different data in it. Both files contain macros. When these macros run the files become **FINALIZED** version.

Originally, I get the above files in my email as txt. attachments. I then move these two txt files to my desktop in a folder called Folder-1. Then I open these files as an Excel and save them.

Basically, I need to know if two txt files are sitting in a folder-1 on my desktop. What can I do or what can I clik that....those two text files get converted into excel automatically, including running that macro I talked about in the above paragrah.

To put it differently, if I have two txt files Book1.txt, Book2.txt in a folder, how can I automatically create an excel **FINALIZED**version which sits right next to their txt version.

View 9 Replies View Related

Save AS Tab Delimited File For SAP

Feb 4, 2008

I'm trying to create a excel sheet with macros that will generate a tab delimitd file which is a journal voucher and I want to upload that .txt file to our financial system (SAP). When I create a tab delimited file manually, that is I enter all the values in excel and save as .txt it works great. But I have created a spreadsheet where the useres (all employees on the financial departement) enter the info required. I have a macro validating that all fields are correct and so on.. When they are finished they sedt the spreadsheet to me (an excelfile) via email, this is done by a submit buttom(macro) and the I have a hidden sheet in the worksheet which gets all the values that are necessery and then I create my tab delimited file using the well known save as fileformat=xltext macro. But SAP will not accept this file. I get error messages as convertion erros in line 4 column 6 and so on. The sheet which is saved as .txt is 100% with formulas, it gets all the value from another sheet(sheet1). I use the formula =IF(Sheet1!$E17=0;"";Sheet1!E17) in the cells with E17 changing to whatever cell is required. What can be wrong here? May it be that all the formulas (columns A to H and row 2 to 1001) somehow mix it all up and even to the cell is empty is the formula is somehow hidden in the .txt file and that messes it all up??

View 2 Replies View Related

Convert XLS To Pipe-delimited TXT File

Jul 15, 2009

I have the following code (borrowed) which converts the current .xls worksheet to a tab-delimited .txt file. The problem is that i need to add a PIPE to the end of each row/record as well, so that the records would look something like this:

A|123|
B|456|

currently there is no PIPE following the last character (3 or 6) and i am getting this:

A|123
B|456

I was hoping there would be a way to revise the VBA to add a PIPE at the end of each row/record.

Here's the code:

[Code] ......

View 10 Replies View Related

Convert .xls To Pipe-delimited .txt File

Jul 15, 2009

View 4 Replies View Related

Saving A File To Asterisk Delimited?

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

VBA To Read In Delimited Text File

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

Saving To The CSV (comma Delimited) File

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

Text File But Comma Delimited

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

Importing Tab Delimited Text File

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

Macro To Save As CSV File With Extra Commas

Mar 6, 2012

I created a macro to save an active sheet as a CSV file. Two issues arise out of that effort. One is that I can't seem to get the environment variable right to allow any user to save the file to the root of their My Documents folder on their home directory in a domain environment. That way the template will work for everyone and requires no user intervention. Tried using the % with HOMEPATH, but I must somehow have the syntax off.

Secondly, the file being saved as a CSV file is creating a huge several megabyte file with empty lines of commas.

Sub Macro2()
Sheets("Results").Select
ActiveWorkbook.SaveAs Filename:= _
"\%HOMEPATH%My DocumentsTesting.csv", FileFormat:=xlCSVMSDOS, _
CreateBackup:=False
Application.WindowState = xlNormal
End Sub

Thought it should be CSV MS DOS file type, but that doesn't seem to work either. I only want to save the rows with data on the active sheet to a CSV file, which will not have all the extra lines of commas.

View 1 Replies View Related

Prevent Multiple Commas In Text File

Nov 30, 2007

I have a spreadsheet that is produced by a macro. This spreadsheet has a table of 30 rows and 24 columns pasted into it from another spreadsheet. The first 6 rows of vary between containing 1 and 6 columns of data. The next 17 rows always have 24 columns of data. The last row has 1 column of data. The macro saves this spreadsheet as a .csv file. The macro works great, but when I open the .csv file in notepad all the rows have 23 commas, even for the rows with only a few columns of data. Here is a shorten example of the file when opened in Notepad....

<GTS-IPL-Customer>,,,,,,,,,,,,,,,,,,,,,,,,
H1,October,2007,,,Normal,,,,,,,,,,,,,,,,,,,
H2,000426,,,,,,,,,,,,,,,,,,,,,,,
H3,2.72283E+12,,,,,,,,,,,,,,,,,,,,,,,
H4,88100031,,,,,,,,,,,,,,,,,,,,,,,
H5,NBPL,,,,,,,,,,,,,,,,,,,,,,,
D,10012007,0,312,2,310,304,1006,306,306,4,4,31,4,0,0,0,,,,,,,5.805,-23.22
D,10022007,0,334,2,332,330,1009,333,639,-1,3,33,1,0,0,0,,,,,,,6.165,6.165
D,10032007,0,316,2,314,302,1045,316,955,-2,1,31,2,0,0,0,,,,,,,6.675,13.35

Is there anyway to have the macro not insert the commas in the first 6 rows for columns on the right side of the last column of data? I need the data to be formatted in the format shown below.

<GTS-IPL-Customer>
H1,October,2007,,,Normal
H2,000426
H3,2.72283E.....................

View 9 Replies View Related

Editing Contents Of A Text File Which Is Delimited

Aug 2, 2009

I have a text file, which has a column of wrong data. I can change it manually by loading it into Excel but it is semi-colon delimited and I can't export to such a file.

I therefore, would like to write a VBA tool, which reads the file and edits the the value between the 5th and 6th semi-colon and will continue do this for each row (the new value is constant across the rows).

View 9 Replies View Related

Select And Import Delimited Data File (ERA)

Feb 27, 2012

I need to summarize some data from multiple files but I can't seem to have the user select the file and import it.

It's a ERA(electronic remittance advice) 835 file that will import with the "Import Text File" routine. I've added the "GetOpenFileName" to it but apparently have not appropriately adjusted my "QueryTable.Add" routine.

I removed the .Refresh Backgroundquery:=False line as this was causing an error and this not SQL data.

The desired files have names like this: C123456.835.EDIPROCESSERPROCEEDED

My simpleton code reads:

Sub IMPORT_835()
' IMPORT_835 Macro
Dim InputFile As Variant

InputFile = Application.GetOpenFilename(Title:="Choose your file", _
FileFilter:="All Files (*.*), *.*")

[Code] ........

No data is imported, Why?

View 9 Replies View Related

Excel - Default Tab Delimited Text File

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

Create Individual Comma Delimited Cells From Delimited Cell

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

Visible Sheets To Be Hidden With The Exception Of The Sheet Called "open". Then Is Saves The File

Sep 9, 2008

i am using the macro below to get excel to hide all sheets but the one called "open"...

PHP
Sub�savemini()

ActiveWorkbook.Unprotect�"letmein"

��For�Each�sh�In�ThisWorkbook.Worksheets
��If�Not�sh.Name�=�"open"�Then�sh.Visible�=�xlVeryHidden
��Next�sh

��ActiveWorkbook.Save
End�Sub�

So the workbook is protected with the password "letmein", and the VB code above in theory should unlock the workbook, allowing all visible sheets to be hidden with the exception of the sheet called "open". Then is saves the file.

Now heres the odd bit... it works for certain people, but not for others. I have made sure those it wont work for are not doing anything weird and they are not.

The workbook i am using has a code that only opens certain sheets for certain users. I as a master user have access to all sheets. I can go into the users sheets and click the button that activates the macro above myself and it works fine, but for some users it wont work.

View 9 Replies View Related

Saving A Worksheet As A Asterisk Delimited Text File?

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

VBA To Save Single Sheet As Tab Delimited Text File

Jun 5, 2014

Is there any way to write a macro that will take a specific sheet in a workbook and save it as a tab-delimited text file?

View 1 Replies View Related

Save Excel File To CSV With Blank Cells With Formula Without Getting Commas?

Mar 20, 2014

I am working on a excel file that has a sheet that i want to convert to .csv. The sheet has formula's for the first three column that are warped an if statement that returns "" (blank) if certain conditions are not meet. However when i got to save the file as a csv i get a file with ",,,,,,," where there is no data. I would like to save csv and have only commas where this data.

Example
When i convert a sheet like
Firstname Lastname Password
Tim Cats Bo3n9x

it saves as
Firstname Lastname Password
Tim Cats Bo3n9x
,,,
,,,
,,,

In the .csv file.

Files i am current working on are attached.

View 13 Replies View Related

Excel 2010 :: Export Selection To MSDOS CSV File With 12 Commas?

Nov 28, 2012

Currently using Excel 2010 and looking for a macro that will export a selection (will be part of a column that I select) and export as an MS-DOS CSV file (no quotes around values, each row on different line) with 12 commas after each value in the selection.

Attached is an example of how I would like the output to appear, with the selected values only have four numbers in them. If possible, I would like a dialog box to appear for saving the file to a directory and name that I chose.

_________________Sample________________

1234,,,,,,,,,,,,
4567,,,,,,,,,,,,
9876,,,,,,,,,,,,

View 1 Replies View Related

Transpose Data From Rows To Columns, Export To A Tab Delimited File

Mar 12, 2003

I was wondering if their is a simple macro to transpose data from rows to columns so I can export to a tab delimited file. This particular list is 5 lines underneath each other and then the next entry.

There are no spaces between entries.

View 9 Replies View Related

Macro To Save Excel Sheet As Comma Delimited Text File?

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.