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.
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.
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
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.
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.
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).
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:
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?
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.
I know how to pull an entire text file into an Excel Spreadsheet, but I only want specific information from the text file not the entire text file.
What I have is about 25 text files stored in a folder, let's say C: est.
Each file is named by a property address as follows: 209 MAIN ST.txt 213 MAIN ST.txt 111 ELM ST.txt 2356 WOOD AVE.txt
On the 11th row of each file is as follows: Property Address:209 MAIN ST On the 31st row of each file is as follows: Total Value:30500
What I would like to do is read each file located in the "C: est folder and write a record (row) into a single Excel Spreadsheet for each property. I would like the Excel Spreadsheet to look as follows once completed. Note the 1st row below is a header row that needs to be generated by the code.
Property AddressTotal Value 209 MAIN ST 30500 213 MAIN ST 60700 111 ELM ST 20400 2356 WOOD AVE 20900
Can I read a header list (in a spreadsheet, text file, or hard coded in the code) which I would prefer the spreadsheet or text file method, write the header row in A1 then B1. Next read the 25 text files and search based on the header info written above (Property Address & Total Value) and write the appropriate to the single spreadsheet. The 11th row of the First text file value written in cell A2, then read the 31st row of the First text file write the value in cell B2, then loop to the Second text file and values from The 11th row of the Second text file value written in cell A3, then read the 31st row of the Second text file write the value in cell B3, so on and so forth until the last text file is read and the last record is written.
I have written a VBA to read data from a text file (almost 5 MB = 2 lakh rows) and write into excel file. The script runs fine and transfers all data to the excel.But when i open the resulting excel file, only the first 2 and the last worksheets are visible. The worksheets in between and the data in it are missing. Second issue is tht, although i have written code to create a new worksheet when row number reaches 65535, the script writes only till row no. 32768.
I'm using the following code to read a text file that I downloaded from a mainframe file.
Do While Not EOF(FileNum) Line Input #FileNum, myLine Debug.Print myLine Loop
It reads and prints the first line, but then drops out of the loop. According to the help file, "Line Input" is looking for a carriage return (Chr(13)) or carriage return–linefeed (Chr(13) + Chr(10)) sequence. I have pasted a sample of the text file below. I'm not sure what the characters are at the beginning of each line, but perhaps I could find a way to replace each of them with a carriage return.
What if you have text that are hyperlinked to a txt file and you want to read from it and copy it into excel. What can I do then. Here is what I have been working on so far:
Sub GoToHyperLink() Dim cell As Range Dim link As Hyperlink Dim Textline As String
I want to read data from Notepad into Excel. I found this code on the net and have been trying to modify it for my needs:
Sub ImportText(FileName As String) Dim X As Long Dim FileNum As Long Dim TotalFile As String Dim Lines() As String Const DataRowStart As Long = 1 Const DataColStart As Long = 1 FileNum = FreeFile Open "C:...data.txt" For Binary As #FileNum.................
This pastes the contents of each line into 1 cell but I want to paste each value into a seperate cell. So in the attached data.txt there are 5 records each of 2 lines. For example in the 1st record I want to paste 05-693-1900 into 1 cell then 0040 in the cell to the right of that, Town A into another cell, 000000 into another cell....and so on. For the 2nd line it should be 000000000033 into one cell, AA28816 into the adjacent cell...and each remaining number into a seperate cell. I also want to leave 3 blank lines before going to the next record.data.txt
I have a comma delineated text file that is full of stock item details such as stock code, description, 3 different prices etc it is about 15 fields wide by about 400 rows down. I need to be able to import all rows but only certain columns into an excel worksheet using vba. I know I can easily just rename it to .xls or .csv or something like that but its a text file we export out of our system at work and we need to be able to give it to customers who then can import into this excel spreadsheet/calculator I am creating.
The file looks something like this:
"String 1", ""String 2", "String 3", "String 4", ...... all the way up to 15
There is 400 or so rows (which represent stock items)
Now I need to copy all rows in column 1 (string 1) all rows in column 12, 13, 14, 15 (This is the only data I need from the text file in this particular spreadsheet/calculator)
I have it working fine reading the entire row in one at a time splitting it up into a string array using the Split() function and then only copies the certain array indexes i need into the worksheet here is the code for this: ...
I have a Text-File with characters coded in simple chinese (gb2312) rsp. codepage 20936 and I want to read this file, paste the content in my worksheet. Later (after a chinese edited the text) I've to write this back to a text file.
The only way I found to make this, is by copy/paste e.g. from WordPad, but I want to automate it with VBA.
I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.
Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:
- Renaming - Converting to a different file format (didn't work, it won't let me) - Opening in notepad...etc doesn't work.
This file is dated back in 2004...do you think it's corrupt? Is there anything i can do to open or recover this?
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??
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.
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 ...
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?
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.
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?
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 (*.*), *.*")
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.
