Xls to Csv -- remove formatting

[swtransaction]

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

 

[NickHK]

Not sure why .SaveAs csv does not achieve your aim.
What are you trying to achieve ?

NickHK

"swtransaction" <[email protected]>
wrote in message
news:[email protected]...

 

[swtransaction]

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!!