XLS To CSV Macro

[JR]

Hello,

What I am trying to accomplish is I have xls files that I need to keep
updating, but also have to have the same xls file saved as a csv file for
importing into a program. Is there away when I modify or add items to the
xls the csv is automatically updated. I know I can link them and have tried
that, but when I link the two files, I copy the complete xls and paste
special to the csv, but I get all zeros on empty cells. Is there away around
this?

Thanks JR

 

[Earl Kiosterud]

JR,

A new CSV file must be written any time you want an updated csv copy. A csv
file is a text file, and can contain no links. When you save an xls, the
saved file is no longer an xls. It's text. One approach would be to write
a new csv file any time a change has been made to the underlying xls, either
manually, or via a macro. Another is to write a new csv file any time
you're ready to import to the other application.

 

[JR]

That would be perfect. If I could get a macro to automatically write a csv
file when xls is saved would work fine. If possible, I would like to have
csv filed saved to /csv directory. This is possible?

Thanks JR

 

[Dave Peterson]

There is an event that you can tie into (workbook_beforesave).

rightclick on the excel icon to the left of the File option on the worksheet
menubar.

Then select view code and paste this into the code window:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim wks As Worksheet
Dim newWkbk As Workbook

For Each wks In Me.Worksheets
wks.Copy 'to a new workbook
Set newWkbk = ActiveWorkbook
With newWkbk
Application.DisplayAlerts = False
Application.EnableEvents = False
.SaveAs Filename:="C:\WINDOWS\TEMP\csv\" & wks.Name, _
FileFormat:=xlCSV
Application.DisplayAlerts = True
Application.EnableEvents = True
.Close savechanges:=False
End With
Next wks

End Sub

(I used c:\windows\temp\csv\, since I didn't know where the \csv folder would
be.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about application events, visit Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm

 

[New]

Hello Dave,

This works great except for one item. Each xls I write, it over writes the
same csv file name (sheet1). Can the csv file name be the same as the xls
file name?

Thanks JR

 

[Dave Peterson]

Is there more than one worksheet in each workbook?

If yes, then this would be a problem. If no, then maybe this:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim wks As Worksheet
Dim newWkbk As Workbook
Dim newName As String

'remove the .xls from the name
newName = Left(Me.Name, Len(Me.Name) - 4)

If Me.Worksheets.Count > 1 Then
MsgBox "Design error!" & vbLf & "Save as CSV cancelled!"
Else
Set wks = Me.Worksheets(1)
wks.Copy 'to a new workbook
Set newWkbk = ActiveWorkbook
With newWkbk
Application.DisplayAlerts = False
Application.EnableEvents = False
.SaveAs Filename:="C:\WINDOWS\TEMP\csv\" & newName, _
FileFormat:=xlCSV
Application.DisplayAlerts = True
Application.EnableEvents = True
.Close savechanges:=False
End With
End If

End Sub

 

[Earl Kiosterud]

Hey Dave,

That's not a design error! It's a user error!