LinkBack URL
About LinkBacks
Hello members,
How can I change the 'cell format' of all columns (A-E) to decimal number for all files (*.csv) in a folder.
How to change cell format to decimal number
Hello members,
How can I change the 'cell format' of all columns (A-E) to decimal number for all files (*.csv) in a folder.
Last edited by Moriexcel; 01-06-2015 at 02:45 AM. Reason: omit the 'save as .dbf' !
i believe the save as .dbf option was removed from excel 2007.
this will open your books.
there may be some addin for saving into .dbf though it would seam easer to use a earler version
Sub OpenFiles() Dim MyFolder As String Dim MyFile As String MyFolder = "C:your locaion here" MyFile = Dir(MyFolder & "\*.xls") Do While MyFile <> "" Workbooks.Open Filename:=MyFolder & "\" & MyFile MyFile = Dir Loop End Sub
Last edited by D_Rennie; 01-06-2015 at 12:38 AM.
You're right. I've just installed 2003 version! However, more important is to change the cell format.
The code is for looping through folder. Thank you. Any idea about the main issue?
in the loop Columns("A:E").NumberFormat = "0.00"
then save each book Workbook.SaveAs Filename:=MyFolder & "\*.dbf" this may not be the correct syntax as i only have 2007 installed so no way of cheeking.
Last edited by D_Rennie; 01-06-2015 at 01:02 AM.
I used this code but it is not working
Sub LoopThroughFolder() Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook Dim Rws As Long, Rng As Range Dim i As Integer Set Wb = ThisWorkbook MyDir = "C:\MY Location\" MyFile = Dir(MyDir & "*.csv") 'change file extension ChDir MyDir Application.ScreenUpdating = 0 Application.DisplayAlerts = 0 Do While MyFile <> "" Workbooks.Open (MyFile) With Worksheets(1) Columns("A:E").NumberFormat = "0.00" ActiveWorkbook.Close True End With MyFile = Dir() Loop End Sub
try putting a . in front for columns to qualfiy the line of code to that workbook.
Rennie means this.....
.Columns("A:E").NumberFormat = "0.00"
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Nothing was changed ! I used F8 and went through the code line by line, but the mentioned code is not changing the cell format. Although there is no bug in the code.Originally Posted by sktneer
Rennie means this.....
.Columns("A:E").NumberFormat = "0.00"
May be your Numbers in Columns("A:E") are not real numbers and may be Text Data or Text Numbers
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Although there are all not numbers (I have header row), there are all in same format, 'general'.May be your Numbers in Columns("A:E") are not real numbers and may be Text Data or Text Numbers
Ooo, sorry. That's why I was not mentioned my data format and asked a general question.
I just attached a sample file to the original question.
May be replace this
WithWith Worksheets(1)
With ActiveWorkbook.Worksheets(1)
And check....
Thank you all.
Although nothing changed in Excel, I was noticed cell format changes when I save the files as .dbf
However, to whom may use this program later, I would suggest to check the results whether the values are changing or not as well.
Because I learnt that in dbf file, in some cases, decimals are rounded in following way.
0.1 to 0
1.5 to 2
By the way, if anyone found a better way with no error, I will appreciate to mention it here.
Thank you again.
Last edited by Moriexcel; 01-06-2015 at 04:30 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks