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


ADVERTISEMENT

Imported Text Files Numbers Seen As Text

Mar 10, 2008

I have opened a .txt (by right clicking and selecting Open with rather than open from within Excel) file with numbers using the format 1,234,567.00 but in my country we use 1 234 567,00. So I created a macro changing the , into "" and the . into , and it works fine while creating the macro itself (by using the macro recorder) and the numbers shows and behaves like numbers. However, when running the macro on a new file some numbers remain as text whereas others are numbers. Theres is no common string, eg that numbers starting with - (minus) remains text.

It does then not matter if I go to the format function and select number with decimals and thousand dividor - the number still remains as text. I have also tried the suggested multply by 1 but it still remains as text. The only thing that helps is to either double click on the number or use the Konvert text to number. But I would then have to click each number in the whole table which is what I wanted to avoid.

View 7 Replies View Related

VBA Code To Import Text Files & Process Imported Data

Jul 26, 2009

I have two macros one of which imports the data and the other processes the data. These macros were created at different times and need to be joined into a single macro that will combine their operations – with slight modification to the importing macro. Let me describe what each of the macros does:

PROCESS macro:
Cycles through the CONTROL CELL on the “1” tab using the VALUES TO TEST. For each value to test it copies the values form the output tabs and pastes them into the A+B tab. Then it removes the duplicate rows there.

Option Explicit
'======================================
Sub PROCESS() 'This subroutine is called when clicking on the first button
Dim i As Long, j As Long, k As Long 'Those are the variables that will browse through the whole sheets
Dim l As Integer 'this will be used to browse through the values to test
Dim TheSearch(1 To 2) As Object 'This is an array of objects that will be used for the search
Dim TheRange As Range 'This will be used to flag the range of duplicates in the output

'Searching for the Text string 'VALUES To TEST' in the sheet "1" in order to know where the array of values to be tested start
Set TheSearch(1) = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count + 10, ActiveSheet.UsedRange.Columns.Count)).Find(What:="VALUES TO TEST", LookIn:=xlValues, Lookat:=xlWhole)

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

Write Ranges To Text Files & Save Each As Cell Text

Sep 17, 2007

I m trying to write a macro which could take the text from a single column row T2 to row T313 and write it to a .txt file. Have the .txt file name created by the text in T4 or I could also put the text to name the file in T1 if you think it would be easier.

Then carry on to the next named sheet and produce another .txt file in exactly the same way until all 15 sheets have been completed. It would also be helpful if prior to starting to write each text files, it could test for any text in cell A2 of the sheet. The first empty A2 cell of a sheet would determine the end of the run, if it was prior to sheet 15 being reached.

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

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

Save Multiple Files In Folder As Text Format

Mar 27, 2008

I have 100 excel files in a folder need all these files to be saved in txt format in another folder need vba code to do this

View 2 Replies View Related

Reformat Directory Of Text Files And Save As Excel Simultaneously?

Mar 3, 2014

I have several hundred text files which I would like to reformat and save as excel without needing to open each file individually.

I'm entirely new to working with macros, so, I was able to use macro recorder to save the steps to reformat the file, but would like setting up the part of the macro to automatically open and reformat all of the files.

View 2 Replies View Related

Create Multiple Text Files From Cells & Save As Name In Adjacent Cell

Jun 26, 2008

I have two columns one of which has filenames and the other having values. I need to creating a macro that takes each value from a cell outputs it into a text file and then saves it with the filename of the cell next to the value. I'll need it to go down the columns and create separate text files for each.

View 7 Replies View Related

Append Text Files To Workbook

Oct 26, 2006

I want to open multiple .csv files from a single directory and append them to one workbook. The following code partly works, but appends only the first line from each file.

Sub GetFiles()
Dim w As Worksheet, fn As String, k As Long
Application. ScreenUpdating = False
Set w = ActiveSheet
k = Cells(65536, 1).End(xlUp).Row
If Not IsEmpty(Cells(k, 1)) Then k = k + 1
fn = Dir("*.csv")
While fn <> ""
Workbooks.OpenText Filename:=fn, Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False
Rows(1).Copy w.Rows(k)
k = k + 1
ActiveWorkbook.Close False
fn = Dir
Wend
Application.CutCopyMode = False
End Sub

View 3 Replies View Related

Save Workbook As Cell Text

Jan 15, 2009

Have read a few codes that allow you to automatically save a workbook using the value of a cell as the name of the file.

BUT, I can not find the code to save it if the cell is a date.

The 17/01/2009 wont save due to the forward slash.

Is there anyway within code that I can save the workbook by first removing the slashes?

View 9 Replies View Related

Save Each Worksheet In Workbook As Text

Nov 21, 2007

I have a file consisting of many sheets. I need a macro that saves the file as a normal excel file, that is just a normal save as function. This I have. But then I also want the macro to save one of the sheets as a .txt file. This is what I got from reading this outstanding forum. But this saves all my sheets as seperate .txt files

Sub wsToText()
Dim ws As Worksheet
Application. ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
Sheets(ws. Name).Select
Sheets(ws.Name).Copy
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and Settings1kitvelDesktop" & ws.Name & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate
Next
End Sub

View 2 Replies View Related

Import Multiple Text Files Into Workbook

Jan 23, 2010

I found the code below on the internet and adjusted it to my needs.It is working perfectly fine except for the fact that it opens a new workbook to import the data. I run this code from a workbook named "InstronImport.xlsm" and would like it to add the data in this workbook instead of a new one. I have been trying for a day now and can't seem to figure out how to do it.

Sub ImportRawFilesInstron()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String
On Error Goto ErrHandler
Application. ScreenUpdating = False
sDelimiter = ","
FilesToOpen = Application. GetOpenFilename _
(FileFilter:="Text Files (*.raw), *.raw", _
MultiSelect:=True, Title:="Text Files to Open")...................

View 4 Replies View Related

Importing Multiple Text Files Into One Workbook

Apr 26, 2007

I am using excel 2003. I have more than 500 text file which are result of some numerical analysis. I have another bunches of the same file number. I can record and play around with macro.

What I want to do is
-copy selected cells from imported text file in excel and paste in new or in first opened excel file. I have recorded macro for one file including importing from text to excell, copy and paste the selected file (look the macro below). But I have more than 500 files and I want to do the macro the same thing for each file in one excell file. Do i have to make one macro for each file??, that takes much more time than manual import and copy paste. The cell position and range to copy is the same, but have to be pasted in new row (in one excel summery file). The file name of each text file is different and all are in the same folder.

Sub text_to_excel()

Workbooks.OpenText Filename:= _
"I:ResearchVALERI_germinationvaleri_slopevaleri_slope_COREL_DHPoutputDSCN2589.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _ ..................

View 9 Replies View Related

Specific Cells Imported To New WorkBook

Oct 13, 2009

I work with a machine that takes readings and exports them into an unorganized Excel file along with irrelevant data. What I need to do is create a macro that will import only specific data and place it where I want it.

I only need to go from File A to File B, so no complex data compilation from multiple workbooks [at this time!] What may be an issue, however, is that there is no set number of data entries. It could be anywhere from 1 to 500 depending on the sample. What I'm hoping is that since the samples are added in a set pattern that it will possible to accomplish this. I've attached an excel file with two sheets. Sheet one is to represent the data file that is extracted from the machine and sheet two is a mockup template demonstrating how I want the data organized in the new file after import

View 4 Replies View Related

Copy Text From Word Doc - Write And Save Workbook

Jan 12, 2013

I'm using a code to copy text from a word doc into a spreadsheet. It does it all right, but when it comes to saving, it does not save the open worksheet and prompts me to save as a copy. If I do so, nothing is changed in the original file and the copy file has the information copied from the word file.

This is the code I'm using to write and save the spreadsheet.

...
(VBA in WORD)

Set wdApp = Excel.Application

Workbooks.Open FileName:="C:UsersljsDocumentsword2excel.xls"

Workbooks("word2excel.xls").Activate

Worksheets("Plan1").Activate

Worksheets("Plan1").Range("A2").Value = DOCname
Worksheets("Plan1").Range("B2").Value = CarNo
Worksheets("Plan1").Range("C2").Value = WordNo

ActiveWorkbook.Save
ActiveWorkbook.Close
...

View 1 Replies View Related

VBA Open All Text Files In Excel In Folder And Save Them As Excel File

Dec 7, 2013

I have some daily text files in a folder (so about 30 of them each month), which in the end of month, I need to open them up in excel, format them so that I can use the information for my analysis.

I would like to create a macro, to quickly open them all up at once and save them each individually in .xls or .xlsm format.

I am new to VBA and after some research online, I was able to have the files open with the following code. but now I don't know how to proceed further to save them one by one with the same name but in .xls or .xlsm format.

Sub Opentxtfiles()
Dim MyFolder As String
Dim myfile As String

[Code].....

View 2 Replies View Related

Add ComboBox Text To Imported Text Sheet

Jan 29, 2014

I have a code that import a txt file to an existing sheet, and works perfectly. I also have a ComboBox named "txtstorey" where users select the level they are working on, ie Ground Floor, Forst Floor etc.

I need to add in a VBA code that add the level that is selected in the cells on the same spreadsheet

For example, the current code import the data like this (It places the data at "$C$1"

Living Room20
Kitchen14
Bedroom 112
Bedroom 210
Bedroom 311
Bathrm4
En Suite5
Hallway3
Garage18

This is what I want:

Ground FloorLiving Room20
Ground FloorKitchen14
Ground FloorBedroom 112
Ground FloorBedroom 210
Ground FloorBedroom 311
Ground FloorBathrm4
Ground FloorEn Suite5
Ground FloorHallway3
Ground FloorGarage18

(Where "Ground Floor" is from the txtstorey ComboBox)

Here is my code as is:

[Code] .....

View 4 Replies View Related

Imported Dates Being Seen As Text

Jan 17, 2008

The problem i'm having is that these data downloads come off the server like this:

01/14/08

so i'm getting the green triangle error "text date with 2 digit year"

with the option to covert it to 19xx or 20xx.

I have to manually select this text and select 20xx before it is recognized by excel for what it is. only after this is the conditional format rule processed, and my 5 day old dates turn their appropriate colors.

When i set up a macro record while i do this date conversion, it does not record any command for this conversion.

Is there any code i can enter that will automatically do this conversion process for me without having to do it by hand?

View 5 Replies View Related

How To Fix Numbers When Imported Into Excel As Text

Apr 10, 2009

I immediately began having problems with formulas and pivot tables when I began importing data from an offsite postgreSQL database. For example, "countif" and "sumproduct" formulas invariably return values of 0. I can create pivot tables from the imported data, but nothing with a numerical value will group.

After fighting with this for literally hours, I finally noticed that all numbers in the columns were on the left side of the column meaning that the numerical data is being imported into Excel as text, even though the columns in the spreadsheet into which the data is imported are in number format.

Any way either to import numbers as numbers from the postgreSQL database, or to convert text to numbers after the data is imported.

View 5 Replies View Related

Adding Text Over Imported Image

Jan 8, 2013

I have imported a jpeg image into Excel but can not figure out how to move it to the "back" so I can add text on top of it. When I make it a "background", it makes duplicate backgrounds when I only want one. Is this the only way and if so, how do I set it so that I only have one image in the workbook?

View 1 Replies View Related

Removing Text From Imported Cells?

Oct 18, 2011

I have imported data and it shows "Check 1234". How can I use a formula to remove "check" from this cell?

View 3 Replies View Related

Force Imported Text Into Formula

Mar 15, 2009

I have a text file I am importing and using text-to-colums.

Some of the text is "+TOL" "-TOL" and when excel converts to columns it is forcing the text to a formula "=+Tol" "=-Tol" giving me #NAME? in those cells.

I can format the cells as text, which is fine for now since I'm using VBA, but if I need to use any formulas then I'm SOL...

View 9 Replies View Related

Reformating Date, Which Is Imported As Text

Jun 28, 2006

I am importing some data from an in house system. This data includes a date, which is imported as text. I have tried to reformat it using the date format options, but this does not work. The date is required as part of a lookup, and whilst it is formatted as text, the lookup does not work.

View 3 Replies View Related

HTML Text Imported As Dates

Mar 27, 2008

I have 85 Html files that I open in excel. The files have a bunch of columns with numbers. Excel handles most of them properly, but if the number looks like a date, it is imported as a date (which it shouldn't be). For example if the number is 13-1, excel handles it fine, but if the number is 12-1, excel thinks it is a date and imports Dec-01. How do I get excel to import it as 12-1?

View 6 Replies View Related

Convert Text Imported From CSV File To Number

Mar 25, 2013

I am trying to convert a text imported from CSV file to a number so that I can further elaborate it.

I tried value, trim, clean but without sucess.

Cell values is -10 000,00 what I want to display is 10000.

How can i Do that.

I am attaching a demo file for illustration : Webistexpences.xlsxi

View 6 Replies View Related

Convert Numbers Into Text When Imported And Autofill

Jul 18, 2013

Following is the master data that's imported from weekly timesheet.

DeptProjNoProjNameEmployee
Dept170000Project1Shawn Johnson
Dept170000Project1Brenda Larry
Dept260010Project2Patrick Leh
Dept170010Project3Shawn Johnson

When imported, the 'ProjNo' is stored in Text format. Now when forecast data is appended to master table, the ProjNo stores as Numbers. I would like to know how I can automatically convert it into Text. The number of records varies every time when imported from the Timesheet.

The other thing is I need AutoFill 'APPROVED' in Column 5 from 2nd row to the last row.

View 5 Replies View Related

Date Formatting In VBA :: Dates That Are Imported As Text

Sep 12, 2007

to change the format of some dates that are imported as text.

In a previous request ...

[url]

I was shown a way to convert dates correctly.

This solution works perfectly when carried out manually, but doesn't work if a record a macro and then replay it - only some of the dates are converted.

Here is a small sample of the dates I'm working with...

04/09/2008
05/10/2007
08/10/2007
14/09/2007
15/09/2007
17/09/2007
17/09/2007
17/09/2007
19/03/2008
20/09/2007
21/09/2007
21/09/2007
25/09/2008
27/09/2007
28/09/2007
28/09/2007

And here's the recorded macro, deliberately left exactly as is

View 14 Replies View Related







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