Saving Sheet As Tab Delimited Text

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


ADVERTISEMENT

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 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

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

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

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.

View 14 Replies View Related

Importing Text Space Delimited Text File?

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

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

Text Delimited By Number

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

Count Delimited Text Within A Cell

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

Sorting Delimited Text Within Cell

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

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

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

Save As Text With Unicode And Tab Delimited

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

Transposing Delimited Text Files

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

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 Current Sheet As Tab Delimited

May 30, 2012

I've been searching for code that will perform the following:

1. Save the current workbook then,

2. Save the current sheet as a tab delimited file to the desktop with the same name as the current workbook then,

3. Return to .xlsx file format

The file saved to the desktop can be overwritten each time the macro is run.

View 4 Replies View Related

Can't Apply Text To Columns Delimited By Space?

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

VLookup And Update New Value In Delimited Text Having Alphanumeric

Dec 22, 2013

We want to insert new row by finding existing value of sheet1 and create new row with same content by replacing column B of sheet 2

Example1 : Sheet1 of Column A1 is having AHOPL5070B

Solution required : find AHOPL5070B from sheet2 of column B and update with inserting row with AHOPC5070B

Example for above:

429^TD^1^45055^1^D^11757^31032005^^AHOPL5070B
430^TD^1^45055^1^D^11757^31032005^^AHOPC5070B

MSN-TESTERmatchrow.xlsx

View 3 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

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

Import/merge Tab Delimited Text Files

Sep 9, 2006

is there any way for a worksheet to import and merge (append) tab delimited files from these directories:

C:TempmergeVdata.txt
C:TempmergeV (2)data.txt
C:TempmergeV (3)data.txt
C:TempmergeV (4)data.txt
C:TempmergeV (5)data.txt

- contents of data.txt have range A1:Pn, where 'n' is last row, different for each file
- found a previous thread very similar to what i wanted, but donno how to edit this to suit my purpose
[Solved] Importing: Import many TXT files to singe workshee

BTW, does VBA recognise directory folder with unicode characters? I can rename my directory if it doesn't.

View 3 Replies View Related

Macro To Copy Data From Excel And Creates Pipe Delimited Text File

May 27, 2014

Macro to copy data from Excel sheet and creates a Pipe delimited text file.

View 10 Replies View Related

Saving Just One Sheet

Jul 8, 2006

if it is possible to save just one sheet out of a workbook. I suppose it would save as a whole new workbook with just that one sheet, which is fine.. but is there a command that does this?

View 9 Replies View Related

Saving Excel Sheet As PDF?

May 26, 2013

I got a problem saving an Excel Sheet as PDF. The problem is that i cant see some parts of sheet on the PDF file. I have uploaded 2 files one of which is that .xlsx file which i want to save as PDF, the other is the PDF file that i see after saving which is not desirable.

View 1 Replies View Related

Sheet Not Saving To Location

Feb 3, 2014

In the attached sheet in module5 I have a save macro with this code

HTML Code: 

ActiveWorkbook.SaveAs Filename:="D:Picking Logs" & _
Replace(sFile, "/", "-"), FileFormat:=52

-This saves it to my computers D drive and works fine.

Then I also have the following code whice is suppose to save it to the public drive which is U: drive, but this computer does not have direct access that that drive so i save it to

HTML Code: 

ActiveWorkbook.SaveAs Filename:="wcrsan1
owley public1 picking logs" & _
Replace(sFile, "/", "-"), FileFormat:=52

-The wcrsan1 is the path i use to save it to th U:drive.

My issue is the code runs fine, but it does not save it to wcrsan1 owley public1 picking logs. If i save it manually it works but not when the macro runs it.

VBA is password protected.. The password is Kayley98 the sheet password is go

ho.xlsm

View 2 Replies View Related

Saving Excel Sheet As TXT?

Jan 27, 2012

I've got a macro that generates some parameters and after they have been generated, the values have to be saved in text file. The problem that I'm facing is that the text file saves the values in double quotes.

I've been searching online for a solution to the problem and what I've found is that if the cell value in excel includes a comma, the resultant text file will store the entire value in double quotes.

I'm using the save as method and I read that if you provide the save format as xlTextPrinter this problem will not occur, but the limitation is that it can only work with values less than 240 characters in length.

View 5 Replies View Related

Return To First Tab In Sheet After Saving

Aug 11, 2009

I am working on a project for work and my first tab is titled "Directions". I have over 500 users coming in and edit multiple tabs at the same time. Is there a macro/or option that would after a user saves changes, would return to the first tab "directions" so that my other users wont be in the middle of what the last person edited?

View 9 Replies View Related

Saving Worksheets As Text

Jul 22, 2007

I have this code (got it from here: [url] that saves worksheets as separate workbooks. It works perfectly fine for that but I would like to save each worksheet as a text file (tab delimited). I tried changing FileFormat:=xlNormal to FileFormat:=xlFile but a debug error occurs.


Sub MakeMultipleXLSfromWB()
'Split worksheets in current workbook into
' many separate workbooks D.McRitchie, 2004-06-12
'Close each module AND the VBE before running to save time
' provides a means of seeing how big sheets really are
'Hyperlinks and formulas pointing to other worksheets within
' the original workbook will usually be unuseable in the new workbooks.
Dim CurWkbook As Workbook
Dim wkSheet As Worksheet
Dim newWkbook As Workbook
Dim wkSheetName As String
Dim shtcnt(3) As Long

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved