autosave as different file type

  • Thread starter Thread starter maryanne
  • Start date Start date
M

maryanne

hello. i have encountered a situation, and searches of
items already on this newsgroup have not yielded any
results. i have a .xls file that can be edited by several
members of a development team, and i've written code where
i post a report to the web using certain columns of the
most current info in this .xls file. however, i use
the .csv version of the file for my code for the web
page. is there a way that the .xls master file can also
be saved as a .csv file whenever someone makes changes?
i'm looking for an alternative to doing this save as...
task manually every time a member of the team makes a
change because i do not know every time a member may make
a change. any suggestions? thank you in advance.
 
...
...
. . . is there a way that the .xls master file can also
be saved as a .csv file whenever someone makes changes?
i'm looking for an alternative to doing this save as...
task manually every time a member of the team makes a
change because i do not know every time a member may make
a change. any suggestions? thank you in advance.
Click to expand...

If you don't want to do this manually, automate it. Ideal place would be in the
BeforeSave event handler, which would be placed in the ThisWorkbook class
module.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fn As String, ff As Long

On Error GoTo CleanUp
Application.EnableEvents = False
Application.DisplayAlerts = False

fn = Me.FullName
ff = Me.FileFormat
If StrComp(Right(fn, 4), ".xls", vbTextCompare) = 0 Then _
fn = Left(fn, Len(fn) - 4)

Me.SaveAs FileName:=fn & ".csv", FileFormat:=xlCSV

CleanUp:
On Error Resume Next

Me.SaveAs FileName:=fn & ".xls", FileFormat:=ff

Application.EnableEvents = True
Application.DisplayAlerts = True
Cancel = True
End Sub
 
i'm not sure how fancy you want this, but in Excel 2002 (aka Excel XP), you
can goto:

Tools
Options
Transitions tab
Choose CSV (comma delimited)

All workbooks will now default to CSV format; of course, if you only want
your master
file to default to .csv and you want everything other workbook to default to
..xls, my
suggestion won't work.

Let me know how it turns out.
 
Back
Top