Xls to Csv -- remove formatting

S

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
 
S

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top