Saving Sheets As Text Files Results In Blank Lines

May 11, 2008

I load an text file into an excel sheet and after some eventually changes I save (overwrite) it back into the text file again. Now the text file has become a file with 50 pages (49 empty pages). How can I avoid this or how can I set an EOF when no more written lines follows. The code for saving the excel sheet is this:

Application. ScreenUpdating = False
Application.DisplayAlerts = False
Sheets(wsImport).Select
Sheets(wsImport).copy
ActiveWorkbook.SaveAs FileName:=WorkFolder & WorkFile, _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
'ThisWorkbook.Activate
Sheets(wsKwartaal).Activate

View 6 Replies


ADVERTISEMENT

Reading Lines From Text Files...

May 23, 2006

I have checked the forum about reading from text files, but I wasn't able to find and similar thread. Here is the situation: I have one text file, but first 204 characters are the file header. 204. character is an asterisk. Then, the records begin. Following is the beginning of the file:

$$GNCSBU/PML/0002 TCPX 11006 IST Bayplan 23.05.06 14:35:57 TRIST 11006 *
And it continues:.....................

View 2 Replies View Related

Importing Selective Lines From Multiple Text Files

Jun 18, 2009

I have many text files basically with the same data from different dates. I am trying to select specific lines from each text, one that includes ".txt" which is the date, ex: "2008_308" and one that includes the word "PIXEL" and the following 5 lines after that.

I found a very similar post to mine and someone provided the script using VB for the question. It works, but it was only for one text file. I would like to use it for multiple text files. The files are all dated, i.e. "2008_308.txt". Although some of the dates are missing.

This is the script, from another posting, for importing selective lines from one text file:

Sub test()
Dim fn As String, txt As String, delim As String, a() As String
Dim i As Long, ii As Long, iii As Long, n As Long, x, y
fn = "c: est.txt" ' İChange here (File Path)
delim = vbTab ' İChange here (delimiter)
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Split(temp, vbCrLf)
ReDim a(1 To UBound(x) + 1, 1 To 100)
For i = 0 To UBound(x)
If InStr(1, x(i), "txt", 1) > 0 Then
n = n + 1 : y = Split(x(i), delim)
For ii = 0 To UBound(y)
a(n, ii + 1) = y(ii)..........................

View 9 Replies View Related

Import Multiple Text Files - Delete Rows Between Lines With File Name

Apr 10, 2014

I have around 50 text files with similar design per attached file. I need to import the text files with criteria below:-

1. include file name
2. let user choose the folder
3. exclude data from "work in process summary" to "work in process cost totals"
4. only have one title in the excel files which all text files is combined "Item, Line ....."
5. If the text files do not have title like "Item, Line, ..." do not import

Is it possible to have all criteria listed above by running a macro?

A.txt

View 1 Replies View Related

Saving Portions Of Selected Cell Data As New Text Files

Jun 21, 2009

I have a worksheet in my workbook that contains data for 25 different airfoils, each one in a cell space of 3 columns and 50 rows.
In order to do some analysis on these airfoils, I need to save each 3x50 as separate text file to define input for analysis code.
How can I do this in VB in one macro?

View 9 Replies View Related

Text File Import Produces Unwanted Blank Lines Between Rows Of Data

Jan 19, 2010

After doing some searching I found a macro here which imports multiple text files into one workbook, keeping each text file as a seperate worksheet, each worksheet named the same as the original file name (minus the .txt extension).

However, it adds a blank line between each line of text. So I get this:


Pinging 172.27.133.29 with 32 bytes of data:

Reply from 172.27.133.29: bytes=32 time=232ms TTL=62

Reply from 172.27.133.29: bytes=32 time=62ms TTL=62

Reply from 172.27.133.29: bytes=32 time=65ms TTL=62

instead of this:........

Pinging 172.27.133.29 with 32 bytes of data:

Reply from 172.27.133.29: bytes=32 time=232ms TTL=62
Reply from 172.27.133.29: bytes=32 time=62ms TTL=62
Reply from 172.27.133.29: bytes=32 time=65ms TTL=62

This doesn't happen when I use the import wizard, or when I just open the file from the File -> Open menu.

My text files are always the same format (number of lines, etc.), so it's easy to delimit and insert forumlae as needed, but those extra lines are breaking a macro I've previously written to parse the data.

View 9 Replies View Related

Saving Comma Separated .txt Files As .csv Files

Sep 15, 2009

I have a large number of .txt files that are comma separated files (but not saved with the .csv extension) that I need to convert to files with the .csv extension.

Is there an easy way to do this for all files in a given folder? Ideally, the files don't even need to be opened as they are are quite large in size (70MB+) and there are a lot of them (500+).

View 9 Replies View Related

Import Text Files Into Multiple Sheets

Jan 4, 2008

Trying to import mutliple text files from one folder and save them into multiple tabs in the workbook. Found this piece of code on forums but can't respond to thread since its expired. Whenever i choose a file from the folder, it would say no files exist. Is there something wrong or am i missing libaries to run this?

Sub Test()
'First off, this will prompt where the text files are saved
filepath = Application. GetOpenFilename("Text Files (*.txt), *.txt", , "Where are your text files saved")

'this will strip the filename from your selection, leaving just the folder
Do While Right(filepath, 1) <> ""
filepath = Left(filepath, Len(filepath) - 1)
Loop

'This will search for all of the files within the folder
Set fs = Application.FileSearch
With fs...........................

View 3 Replies View Related

Importing Text Files - Large Number Of Rows Blank After Each Import

Apr 5, 2012

I have a log that I regularly use to import text files, after each text file import it leaves a large number of rows blank that I have to delete before I add in the next text file import.

Any VBA method to detect this and remove the blank rows so that I dont have to keep checking to remove?

View 3 Replies View Related

Results Show Results Of Formula, But Should Be Blank

Jul 27, 2006

I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.

View 2 Replies View Related

Saving Workbook On Shared Drive Results In Second Copy

Dec 4, 2007

When saving excel workbooks in our shared drive, a second copy of the workbook (with an unusual name) is being saved in addition to the original copy. Why would this happen?

View 7 Replies View Related

Converting Text To Numbers In Multiple Sheets While Leaving Missing Values Blank?

Jul 24, 2013

I have an Excel workbook with around 100 worksheets. All of them have similar columns, but the values are stored as text. The length of columns and missingness is different in different worksheets. I will be grateful if any of you could suggest a VBA that could convert the numbers stored as text back to numbers. I have been looking for an answer for a while, but cant seem to find one that automatically looks at every worksheet and converts text into number.

View 1 Replies View Related

Import Two Lines From HTML Files

Jan 20, 2012

So I have a few thousand .html-files that i need two lines from. It is line 43 and line 46. I want those lines in each cell and the dokument name in the last cell. After that it goes 1 row down and takes the next html-file.

So it would be something like:

Row 1 | line43 | line46 | Dok1
Row 2 | line43 | line46 | Dok2

View 3 Replies View Related

VBA Saving PDF Files Into Folders

Sep 29, 2013

I have this code that generates excel pages as pdf files, but I can't get it to save it in its individual folders. For example, I have the sheet name as Brian Lin, I want to save the pdf into the folder called Brian Lin, then if I have the sheet name as Lilly Tran, the pdf should save into the folder called Lilly Tran. Here's my code. The code is one step short of what I want it to do, it saves in the folder right before the name folders.

Code:

Sub Macro1()
Dim wsh As Worksheet, vWshs
Dim sFolderName As String, set_Path As String, name As String, hwas As String, sname As String, swsname As String
vWshs = Array("Rates", "Inputs")
set_Path = "C:UsersJonathanDesktopFaFundInvoices" & hwas & ""

[Code]...

View 4 Replies View Related

Saving Causes Backup Files To Form?

Apr 19, 2013

Whenever I make a change to any of my spreadsheets and click save, a backup is created in the same folder. A sample document is attached. Download it, make a change, then save it.

View 3 Replies View Related

Excel 2010 :: Saving To TXT Files?

Nov 23, 2011

I'm saving information from excel 2010 in to a text file (txt), when doing this and opening up the txt file the below line is saving it with " at the start and at the end of the line.

:32A:110809GBP4,00

Is there any way when saving as a text file without the " pulling through at the start and at the end of the line?

View 2 Replies View Related

Saving Batch Files From Excel

Nov 22, 2013

I currently have 3 batch files that I would like to be able to update using VBA instead of manually adding the information to each one. My goal is to be able to use my excel file and when I add new lines I could then run the script to save and/or update each of the batch files. I would like to be able to add the new lines I added in excel to the end of the batch files. I use the batch files for automation purposes but I do not like the fact that I have to open each one and added the same lines three times. This is why I want to be able to update my excel file and then add those new lines to the batch file. An example of how to accomplish this task using VBA would be awesome.

View 2 Replies View Related

Copying Tabs And Saving Them As Files

Jun 13, 2007

I've already done a search and found this on ozgrid.com:

RunCodeOnAllXLSFiles

I'm sure it is what I need, but as a non-programmer, I'm having trouble using it.

I have 12 files, each with 14 tabs. Example:

File: Sector.xls
Tab 1: Region 1
Tab 2: Region 2
Tab 3: Region 3

The client now wants 14 files, each with 12 tabs:

File: Region 1.xls
Tab 1: Sector 1
Tab 2: Sector 2
Tab 3: Sector 3

My first attempt was to try a macro:

1) left click on tab
2) move or copy...
3) move selected sheets to book (File: Region 1.xls)
4) then go on to next tab...............

Now, this does loop through the requested files, I can see them open, then close. I'm guessing they are copying, but I take it that I need to have it paste, then go to the next? (The c:files is just generic, as the files are on our LAN, and I'm just paranoid. The "Erie St. Clair" are the actual names of the file, and the tabs).

View 2 Replies View Related

Keep Sheet Names Unchanged After Saving Them As TXT Files

Mar 24, 2014

I want two sheets of my Excel workbook to be saved as txt file. The name of the file is compiled of a fixed part ("TrialList" or "BlockList") and two values from another sheet (subject and session number):

[Code] ........

Now, saving the text files with the desired file names works perfectly. However, Excel decides to change the names of the sheets that have been saved as text files accordingly. This is something I want to prevent from happening, since a second loop of actions will end because of the unexpected sheet names...

View 1 Replies View Related

Saving Attached Files From Outlook To Folder

Mar 3, 2013

I'm programing a VBA macro in Outlook 2007. I have added a button to the quick bar so when i open an email and push that button, a Excel file opens and i can input some data. Thing is, i need that if the opened email have some kind of attachment, when i push the button i want this attachement to be saved into a specific folder. Is that posssible?

View 2 Replies View Related

Difference In File Size When Saving Excel Files?

Apr 11, 2013

I tried to use prtScrn button on the keyboard to capture the screen (excel UI) and then pasted it to excel then saved it. I tried to do the same thing on my friends machine and what surprises me is that the file size of two excel is different.

We use the same OS, excel version and the same machine specs.My saved excel file is around 7.91 mb in size while his is just around 235kb. Why is this like this?

Both pasted image have bmp as a format (default for excel when a you use print screen and directly paste it on excel.).

View 3 Replies View Related

Saving Macros As Global Addins Or Personal.xls Files

Jan 12, 2010

So i have a macro that i wrote that willclean up these datafeeds that i get. i want to be able to use them for each spreadsheet. So the first bit i tried was saving a personal.xls file in the xlstart folder in XP (MSE 2003) . That would automatically pull up but when i tried to run the macro on other worksheets i get the generic 400 error.

the next thing i tried was copying the macro to a module and adding function tags and taking out the sub tags. i then "saved" that in the addins folder then tried assiging my custom button to is through the macros prompt. When i gave the title box the absolute path to the addins folder with clean.xla cited it said it was invalid, so i tried saving it just as clean.xla expecting it to locate the file there anyway.

this far each time i try to run the macro globally it says it cant find the current sheetname!macroname So i must be missing a step somewhere... can anyone give advice, i have searched the forum and couldnt find a similar problem for solutions.

View 6 Replies View Related

User To Select Variable Folder For Saving Files

Jun 11, 2009

I would like my macro to prompt the user to select a folder for files to be either opened from or saved to during the running of my macro.

In fact, I need them to select two folders, one for this month and the other for last month.

I think I need to set the two folders as a variable but being fairly new to vba I'm not quite sure how to do this.

View 9 Replies View Related

Removing Blank Lines From String

Feb 17, 2010

I have a texfile that populates a textbox on a userform. I would like to remove all blank lines in the string including those at the end if they exist, before populating the textbox. I'm reading the entire file at once into the string, not line by line.

Is there any way to edit the string called Text to remove the blank lines before populating the textbox? I'm looking for 2 carriage return characters in a row, and if so then remove one of them, but I don't know how to code that. This is in the userform activate section. If I read the textfile line by line, I don't know how to populate the textbox that way and remove the blank lines.

View 3 Replies View Related

How To Remove Blank Lines Within A Cell

Nov 17, 2011

In Sheet1 cell A1 has concatenate formula and the data source is linked from sheet "DataInput". Sheet1 cell A1 has multiple data and there are blank lines within the cell. I would like to remove these lines so it looks clean without any gaps. So basically its looking like this right now

Harry

Andrew
Joe
Apple

Billy

I want it to look like this but i am having a difficult time because this cell contains a formula

Harry
Andrew
Joe
Apple
Billy

A formula or something that would fix this problem automatically.

View 1 Replies View Related

Blank Lines In Listbox With RowSource

Apr 3, 2008

I have three listboxes and I set each of their row sources from code in UserForm_Initialize() event like so:

With Sheet1
lbxLanguages.RowSource = . Range("C2", .Range("C2").End(xlDown)).Address
End With

When I run the form, sometimes I see data in two of the three listboxes and the third has blank/empty rows; sometimes all three listboxes have only empty rows in them (i.e. correct number of checkbox squares displayed but no data in front of the squares).

View 6 Replies View Related

Excel 2003 :: Saving Files Generated By External Application

Jul 23, 2013

I have the following code which edits an excel file and saves it in a different format, I think want the code to take this newly saved file and open it in an external application (Softplot) and then save it through this new application.

VB:
Sub FormatMacro1a(ws As Worksheet)
ws.Copy
Rows(1).Delete
Columns("D:E").Cut Destination:=Columns("J:K")
Columns("F:K").Cut Destination:=Columns("D:I")
Range("E1:E201").Value = "0"

[Code] .....

As it stands I have the file saving in a new format and I can open a specific file in softplot through VBA however where I am getting stuck is opening my newly saved file and then saving it.

I have tried the following :

VB:
Path = "C:Program FilesSoftPlot-8softplot.exe"
File = "ActiveWorkbook"

View 3 Replies View Related

Macro To Insert Blank Lines And Formula?

Jun 11, 2014

I have a worksheet that contains data arranged in fields from columns A to J. The relevant columns for the purposes of the macro are columns B (customer codes) and G (sales values). Column B may contain a single instance of a customer code, or multiple (over 50), depending on how active a customer has been. The worksheet always contains many different customer codes with varying numbers of rows for each customer, sorted by customer. What I'm trying to do is write a macro that will loop through the worksheet and insert a blank line immediately after a change in value of column B (customer code), and in this blank line, insert the customer code in column B (which comes from the cell immediately above), a sum formula for all values within a range that relate to that particular customer in column G, and an IF statement in column J that relates to the SUM formula. The loop concludes when there are no longer values in column B.

I have managed to insert blank lines on change in values in column B, but am not sure about the best approach for inserting the SUM formula or the IF statement in columns G and J respectively.

View 9 Replies View Related

New Lines To Refer To Different Sheets

Nov 20, 2009

=COUNTIF(A2&"_RES","Yes")

A2 refers to a linked cell but I have tried without the link there still no joy. Together they would generate for example "DCA_RES" which would refer to a column range in a different sheet on the same workbook.

I want this to work so that I can implement more easily new lines to refer to different sheets. (Drag to copy) instead of having to copy and edit a formula 100 times.

View 6 Replies View Related

Inserting Rows - Need To Alter For The Blank Lines And Totals

Aug 13, 2009

I need to alter this code in order to do 2 things

1. to leave a blank line inbwteen Shop A's Apples, Shop A's Pears etc

2. I also wanted to total up the price totals....(see example below).So if the last price was in E4, the total of all those prices above would show in F5 ....

View 9 Replies View Related







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