Save As Macro :: Tab Delimited Files

Oct 22, 2009

I use a macro that saves my activesheets in text(tab delimited).

I am trying to find how to save my worksheets as tab delimited files without having to open the notepad later, in order to press the backspace button. Just to clarify more, if a sheet has 15 rows of data, the tab delimited file will be created with 16. Is there a way to save the actual number of rows in the text file or this is a default operation in excel that cannot be changed in any way?

ADVERTISEMENT

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

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

Convert Csv Files To Tab Delimited Files

Dec 21, 2007

I would like to be able to execute a command line that will convert an Excel csv file to an Excel file that is TAB delimited. In other words, replace the comma delimiter with a TAB delimiter without having to open the file in Excel. I am an inexperienced Excel user

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

Open Txt Files Delimited, Space

Sep 6, 2008

On a daily basis I open about 35 text files in Excel at one time, and then must responde to 35 dialog boxes:

"This file is not in a recognizable format" - I click OK
"Delimited or Fixed Width (default)" - I change to Delimited
"Delimiters listed with Tab as default" - I change to Space

I go through that 35 times. I don't save these files that I have opened, but once they are all open I paste each one in a separate worksheet in one workbook.

Can I at least change the defaults on these text boxes to Delimited, Space? That way I would just need to click on OK, then Finish for each file.

I am using Excel 2000.

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

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

Macro To Save Excel Files As PDF With Password

Mar 3, 2014

I am looking to convert 2000 excel files in PDF and include a unique password (password ideally will be their employee number). Is this possible using VBA, I am thinking that if I have a seperate excel file with the name of each file and the applicable password it might be but not sure?

View 3 Replies View Related

Open, Edit, Save Macro - Various Files

May 22, 2006

i've got the code below, and if you notice the directory and file line, I want a loop that'll do what the macro below does, but also for files j1k2-j1k200. I'm hoping there's a macro that'll be able to do this, as I don't want to have to copy, paste and edit the same macro below 200 times!

Application. ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("C:Uni StuffStocks_J20j1ka.xls", False, False)
Sheets("Sheet1").Select
Range("B6:S6").Select
Selection.Replace What:="]w1", Replacement:="]w2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
wb.Close True ' close the source workbook saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

View 5 Replies View Related

Open All Txt Files (delimited Tab Comma ) In A Folder

Aug 11, 2009

I have around 30 files each quarter which I need to convert from a text file to an excel spreadsheet. I am a beginner with VBA and am looking for generic code I can use for a macro to:

1. open a text file from a folder
2. delimited/tab/comma
3. format columns H, O, and AH into dates
4. 'leave a spot for me to insert my code to manipulate the data'
5. save the file as an excel spreadsheet to a folder with the same filename
6. loop to perform this task to all files in a folder and stop after the last file.

The dilemna I am having is that my text files do not have a suffix ".txt" after them.

They just have the file name ***MMDDYYYY. There are always 3 initials at the beginning which change for each file i.e. ABC06302009.

The date remains the same for the given quarter, i.e. ***06302009. Next quarter I will have to do this same thing for all files ***09302009.

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

VBA Macro To Follow Hyperlinks & Save Files To Another Location

Jan 16, 2010

I am attempting to write an Excel macro that will be stored in a file called MacroFile. The purpose of the macro is to

1. Follow a hyperlink to an Excel file saved in a SharePoint type enviroment
2. Save the file to my laptop directory My Documents.

Below is the code I have written. The code is following the hyperlink and saving a file but is the focus file is incorrect.

Here is what happens:

1. Open up MacroFile and run macro
2. Hyperlinked file LinkedFile_1.xls is opened
3. File NewFile_1 is saved but contains the info from MacroFile
4. Hyperlinked file LinkedFile_2.xls is opened
5. File NewFile_2 is saved but contains the info from LinkedFile_1
6. Hyperlinked file LinkedFile_3.xls is opened
7. File NewFile_3 is saved but contains the info from LinkedFile_2

The files created are named correctly but have the wrong data in them. I need to know how to control which file is considered ActiveWorkbook.

Sub LinkAndCopy()
Application. ScreenUpdating = False
Application.DisplayAlerts = False
'**** Copy LinkedFile_1..................

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

Macro To Save Files In New Folder With Current Month And Date

May 6, 2014

I have this existing macro which saves each tab into a separate excel file. However, I'd like for it to also save them together in a new folder using the current month and date (named: QA Files May_05.06.14). The month and date would change according to current month/date. How would I incorporate that into this code? I am not good with macros.

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.Name = Range("D1").Value
Next

[Code]...

View 4 Replies View Related

Excel 2007 :: Save Files XLS In Macro Workbook And Send Email

Sep 3, 2012

I am using 2007. This may be something simple but I can't seem to find a solution to this, I have an excel macro enabled workbook which produces a daily report the final task is to save and send an email but I need the file to save as an xls rather than xlsx as some of the recipients are unable to open the file here is the code used for the tasks in the

workbook:Sub Step1_Refresh()
'
' Step1_Refresh Macro
'
'
ActiveWorkbook.RefreshAll
End Sub
Sub step2_save_close()

[Code] ........

View 1 Replies View Related

Excel 2007 :: Save Files XLS In Macro Workbook And Send Email?

Sep 3, 2012

I am using 2007, I have an excel macro enabled workbook which produces a daily report the final task is to save and send an email but I need the file to save as an xls rather than xlsx as some of the recipients are unable to open the file.

Here is the code used for the tasks in the:

workbook:Sub Step1_Refresh()
'
' Step1_Refresh Macro
'
'

[Code]....

View 7 Replies View Related

Excel 2010 :: VBA Code To Save File As Semi Colon Delimited Format

Jun 3, 2014

I am looking for VBA code that will save Excel 2010 files in semi-colon delimited format without having to chage the universal language options.

View 1 Replies View Related

Save Ranges In Multiple Files To Text Files

Dec 21, 2007

I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.

Example:
12/20/2007
Your Name
123 Somewhere St.
Here, OH 45111
Home Visit

12/20/2007
Someone Else
345 Anywhere St.
There, OH 45211
Hospital Visit

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

Want To Be Able To Open Csv Files And Save As Xls Files

Apr 21, 2009

I have the code below. It openes all csv files within a folder, then delete rows depending on the value of a cell. Now it needs to save the file as a xls file ....

View 9 Replies View Related

Save Files As XML

Jun 26, 2014

I am trying to put together an excel workbook that our customer service can fill in a spreadsheet and then convert all the data to XML and put into a folder. The issue comes in that we can have up to 12 parts included in the item field. The XML maps this as a vertical drop down as opposed to a horizontal spreadsheet.

XML won't paste with code.

I have a macro put together, that works, but saves them as text files. Is there a way to convert my macro to save as XML or is there another way to do this?

Macro:
Sub Macro1()
'
' Macro1 Macro
'

'
Dim n As Integer
Dim r As Integer
Dim Fname As String

[Code] ..........

View 6 Replies View Related

Converting Pipe Delimited Data String Into Separate Columns Using VBA Macro

Mar 6, 2014

I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.

U:U AQ AR AS AT AU AV
to
Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date

View 2 Replies View Related

Need Macro To Create Formulas And Split (Comma Delimited) Cells Into Rows

Mar 30, 2013

Example: Column A has a mixture of letters and numbers. ie AU1234 or AU5678 Always the letters will be first, but not sure if 2 or 3 letters. Need to insert space between letters and numbers.

I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) " this works if all are only 2 letters...

Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.

Step 1: Insert a column next to A, check rows down and for however many rows, make above formula (include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567" .

Step 2: Column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields. D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields
F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A).

Need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields).

E.g.: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)"
Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)"
Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"

Then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....

Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F

View 7 Replies View Related

Open All Files And Save As In New Dir

Jul 9, 2012

I have files stored at C:KLQ1. Some with .xls and some with .xlsm. Now I want to same all the files under C:KLQ2 with .xlsm extension. Then replace all the 2005 with 2006 in the saved-as file. Then close all the files in Q1 without saving and all the files in Q2 with saving. The following code is modified from somewhere but does not work.

Code:

Sub OpenCloseFile()
Dim i As Integer
Dim wbResults As Workbook
Dim ws As Worksheet

[Code]....

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

Proceed Data And Save In Different Files

Jun 13, 2007

Company Name | Model # | Comments |
ABC | 1234 | Good |
DFG | 3245 | Bad |
IUR | 8950 | Moderate |
ABC | 2435 | Bad |
IUR | 2432 | Moderate |
.
(could be >100 entries under 20 companies)

We need to group the above data by company, copy it into a new file, and send it to respective company...

Any macro can serve this purpose? Save .xls to a location named by company name will be perfect as we'll send the files by mail merge to email with attachment.

In addition, the headers need to be included in the new files too...

View 14 Replies View Related

Save Files Directly From Hyperlink

Nov 6, 2006

I have a large number of webpages represented by live hyperlinks in a worksheet. I need to save the webpages either as html or prefereably as text files. My current option is to save the worksheet as a html file, open the saved html file in Opera and manually right click and save these webpages to the download folder. This will save the webpage without opening it in a new window or tab.

I would like to write a macro that automatically moves vertically from cell to cell and saves the webpage without opening a browser and requires no user interaction.

I need to know the commands to have the macro read the hyperlink in the cell, got to the website and save it to a location on the local hard drive using a differently numbered filename (file1.txt, file2.txt etc)

There is no problem if a webbrowser needs to open and close as long it is done automatically and controlled by the excel macro.

View 9 Replies View Related

Save Files Directly From Hyperlink

Nov 6, 2006

I have a large number of webpages represented by live hyperlinks in a worksheet. I need to save the webpages either as html or prefereably as text files. My current option is to save the worksheet as a html file, open the saved html file in Opera and manually right click and save these webpages to the download folder. This will save the webpage without opening it in a new window or tab.

I would like to write a macro that automatically moves vertically from cell to cell and saves the webpage without opening a browser and requires no user interaction.
I need to know the commands to have the macro read the hyperlink in the cell, got to the website and save it to a location on the local hard drive using a differently numbered filename (file1.txt, file2.txt etc)

There is no problem if a webbrowser needs to open and close as long it is done automatically and controlled by the excel macro.

View 9 Replies View Related

Save Multiple Sheets As CSV Files

Oct 24, 2007

I will have about 20 excel spreadsheets that will need to be run through some data cleansing and validation than each one exported to a csv file (without the column headings on them), and saved as the same file name as the spreadsheet was. If there are errors in the validation process then the one that fails (row) will be copied to an error log spreadsheet. There will be multiple worksheets in the error log workbook (one for each of the spreadsheets which I hope VBA can create). What I am doing so far is creating a loop that will run on all of the spreadsheets located within a folder.

'Procucedure that will run all validation processes and error checking on extracteds spreadsheets

Sub RunCodeOnAllXLSFiles()
Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch..............................

View 3 Replies View Related

Loop Through All Files Rename And Save In Another Folder

Feb 27, 2013

I have excel files in a folder, I need a macro to loop through all files, rename them and save them in another folder. the file's name is available in a cell in each file, I am able to get the file name as text (I had to dig a little to get the text as it was between ""). Well I am stuck right now because the macro is doing what it's supposed to do for one excel file only and does not continue with the rest of the available files for a reason.

VB:
Sub RenameAllFiles()
Dim Bk As Variant
Dim n As Integer
Dim NewName As String

[Code] .....

View 8 Replies View Related

Possible To Save Areas Of Spreadsheet As Image Files Via VBA?

Sep 27, 2013

I produce spreadsheets which is conditionally formatted to be both functional and aesthetically pleasing. The tables are separated by one line, and can easily be defined by VBA, I was wondering if it is be possible to export them as image files via vba? I know it can be done for graphs, but not sure about defined sections of the worksheet?

View 1 Replies View Related

Save And Enumerate Base On Existing Files

Nov 9, 2009

I have this macro it save to specific location but if the file name exist then macro fails or wants to overwrite existing file I will like to make this macro to add a number

So It will look like this
DISCONNECTED # 11-09-09.xlsx
DISCONNECTED # 11-09-09 2.xlsx
DISCONNECTED # 11-09-09 3.xlsx
DISCONNECTED # 11-09-09 4.xlsx
DISCONNECTED # 11-09-09 5.xlsx

View 3 Replies View Related

Save Different Tab Excel To Individual Text Files?

Nov 14, 2013

I want to get a vba which will convert an excel with different tabs to individual text files. IT MUST BE PIPE DELIMITED.

So if there is an .xls file with 5 different tabs, i should get 5 text files with each text file getting name of the worksheet it was created from.

I have a similar code but somehow it is doing the conversion only for last worksheet, also it is saving the file in the same name as workbook.

Code:
Sub save_as_text()
Dim i As Long, txt As String, delim As String
delim = "|"
With ActiveSheet.UsedRange
For i = 1 To .Rows.Count
txt = txt & vbCrLf & _
Join(Evaluate("transpose(transpose(" & .Rows(i).Address & "))"), delim)
Next
End With
Open Replace(ThisWorkbook.FullName, ".xls", ".txt") For Output As #1
Print #1, Mid$(txt, 2)
Close #1
End Sub

View 2 Replies View Related

How To Create / Save Multiple Files In One Shot

Mar 26, 2014

Any way to create individual excel files for over 300 people in one shot.

I have created an excel template and have a list of 300 people. Rather than one at a time, is there a way to do it in one shot?

View 2 Replies View Related

Excel 2010 :: VBA To Save Tabs As New Files

Apr 15, 2014

I have a file containing 20+ tabs and would like to be able to save each tab as a separate Excel file (with the same name as the existing tab)

View 6 Replies View Related

Open PDF Files, Save With New Security Settings

Aug 25, 2006

I am trying to use a shell command to dig into a specified directory (and ideally all sub-directories) and open every pdf file and re-save with security settings changed (adding a password to prevent printing). I can't seem to get the shell command to run from where it is:

Option Explicit

Sub OpenPDFFilesAndSave()
'opens every pdf file in a directory
Dim FSO As Object, Fld As Object, Fil As Object
Dim MainFolderName As String, i As Integer
Dim RetVal As Variant

Set FSO = CreateObject("Scripting.FileSystemObject")
MainFolderName = "C:Test"
Set Fld = FSO.GetFolder(MainFolderName)
For Each Fil In Fld.Files
i = i + 1
RetVal = Shell("C:Program FilesAdobeAcrobat 7.0
_&AcrobatAcrobat.exe MainFolderName & " " & Fil. Name")
Next
'have to add save the file, and change the print settings but not figured that out yet
End Sub

View 3 Replies View Related

Count & Rename Files & Save Each To Array

Dec 12, 2006

I have used macro to ftped a couple of files to my desktop and save them in a folder but i have problems performing the following.

1) Count the number of files in this directory "C:Summary_Reports_from_VBA". Note that the number of files is not fixed every time.

2) Rename each files to .txt files. (intially files do not have any extension and we do not know the name of the file until it has been ftped over but all files will end with a term "sorts" eg renaming of "xxxsorts" to "xxxsorts.txt")

3) Save each renamed files in an Array such that i can "opentext" each file in an excel worksheet. So if there's multiple files, there will be multiple worksheets.

View 11 Replies View Related

Save Workbook Of Imported Text Files As .xls

Nov 20, 2007

I am using the following code to import a number of text documents into the same workbook, on seperate sheets and then to save the workbook as an excel file. the problem is that when the file tries to save (red section) I get a message telling me that Excel can not save the workbook in the requested format, I think because it is still trying to save them as text files rather than as an excel workbook. When I try to save manually, selecting .xls as the format it works fine, but I would really like this to be part of the macro to ensure the WB is saved.

Sub ImportFiles()
Dim Sfile As String
Dim count As Integer
Dim ans As String
Dim A As Integer
Dim FileSaveName As String
count = 0
Application.DisplayAlerts = False
Do
' Allow user to select the file to open, text files only
Sfile = Application. GetOpenFilename("Text Files(*.txt), *.txt")
' Check that a file has been selected
If Sfile <> "False" Then......................

View 4 Replies View Related

Macro To Check If Saved Then Save Else Bring Up Save As Dialog

Sep 6, 2012

I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .

The macro itself is saved in personal.xlsx.

View 1 Replies View Related

Save As Macro (macro That Opens The Save As Window)

Oct 22, 2008

I'm trying to have a macro that opens the save as window, places the name in cell f5 and then allows you to save the workbook with cell f5 as the filename. I have managed to get the save as window to come up and the cell f5 as the name, but when I press save is doesn't. Here is what I have (also, is there a way to direct this to a specific folder).

View 4 Replies View Related

ADVERTISEMENT