XLS To CSV Macro

  • Thread starter Thread starter JR
  • Start date Start date
J

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
 
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.
 
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
 
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
 
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
 
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
 
Back
Top