When I export my files onto text and open it with Excel, it will automatically format the cells to take out leading zeros, convert to scientific notation, and convert certain numbers into date/month/year format. How can I get Excel to open my file without reformatting the numbers in my text file?
View 4 RepliesI have a spreadsheet that is produced by a macro. This spreadsheet has a table of 30 rows and 24 columns pasted into it from another spreadsheet. The first 6 rows of vary between containing 1 and 6 columns of data. The next 17 rows always have 24 columns of data. The last row has 1 column of data. The macro saves this spreadsheet as a .csv file. The macro works great, but when I open the .csv file in notepad all the rows have 23 commas, even for the rows with only a few columns of data. Here is a shorten example of the file when opened in Notepad....
<GTS-IPL-Customer>,,,,,,,,,,,,,,,,,,,,,,,,
H1,October,2007,,,Normal,,,,,,,,,,,,,,,,,,,
H2,000426,,,,,,,,,,,,,,,,,,,,,,,
H3,2.72283E+12,,,,,,,,,,,,,,,,,,,,,,,
H4,88100031,,,,,,,,,,,,,,,,,,,,,,,
H5,NBPL,,,,,,,,,,,,,,,,,,,,,,,
D,10012007,0,312,2,310,304,1006,306,306,4,4,31,4,0,0,0,,,,,,,5.805,-23.22
D,10022007,0,334,2,332,330,1009,333,639,-1,3,33,1,0,0,0,,,,,,,6.165,6.165
D,10032007,0,316,2,314,302,1045,316,955,-2,1,31,2,0,0,0,,,,,,,6.675,13.35
Is there anyway to have the macro not insert the commas in the first 6 rows for columns on the right side of the last column of data? I need the data to be formatted in the format shown below.
<GTS-IPL-Customer>
H1,October,2007,,,Normal
H2,000426
H3,2.72283E.....................
I need to format an Excel spreadsheet into a comma delimited text file. I have saved the file as a text file, I have suceeded at making it comma delimited. In the text import wizard I have also selected no text qualifier.
However when the file is imported everything works fine but some quotation marks pop up at the beginning and at the end. The quotations are not part of the file and I have already chosen no text qualifier. How do I get rid of the quotation marks so that they do not appear when the file is imported?
Attached is a sample of the file.
I have a program in unix that ouputs a text file in .csv form which allows me to import my data into an excel spreadsheet very easily. I am wondering if there is anyway I can add formatting, like to tell excel to make a cell green or something like that.
If it can't be done with .csv, is there any other tool or format available that will accomplish this?
When opening a CSV file in Excel, it seems Excel makes an "intelligent" attempt at interpreting the data into a data type.
This causes a text field containing 7300070E-4 to be interpreted as 7.30E+0
I have a spreadsheet with many rows of data. I want to be able to see the last month or so of data (each day is one row), but I do not want to have to scroll to the bottom of the worksheet to see the recent data, so I want to reverse the data - I want Row 1 to be my column headers, and for every new day I want to insert a new row at Row 2 and add my data. However, when I do that, my conditional formatting gets all messed up. Once the new row is added, the formatting rule changes its range to only those rows which had data in them before I did the insert, each new row gets a separate but identical rule that applies only to itself, and the headers row gets its own rule.
That seems incredibly stupid to me. Is there a way I prevent the formatting rules from changing and just stay the way they are which applies to the entire column?
I am trying to design a spreadsheet for planning a project schedule. Down the left are the dates, across the top are the resource names and in the content are the activities being carried out.
Each activity I have is given a two letter prefix, e.g. TD = Training Delivery. I then have conditional formatting applied to each cell to change the cell colour based on the two letter prefix. In all there are six conditional formatting rules.
Now, my problem is that I need this sheet to be really fluid. I want to be able to drag and drop blocks of activities to a buffer area at the side and then drag them into another part of the schedule. However, when you drag and drop (effectively cut and paste) the activities around the conditional formatting moves with the cut content, whereas I want it to remain where it was. I guess what I'm after is a way of dragging and dropping values only.
The best I've come up with so far is an onChange macro that reapplies the conditional formatting to the relevant part of my spreadsheet, but I can't believe this is the most elegant solution. Is there perhaps a way of triggering a macro onPaste?
I use a query table to import data into a workbook which works fine
With wsActiveSheet.QueryTables.Add(Connection:=sQueryString, _
Destination:=wsActiveSheet. Range("A1"))
.Name = CStr("main.html?LANG=de&search=true&searchterm=" & sIdentifier & "_1")
.PreserveFormatting = True
.BackgroundQuery = False
.RefreshStyle = xlInsertEntireRows 'xlInsertDeleteCells
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebTables = "9,14"
.WebPreFormattedTextToColumns = True
.WebSingleBlockTextImport = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
One of the fields I import has a value like 100:01 or 10:01 or 01:01 or other combinations (the above ones are the most frequent ones).
Unfortunately excel always converts this field into a date and a time but this is not what I want.
Is there any way to stop excel doing this? I tried to work around it with the text property e.g. formating it to text after I imported the data and formating the entire sheet to use text before
I want to make sure that the user of a file only saves from a button on a particular sheet. Can I ensure that if the user goes File Save at any time that they cannot use it?
View 9 Replies View RelatedI have an existing spreadsheet with a column of strings (actually VIN numbers). These numbers correllate to a bunch of text files, that can exist in one of three folders (UsernameDesktop1, 2, or 3) on my desktop. What I need the macro to do is:
1) get the filename from A2 (A1 is a heading row)
2) Find the appropriate text file in one of the three folders
3) Put the folder name into I2
4) Scan the text file for some strings, and copy some data that follows those strings into J2:O2 (I can handle programming this)
5) Close the text file
6) repeat above for the remainder of filenames (about 1800 files)
At the time of opening the file some macros are running automatically in my file as per my requirement. If auto filter is on then it disturbs my program. Sometimes I forget to remove auto filter before closing the file and it cause a problem in my file after opening it again.
Requirement is: Is there any code to prevent the file closing if the auto filter is on and give me a warning message 'Please remove auto filter first'.
I already have a macro that on opening the workbook checks to see if the workbook has been renamed outside excel in windows and if so closes the workbook. I need a macro to intercept the save as command and prevent the user from saving the workbook as another name. I would however still like the user to be able to save. I would also like them to be able to save the workbook in another directory but only with the same workbook name.
View 4 Replies View RelatedWhen I am saving my spreadsheet as a text file, I have 1 column with formulas that I would like to not show up in my text file. I have tried hiding the column, and have Googled for awhile now.
View 2 Replies View RelatedIs there a way to disable the Convert button on the File menu and also to limit the Save As file types? I have a workbook that I maintain in Office 2003 that gets filled out by customers and returned. When a user with a newer version of Excel converts the file, it doesn't function properly when I go open it. I believe the conversion issue has to do with Active X Controls, but I'm using them to do some things that won't work with Forms Controls, so replacing them would be a last resort. Also, upgrading Office isn't an option at this point.
View 1 Replies View RelatedIn column A i have a range of dates
In column B i have a range of descriptions
In column C i have formula which calculates an interest figure based on the date entered in column A and a date entered in a cell elsewhere. It also makes the cell display a couple of different strings of text if conditions are not met e.g. It displays "too early" if the date entered is more than 4 years before the date entered in the cell elsewhere
The problem that im having is this:
When i try to calculate column C i want it to throw an error if there is anything but a numeric value in the column. But it is obviously considering the cells in column C with for e.g. "too early" as a "0". I have tried all sorts of formula to do this to no avail, IFTEXT, IFNUMBER, ISTEXT, ISNONTEXT etc etc.
Got a slight problem; I am making a "worker-plan", where you should be able to write a name on one file and have it updated (removed) from the equivalent columns on another file automatically. Here's an example with two separate projects:
Project 1 before and after changes:
Attachment 257366
Attachment 257367
Project 2 before and after changes:
Attachment 257368
Attachment 257369
You can see here how I intended it to work. When one worker is moved from Project 1 to Project 2, that worker should also be removed from those weeks in Project 1. The previous worker from Project 2 should also be removed from those weeks (in Project 2).
Are there any formulas that can make this work automatically? All week numbers will belong to the same columns in all files.
Edit: There are 30 files that should be syncronized, not just two.. I could also gather all files into one file with sheets, instead of having seperate files.
I have one main tab which I pull data from other various tabs. To match data the data, I create a "common unique ID", in other words I create a column on each tab and connect various cells with the ampersand.
So in each of the various tabs (and the main tab), I'll have a column with something like =A2&B2&C3
If the "unique ID" matches between the tabs it will pull back the desired phrase, or it will be left blank. (Just using an =IF(ISNA) formula)
If you have followed so far, on my main tab, I have a column for each of the various tabs. The information I am trying to match could be on any of the tabs, so I need a column using an =IF(ISNA) formula to attempt to match each tab. So, on the main tab, there are several columns which might have the phrase I am looking for. I use another ampersand function to add those columns. My problem is that occaisionally, what I am looking for is on multiple tabs. So, if I am normally pulling the phrase "Pending Loans Report" when a match is made, if it is on two tabs, the cell will add it twice "Pending Loans ReportPending Loans Report"
I realize this is confusing, so I attached a spreadsheet. The "main" tab I was referring to is sheet1. I really simplified it down to post here, but I think the main idea of my problem is still evident. The problem is line 15. Note how column M has "Pending Loans ReportPending Loans Report" - I'd like it to only say this phrase once. The macro pastes special values at the end, but I've highlighted some cells in red which show the formulas I used for that column. Columns N, O, P, Q, etc. all have virtually the same formula.
I want to type some data into a text box on a form, after I press enter I want to do sothing with the data (add it to a list box, no problem) and then go back to the textbox for another entry.
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call processdata
End Sub
Sub processdata()
A = TextBox1.Value
' Do something with A
TextBox1 = ""
TextBox1.SetFocus
End Sub
I can do somthing with the data but it does not jump back to the Textbox i.e. the setfocus does not work.
I have a large sheet that I am having some problems with. The information comes from our database. In column A I have a client code and other information over to column D. In columns E I have dates. These dates have comments that are associated with them entered in column F. The problem is that sometimes these comments wrap into the next cell below. I have done this in the past. But the problem is that in column G there are dates of when the action took place, the issue is that there might be 1 or 10 of these dates per client. And then in Column H there are comments as well that spill into the next cell below.
What I need is to have everything in the first line, the client code over to column E, Combining of the cells in column F that were broken over multiple cells, then in Column G the first date and its combined actions to the right. The next row might have no client code but might have another date in Column G and comments in column H.
This is really hard to explain what I am looking for exactly. I have attached a small file that has an example of what might be downloaded and then a sheet of what I need it to look like when it is done.
An Excel file named CLEARING is availble in thr central server system. it will be accessed by 5 or 6 staffs from some other systems what i need is when one person accesses/edits this file the others should not even able to open in the read only file ,when the file is saved and closed ,then only the next person should access it
View 1 Replies View RelatedI have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))
Is it possible to write vba code that will generate a text file with ALL changes that were made to an excel file. Ex. If Cell A17 = "Monday, June 4, 2012" and a user updates Cell A17 to "N/A", I would like to know what the value was before and after the udpate was made.
View 8 Replies View Relatedi want to browse a text file with 10 textual lines and rewrite the contents in the same file (starting from line number 11) from bottom to top.
View 9 Replies View RelatedI have a workbook with many spreadsheet named Sheet1, Sheet2 and so on. Each sheet is filled completely upto 65536 rows. This data is being picked up from a CSV file. In this file there are sites with each site there is a assciated set of data. What happens is the data that extracts data does not differntiate between sites and when it reaches to the end of worksheet it splits the data into next sheet. So I am trying to create a macro to check each spreadsheet starting with the last sheet in the work book for example last sheet in the workbook is seven it should go to sheet6 and if there is a blank row after row 64000 it should cut all the rows and move them to sheet7. Then it should goto sheet5 and do the same and keep on doing it until it reaches sheet1.
View 9 Replies View Relatedfound the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
I have a macro that takes a text file as input and produces an excel file as output. I want a macro to store the name of the text file in a variable (without its file extension).
View 1 Replies View RelatedUsing Excel 2010
As I am looping through files in a folder, I would like to determine if the file is a text file.
The problem is that all extensions are variable in a pattern such as .078, .051, etc.
In this instance, the extensions are numeric, but I'm trying to figure out a way to handle that is all encompassing to include *.txt, *.tsv, *.csv, *.prn, etc......
How can I handle these efficiently?
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath
'controle = Dir(pad$ + "FactuurNummer7.txt")
'If controle = "" Then GoTo EerstAanmaken
'Open pad$ + "Factuurnummer7.txt" For Input As #10
'Input #10, Nummer1
'Close #10......................
I've encountered a bit of an odd one. I am opening a tab delimited file, which has been sved as an xls. When I open from file manager, it opens correctly. When I open from Excel, I have to go through 3 screen dictating how the data is set up, and the end result is that a couple of the numbers come through as text, rather than numbers.
Failing an explanation - how do you open workbooks as file system objects - currently I am opening them using
Workbooks.Open Filename:=wb.Path & "/" & ws. Range("B1").Value
If I open them by mimicking the FM, perhaps it will work OK.
I want macro which export each excel column to new text file. The data in excel file is number. The column has only 5 rows that means each new text file should contain five lines of one column. It looks simple but couldn't manage to do macro for it. I have very big data set in one excel file, and have to be splitted into text files. The file name in new text files can be any kind as long as it can be in some sort of order for each export.
View 2 Replies View Related