I need to convert some xlsx files into CSV, but they contain the following foreign languages -
Polish
Czech
Romanian
Hungarian
When saving these files as CSV many of the unique characters get changed to a ?
I have tried a few things like using Open Office and saving it as a text CSV and then adjusting the formatting to unicode 8 but it hasn't worked. i am using Excel 2013?
I have a pivot table like the one below.
What I would like to do is filter the drill down keeping the total of the products (in bold) and showing just one of the name (just ENTA for Example).
Basically I would like to add a filter that Hide some of the data keeping the row total.
I'm Using Excel 2013.
Products
Sell out 4 weeks
Stock Units
Avg 4 weeks
Wks of stock
3160-24PC-AP12
1
[code].....
I have attached a spread sheet with some code I recoded with macro recorder. I have been searching for some extra code to insert in the middle of the recorded code which will remove the first 5 characters from the active cell and past the result to the next page. I have seen a lot of relevant code but haven't been able to get any to work in my code.
[Code] .....
I am using Windows7 with Excel 2013.
Attached File : DeleteFirst5Char.xlsm
I am using Microsoft 2013 and am looking for 4 formulas in order to split 1 cell in to 4 (across same row). Number of characters varies between each instance of """.
I would like the formulas to start in column B-D (data in A)
I am looking at formulas based on specific instances of a quote mark in cell from data in column A
Formula 1 - Return with characters up to and including the 5th instance of """
Formula 2 - Return with characters after 5th """ and up to and including 6th """
Formula 3 - Return with characters after 6th """ and up to and including 7th """
Formula 4 - Return with characters after the 7th instance of """
Example below...
Cell A1
xxyy","aabb","ee,ff,""gghh"hh,"llmm,mmnbijp"oossww"
Desired results
Cell B1 - Formula 1
xxyy","aabb","ee,ff,"
Cell C1 - Formula 2
"gghh"
Cell D1 - Formula 3
hh,"
Cell E1 - Formula 4
mmnbijp"oossww"
In Excel 2013, how do I translate all characters of selected cells to Unicode?
There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.
In Excel 2013, how do I translate all characters of selected cells to Unicode? There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.
View 10 Replies View RelatedI'm working on excel document, but despite my efforts, I can't convert B and D columns to numbers.
All numbers align on the left side, instead of right and formulas such as average and sum doesnt seem to work.
[URL]
how to convert .xlsx file into .csv?
View 4 Replies View RelatedI received a few 1-3gb xml files from the government and I would like to open them in an Excel format. I have tried opening them using Excel 2013 (both as an xml table and as a read only workbook) with a machine running 8gb RAM and a 3.4 ghz i5 processor. I left it running overnight but Excel continues to "not respond". The file is intended to display thousands of line items with around 40-50 columns of data. For previous (and much smaller) similar files, the saved workbook is a manageable file. The only hurdle here is getting these open.
View 5 Replies View RelatedI have to download files from a database and save them in .xls format (the only format permitted to export from the database)
Given an open excel wb saved to my hard drive (or network drive) in non .xlsx format. (most likely .xls)
Switch the file to .xlsx format without loosing the open file. (re-open ok).
Caveats:
-Don't want two files
-Dnn't want to have the file name changed (except for the extension).
-Don't want to remain in compatibility mode.
-Don't want to wait a long time.
-Don't want to have possibility of file corruption.
I just want to take my non .xlsx file and then, turn into a .xlsx file. (if I save the file as .xlsx I am still in compatibility mode until I re-open the file. I also will have two files---very annoying).
I have a series of worksheets (different workbooks) that have the same exact column widths and line depths. On screen the width and depth is identical but when printed, one takes up less width and more depth and the other vice-versa. Most (over 100) are just fine but every once in a while another is found that is distorted in print preview and printing and there seems to be nothing I can do to correct the problem. One difficult solution is to take the content from the "wrong" worksheet, do a save as on a "right" worksheet and drop the content in there.
Office and Excel 2013Windows 8Saving as different versions (xls instead of xlsx) makes no difference.Same printerSame monitorsAll worksheets are set to 17 pixels for both columns and rows (looks like graph paper)After setting up print area and viewing, the light gray dashed lines that show page breaks are different! The majority ("right") sheets have four fewer columns and four more rows than do the "wrong" worksheets.
what I need to do to update a folder full of files. There are 120+ .xls files in one folder, the merged cell A30-V38 needs to change its current text to "4th Quarterly Printer Preventative Maintenance".
I have zero prior knowledge of visual basic, and have Excel 2013.
Using Excel 2007:
I am trying to use dir() in a While...wend to get a specific list of files to process, but I want the list to only include those with a .xls extension, not those ending in .xlsx. It seems that the file filter ".xls" doesn't exclude those files with a ".xlsx" extension.
Sample code demonstrating the problem (will show the problem if there is at least one cycle that meets a*.xls and one that meets a*.xlsx).
Is there a workaround to this at the dir() level? Or do I need to go ahead and open the file and test its contents?
Sub TestDir()
On Error GoTo Nofile 'trap cancel, X-out of the dialog, etc.
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.InitialFileName = "a*.xls"
.Show
GetDir = .SelectedItems(1)
End With
On Error GoTo 0 'reset error handler
I is it possible to convert all comma separated text files in a single folder in to excel files. But the requirement is to have 2 sheets in each new file. first to be the full file - with all columns, and in the second sheet to keep only colum A B D G H K L M O P R S T V W from the first sheet. The second sheet name must be the same as the first one but without the first "wlist_" in the name.
One more thing. The third column in the second is called "COUL". there are short letters for colors in french
can they be converted with the sort in English like it goes:
NO = B
BA = W
RG = R
SO = P
JA = Y
BE = L
VE = GY
GR = G
VI = V
MA = BR
BJ = TA
OR = O
Here is a link to the both CSV and an example excel file with the end result. In this example i haven`t change the shorts for the colors. It takes me too much time with the find and replace function. And at the moment i`m really pushed from time.
[URL]
how to run my Macro over multiple (about 60) .xlsx files. I have been looking around, but haven't quite got anything working yet.
I have all the files in the same folder with similar names (i.e. Subject_01.xlsx, Subject_02.xlsx, etc.).
What I'd like is:
*open Subject_01
*run script (some calculations, copy/pasting, my script for this is working fine)
*save file under a different name (i.e. Subject_01_processed.xlsx)
*close file
*again for Subject_02, Subject_03, etc.
Additionally, I have a second question: after I have the processed .xlsx files, I'd like to copy some cells from each .xlsx file (e.g. A1-H1) to a new file. There, I'd like the data from Subject_01 to for example be in cells A1-H1, from Subject_02 in cells A2-H2, etc.
I have a coumn starting from A2 onwards that holds data with various characters, all that i require is the 1st 9 characters (These are always numbers).
View 6 Replies View RelatedI Use the Quickbook 2009 for accounting. I need to convert CSV files over to QBO files. Any Software are not to Convert CSV files to QBO files.
It's Possible to Convert the CSV files to QBO Files via Excel.
Is there a way to convert all files in folder, in this case, xml in excel format to xls without open them?
I recorded the macro below, but this needs to "Open" and "SaveAs" the current file in folder and there are a few thousands of xml files in folder.
I was wondering if through some ADO or VBA code this can be done without open the files in order to save resources and get the work done faster?
Code:
Sub Convert_XML_to_XLS()
Workbooks.Open Filename:="C:MyPathInputFile.xml"
ActiveWorkbook.SaveAs Filename:="C:MyPathInputFile.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False '
End Sub
I'm trying to figure out how to copy raw data from one file(emailed to me) and paste it to my existing file "File b" into a table "tbl a"(to make it dynamic). From another table "tbl b" on another sheet within in "File b" I want to auto populate "tbl b" with all the records from "tbl a" but not all columns from the records. To make it more difficult, I want to edit some of the data and the headings between the two tables are not the same. Example below.
Ship-to-name
Product Name
Date Shipped
Customer Group
ABC-Atlanta
Advil (Ibuprofen) 800MG
11-15-2013
Wholesaler
[code].....
Notice column b has different heading and the data need to be modified. Column d is not needed at all.
Also this need to happen when the raw data is copied into "tbl a" and again all records need to be copied over with changes.
This also needs to be done outside of VBA if possible using just formulas and possibly filtering.
I'm using MS Excel 2013
i have many file text which i want to open in an excel workbook in a way that each text file will be displayed in an excel sheet.
View 3 Replies View RelatedI am using the following code to open a woorkbook:
Workbooks.Open Filename:= "C:UsersDesktopExcel.xlsx"
I am running into an issue were a file may be saved as .xls or .xlsm instead, but I still need to have the macro open it. Is there a way to adjust the above code, or other code for that matter, that will allow my macro to open any of these extensions?
I have about 100 Excel files in one folder that need to be saved as text files. They can keep the same name, but simply need to be converted to text files. I'd like to use VBA for this and I can't find examples that do exactly that...or ones that my limited knowledge can handle.
Excel files exist in C:Source and ALL of them should be saved as text files in C:Destination. Maybe there is an easier way, but I thought for sure there was a routine I could use.
Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.
Mouse, Mickey ;
Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).
I would like to be able to execute a command line that will convert an Excel csv file to an Excel file that is TAB delimited. In other words, replace the comma delimiter with a TAB delimiter without having to open the file in Excel. I am an inexperienced Excel user
View 2 Replies View RelatedWhat I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.
All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.
I am creating an Access database from excel. In the code I get the Database and Tables name from Excel spread sheet and assign them to variables.
In the first table, & tblNamePrime &, I create a Primary Key called, [PrimeRecId]. I create a second table, & tblName & and try to create a Foreign Key called, [RecId] and try to Reference it to the table, & tblNamePrime & [PrimeRecId]. I always receive the following error message:
Syntax Error in CONSTRAINT Clause
-2147217900(80040e14)
If I remove the code in blue it creates the tables but then I need to go th access and link them by access
I have tried many different ways and have followed instructions from SQL, Access VBA and I am unable to Link the Primary Key to the Foreign Key.
I am new at trying to tie Excel to Access and SQL.
The code below is just 2 tables but when I am done there will be a total of 4 tables all linked back to the first table.
Private Sub RDCModel()
Dim dbConnectStr As String
Dim Catalog As Object
Dim cnt As ADODB.Connection
Dim dbPath As String
Dim tblNamePrime As String
Dim tblName As String
'Set database name here
'MsgBox "Current user is " & Application.UserName
dbPath = ActiveSheet.Range("C1").Value 'Database Name.........
Is there an easy way to convert ascii characters to their equivalent hex value, such as 0 = 30?
View 6 Replies View RelatedI have a sheet that the first words in the cells are in Arabic. How can I move the foreign words to another column? The number of words varies and they are all in the beginning of the cell.
View 7 Replies View RelatedI have two access databases which export results to two excel files. I am trying to compair the two excel files (generated by access databases) to find the common data in the two files. I am using Vlookup function.
The data looks like this
File 1
Serial No Fault
40293 A
40294 B
File 2
Serial No Solution
40293 Procedure 1
40294 Procedure 2
The result should be
Serial No Fault Solution
40293 A Procedure 1
etc...
But the column serial no in file 1 has some unknown characters like 40293followed by a small square or a vertical line. The datasource for the access file that generates file 1 is actually a lotus notes database. I think thats what causing this. Is there a solution to clear these spurious characters?
The VLookup function works fine if I manually delete those characters.
Say I have a string "09800EBHR052708"
How would I take the first 5 characters and get a number out of them?