I have files which are in "3I8" format....they open with excel....but the real problem is the data it has ( 121 801 1) is in one cell.....I should be able to add the last number....I cannot do it manually(8000 rows)...is there a way I can separate all three numbers in to each different cells in a single row.
View 3 RepliesI saved my worksheet in vba .xlsx and for some reason, when I go to the folder that it's saved in and try to open the file I get an error message saying "excel cannot open the file 'PO Acks fo 09-20-2013.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file"
This is weird because I have another file that I save right before this that opens just fine without an error. Getting frustrating. When I go into the ~Users folder it shows as an excel file and saved as .xlsx.
Where would the link be missing?
Here is the code
Code:
ActiveWorkbook.SaveAs Filename:= _
"G:PURCHASINGSupplier ServicesGroupSharePublic~UsersPO Acks for " & Format$(Date, "mm-dd-yyyy") & ".xlsx"
i have an issue with CSV file format when opening in excel, between Col G and L i have cells which are overlapping to the next cell, not all of them overlap but there is quite a few, is there a way i can fix this, i have 20000 rows of data, i dont want to be going through manually trying to fix.
PFOLIOA/C CODEACCT NAME CURCODE DESCRIPTNNARRATIVE DATE ENTRY DATE CASH VALUE De--------------------------------------------------------------------------------------------------------------------------------------------ABA001AUDCUST NOMINEES AUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.57N
This is what i want to achieve
PFOLIOA/C CODEACCT NAME CURCODE DESCRIPTNNARRATIVE DATE ENTRY DATE CASH VALUE De--------------------------------------------------------------------------------------------------------------------------------------------ABA001AUDCUST NOMINEES AUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.57N
Why excel won't accept a simple COUNT function?
This is the formula. It is supposed to count every 4th cell in a column.
=COUNT(F156,F152,F148,F144,F140,F136,F132,F128, F124,F120,F116,F112,F108,F104,F100,F96, F92,F88,F84,F80,F76,F72,F68,F64,F60,F56,F52,F48,F44,F40,F36,F32,F28,F24,F20,F16,F12,F8,F4)
Excel tells that it will accept up to 255 arguments in a COUNT function, but it'll only let me include:
=COUNT(F156,F152,F148,F144,F140,F136,F132,F128,F124,F120,F116,F112,F108,F104,F100,F96,F92,F88,F84,F80,F76,F72,F68,F64,F60,F56,F52,F48,F44,F40)
I have to use this exact same formula as a SUM function as well, but am having the same problem. Neither SUM or COUNT will take all the arguments.
I've attached the file. The tabs in red are the worksheets that won't accept the formula. The green tabs worked fine (they all had less cells to include than the sheets in red.)
01-09-14 RouteSheets SLO.xls‎
I am trying to save a file into a CSV format. When I choose that save as option the file is saved with the commas but a need each field to be surrounded by quotes.
Is there an easy way to do this? I know I could append the " around the data in each cell with a formula, but I'm hoping there is a cleaner way to do it.
I would also like to set the extention of the saved file to .txt when I save it, but Excel 2000 does not seem to let me do that.
i have a customer (im a computer tech, but excel is not my specialty) that has a weird problem with her most important file. She meant well, but tried to back up a 2.6 MB file to a floppy disk, and after it told her the disk was full, she was unable to open the file again in excel. (File Format Is Not Valid) It will, however, open in word, with the data there. Its in an unusable format, as there were 12 different worksheets (one for every month), all on the one page of MS Word. Better than nothing, but still will take weeks to reenter properly. I searched for a repair utility and had one that seemed to work, but only put a "0" in column A row 8. A far cry from what we are looking for. Ive also run a disk recovery program in the hopes of finding an older version of the file, but came up empty.
View 9 Replies View RelatedI have a file with code attached and when I try to save it I am told that the file cannot be saved in this format and that I must unprotect the code before saving.
I have no OnEvent macros in the code.
Does anyone have a solution for this problem. The code must be protected, non-protection is not an option.
My company uses database software called Business Objects. At the moment few people are using the program and its reports as they can't find the key ones or workout how to run them correctly. As everyone is comfortable with excel. I am creating a series of forms to guide my users to the key reports. I like this method because it is very user friendly and I can add lots of descriptions. Unfortunately my programming knowledge is limited to excel so I can't write a standalone program as you might suggest I do.
The problem:
I want to launch the non-excel files using VBA. For example, a user will click on a "Customer" button to see a second form containing a list of customer reports. The user will then click on the button containing the name of the report they want to launch. I want the file to open. I tried using hyperlinks in an excel spreadsheet which works fine for opening the non-excel files, but it isn’t nearly as user-friendly or interactive.
I am facing a weird date format error in .csv file for a specific time periods.
I have a file where data is stored on monthly basis from the years 1976 uptil today. I have all the data months in expected format except for the years 1989, 1990 and 1991. These files are created by using Informatica and SAS.
For example:
Expected date format = 1-January 1989
Actual Date Format = 1-Jan-89
This issue doesn't exist when we open the same file in Excel, Textpad and Notepad.
I have attached screenshot of the data (filename: CSV Issue.JPG).
I have done this several times in the past, but don't do it often. I have an
Excel spreadsheet that I need to save in DBase IV format, but when I try, it
cu ts off all of the columns to the right of what I can see on the screen
when I go back into the DBF file.
I have received a large file of text and numbers arranged (loosely) in columns in a .txt file that I need to get into a usable form in Excel. I'm trying to use the Text Import Wizard but am running into problems geting the column breaks right as there are thousands of rows of data items. As soon I think that I've inserted the break line in the correct place I scroll down a little further to find an item that encroaches into the next column, and when I shift the break line to the right to accommodate this new item I then encroach on the items in this next column.
View 2 Replies View RelatedI am working on an excel dashboard to display life information about energy consumption.
there is currently a bespoke syatem which does this but we need the dashboard to be changed so the job has fallen to me.
Energy usage across the site is recorded in a spreadsheet (automatically) every half an hour and is saved in a folder named CSV data, however the files are saved in a format which i assume is something for the current package: *.ew7
I can open and view the data by clicking "open as" and selecting excel.
I can reference to this data from another excel spreadsheet (dashboard) whilst they are both open it will update.
However when i close all spreadsheets and open just the dashboard it tells me that the links cannot be updated.
I need a solution or a way around this so i can reference from one workbook to another.
Has anyone had any experience of this kind of thing? can anyone offer a humble analyst a bit of advice?
I was not able to attach the file here (as its in the ew7 format) but i have uploaded it to rapidshare for anyone who wants to take a look...
I am running a DOS command to create a directory listing of ONLY DIR files with the short dos filenames and the long filenames. The command used is
DIR /A:D /X /N /S > c:sampledata.txt
Once read into excel, I simply want to have a spreadsheet with the following:
Directory Path Name, DIR Short Name, DIR Long Name
Anyone have some code to already do this? I have attached the sampledata.txt file to go with the pseudo code.
I have the following code which is part of a much larger macro which is converting a number so that the the trailing zero in the number 49.50 appears in the formula bar. This is essential as the eventual csv file links to an external printing program which only prints what is sees.
My problem is that this fix works when the file is saved in excel format, but when I save it in CSV format the number reverts to 49.5. How can I convert the original number which appears as 49.5 in the formula bar and is viewed a 00000050 in the file that the sent through. I cannot convert using the text to columns when the file is opened as the other codes in the original file need to maintain the original formatting.
Code:
Sub mc003()
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
[Code]....
Isn't there a checkbox somewhere where I can tell Excel 2007 to leave xls files as they are? My Excel 2010 doesn't urge me to save an old xls file format into xlsx but in Excel 2007 I can't find a switch (and I don't rememeber if I had to do anything in 2010). So ultimately I want to stay in compatibility mode when using old files.
View 2 Replies View RelatedHow do I export a file in .bin format
View 9 Replies View RelatedI'm just wondering how you would go about extracting a date from a filename and formatting it into a different format so you can . Find with it. The file names are always in the same format with the same prefix. So each file is something like ABCDEFGyyyymmdd.xls. I'm really lost on where I should start with this one
View 4 Replies View RelatedI am using a CSV that has the date in Column A in the format of DD/MM/YYYY. When I open this CSV using a VBA comand the Format of the Cells in this col are then formated as either Date or General. The general format happends when the date cant be turned into US format and when it can then the date is turned US and displayd as such.
SO
14- Sep = 14/09/2007 as General
10- Sep = 09/10/2007 as Date
Im in Australia so the correct format is DD/MM/YYYY. Microsoft has issued a fix for this using Local:=True in the file open comand which is all good and well as it works unless you have office 2000 when it crashes. What would be the best way of fixing this, so that the Dates are all correct or the col is all the same format?
In any other software, when I save a copy, or export to a different format the original file remains unaffected but in an excel it seems to work differently...and I end up having to save my file back to the xslx format. this extra time wasted when I am regularly exporting but it also makes me a bit nervous that I might accidentally not save back to the format with all my worksheets etc etc..
View 1 Replies View Relatedcode to convert excel file format into Text.
We are doing this activity on a daily basis to upload into our EPM system , where our system only accepts TXT file.
find enclosed excel input file and output file which should be in TXT format.
Apparently my program quits after i try to open an excel file. If you open the file manually you get an error upon opening the file...something like this:
"File you are tyring to open is in a different format than specified by the file extension".
The problem is i am using an excel macro using VBA and once the file is opened the VBA execution quits.
The excel file turns out to be an xml file which excel can open the file and have the VBA continue running if i RENAME the file with an "xlsx" extension. The problem is that i cant ask the user to rename the file.........how do i get VBA to open the file without an issue?
What I have is a double quoted, comma delimited text file that is created from an external application, every now and again we need to open the file to make corrections and this is easier if we open it via Excel using the delimiter options, the problem is when saving the file as a csv it removes the double quotes, however I have found some vba coding that adds the double quotes back, which works great, but it changes the format of the date fields from what the original file had from 09-Aug-2009 to 09/08/2009.
I suspect this may be because the file is opened before the double quotes are added and excel changes the format.
Is there any code I can add that will stop the date format from changing ...
I want a macro that will open a without fiile in a folder and convert without format file whose name is like
"cognos.701Z%27%5d&SA=propEnum,properties&ITEM=data&EA=&SS
=queryOptions,options&dataEncoding=MIME&ES=&EM=" to Excel (.xlsx).
Save excel file in same folder and delete above file.
I am having problems choosing a format for the column in my spreadsheet that contains my account codes. This is especially a problem when the code starts with an '=' sign and is followed by a number or has a '£' sign followed by a number.
I receive the file in a .txt format which I guess if I get it that way I would not have a problem but as I need to work on the file in excel before uploading it I experience problems. For instance when I open the file the codes that begin with '=' + a letter for instance =Y just comes up as '# name?' and only when I click in the cell can I see that it is =Y. Also the code '=1' just comes up as '1' as does '£1' - now this causes problems as for a start I need to do Vlookups on the account code but now instead of three distinct codes I have 3 1's.
The only way that I have discovered how to solve this is to click into each cell and add a ' before the text. (this changes the format to text - right??) But obviously this is very time consuming - is their a quicker way to change these cells. I can't just right click and format cells - text. This does not work?
I've been doing this excel worksheet for work; even though I've never used excel macros or VBA before.(So if this is an easy question to answer don't laugh at me. :P) (I have worked with other programming languages though.) Anyway, using a VBA manual, I've done pretty well, until now. I'm trying to make a macro that when one clicks on the button it saves the file as a text tab delimited file, but giving it the name of whatever the user put into a specific cell. (And also without closing the excel file too, so they can add more if they have to) So I've gotten it now so that it saves with the correct name, but I am unsure about how to make it save as the text file I want. (At the moment it saves as an unreadable file) Any help would be great thanks.
Here is the ...
I need to bring in 2600 student names from a cvs file and have it formatted to an Excel workbook.
When it imports into the Excel file I need it to populate the proper boxes, such as:
StudentLastName
StudentFirstName
StudentID
GradeLevel
TeacherName
TeacherID
RoomNumber
I have the CVS attached. Hope I can get this done. As you can see it shows the teacher's name with their first initial, their teacher number that they are identified by and their room number. I need the student ID as well but not the phone number and the days of the week. This would save a lot of work.
The attached file "Temp.xlsm" has been provided to me. I need to generate two files from it - 1) csv 2) a text file in a certain format. It should basically look liked the attached file "InFile.txt" which is an input file for a modelling tool. The arrays and struct strings need to be within brackets and it only contains inputs. Outputs don't need to be generated.
With the code in "Temp.xlsm" so far I have been able to generate attached file "Temp.txt". The only missing bit is the formatting of arrays and structures.
Basically for row7 in "temp.xls" , if it is
Xls - In
Struct_Start - (
Array_Start - (
Array_and_Struct_Start - ((
Struct_End - )
Array_End - )
Array_and_Struct_End - ))
and members within these need to be separated by commas.
I want to save the Excel file as "Excel Macro-Enabled Workbook." Currently, File Format:=xlNormal within the VBA code and the results saves the Excel file as .xls. How do I write the File Format to save the Excel file as "Excel Macro-Enabled Workbook."
Below is an example:
ActiveWorkbook.SaveAs Filename:=Left$(ActiveWorkbook.Name, (Len(ActiveWorkbook.Name) - 4)), FileFormat:=xlNormal, CreateBackup:=False
I have a large text file that is generated daily and want to import into MS Access as the end result.
First I need to reformat into the row format in excel rather than the format it is in. The issue is not all the segments are the same number of lines or they may have mutli message lines. The names with colon : after them I want to be field names in the table which I wish the text file to import into. Please, I am looking for assistance with this.
I have attached sample data of the text dump, and the name of the file is as you see but different date on the end. At the very end of the text file you will see the desired output which I will then import into Access
Also every new message begins with the dotted line and the date and time at the end.
The following code stops at the red line with "Method 'SaveAs' of object '_Workbook' failed.
FF="xlCSV". It works fine if I replace
Code:
FileFormat:=FF
with
Code:
FileFormat:=xlCSV
Code:
Sub SaveIt(FileNm, FF)
Dim FSO As Object, a
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(FileNm) Then FSO.DeleteFile FileNm
If FSO.FileExists(FileNm) Then
[code]....