Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xls to Csv -- remove formatting
Not sure why .SaveAs csv does not achieve your aim.
What are you trying to achieve ? NickHK "swtransaction" wrote in message news:swtransaction.2ci1rh_1155507619.4927@excelfor um-nospam.com... I'm using this to convert XLS to CSv files in batch. The XLS files are formatted and it would help a lot if they were not. Can I automate removing the format? Thanks !!!! Private Sub cmdXlsCsv_Click() On Error GoTo MyError Screen.MousePointer = vbHourglass Dim xls As Excel.Application Dim oWB As Excel.Workbook Dim tmp As String Set xls = New Excel.Application tmp = Dir("C:\cmo\*.xls") Do While tmp "" Set oWB = xls.Workbooks.Open("C:\cmo\" & tmp) 'clear formatting here oWB.SaveAs FileName:=Replace _ ("C:\cmo\" & tmp, ".xls", ".csv", , , vbTextCompare), _ FileFormat:=xlCSVMSDOS, CreateBackup:=False oWB.Close SaveChanges:=False tmp = Dir Set oWB = Nothing Loop xls.Quit Set xls = Nothing Screen.MousePointer = vbDefault MyError: If Err.Number = 1004 Then MsgBox "Csv File Exists", vbOKOnly + vbExclamation xls.Quit Set xls = Nothing Screen.MousePointer = vbDefault End If tabManage.SetFocus End Sub -- swtransaction ------------------------------------------------------------------------ swtransaction's Profile: http://www.excelforum.com/member.php...o&userid=37459 View this thread: http://www.excelforum.com/showthread...hreadid=571210 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Xls to Csv -- remove formatting
Columns("A:Z").Select Selection.NumberFormat = "@" The code above does it (my spreadsheets never go past row Z) Here was the problem, there were a couple columns formatted a currency, so I was getting in the saved csv file: xxxxx,"$3,001.45",xxxxxx With that code above, it is now: xxxxx,"3001.45",xxxxxx No dollar signs or commas It is working now!! -- swtransactio ----------------------------------------------------------------------- swtransaction's Profile: http://www.excelforum.com/member.php...fo&userid=3745 View this thread: http://www.excelforum.com/showthread.php?threadid=57121 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to remove all formatting | Excel Worksheet Functions | |||
"Excel encountered an error and had to remove some formatting toavoid corrupting the workbook. Please re-check your formatting carefully." | Excel Discussion (Misc queries) | |||
How to remove all formatting. | Excel Discussion (Misc queries) | |||
Remove all formatting | Excel Worksheet Functions | |||
Remove formatting from SSN | New Users to Excel |