Save Worksheet as csv

[Gene Augustin]

MAC Powerbook G4, OS 10.5.6
Office 2004, Excel 2004 Version 11.5.3

I do a lot of manipulation with a macro in a workbook and end up with
several sheets and I save using the macro. So-far, so-good.

After the save, I activate a certain worksheet,
shtold.Activate

Where shtold is the name of the worksheet Sheets("QIF")
This name changes from time to time.

Then, SAVE AS a csv in the same folder as the original and reopen the
original

This all works except
1 I don't know how to format Filename with the name the variable name of
the worksheet with the date the current date in "mm-dd-yy" format appended.
Perhaps some type of MsgBox or FormBox to input the name, but that would
require user intervention.
2 The format is wrong to reopen the original

The lines below without a comment work.


Dim shtnew As Worksheet, shtold As Worksheet, bookname As Workbook

ActiveWorkbook.Save
bookname = ActiveWorkbook.Name
shtold.Activate

ActiveWorkbook.SaveAs FileFormat:=xlCSV, Filename:="QIF-CSV" 'need to change
Filename

ActiveWorkbook.Close SaveChanges:=False

Workbooks.Open.Name = bookname " 'wrong format

End Sub

 

[Bob Greenblatt]

MAC Powerbook G4, OS 10.5.6
Office 2004, Excel 2004 Version 11.5.3

I do a lot of manipulation with a macro in a workbook and end up with
several sheets and I save using the macro. So-far, so-good.

After the save, I activate a certain worksheet,
shtold.Activate

Where shtold is the name of the worksheet Sheets("QIF")
This name changes from time to time.

Then, SAVE AS a csv in the same folder as the original and reopen the
original

This all works except
1 I don't know how to format Filename with the name the variable name of
the worksheet with the date the current date in "mm-dd-yy" format appended.
Perhaps some type of MsgBox or FormBox to input the name, but that would
require user intervention.
2 The format is wrong to reopen the original

The lines below without a comment work.


Dim shtnew As Worksheet, shtold As Worksheet, bookname As Workbook

ActiveWorkbook.Save
bookname = ActiveWorkbook.Name
shtold.Activate

ActiveWorkbook.SaveAs FileFormat:=xlCSV, Filename:="QIF-CSV" 'need to change
Filename

ActiveWorkbook.Close SaveChanges:=False

Workbooks.Open.Name = bookname " 'wrong format

End Sub

If shtold contains the name of the worksheet, and you want to append the
date, use:
filename:=shtold & format(now(),"mm-dd-yy")

To reopen the file, just append ".csv" to the above string.

 

[Gene Augustin]

MAC Powerbook G4, OS 10.5.6
Office 2004, Excel 2004 Version 11.5.3

This gives error: 91: Object variable or With block variable not set.
DIM bookname As Workbook
bookname = ActiveWorkbook.Name

This gives an error: 438: Object doesn't support this property or method

ActiveWorkbook.SaveAs FileFormat:=xlCSV, Filename:=shtnew & Format(Now(),
"mm-dd-yy")

 

[Bob Greenblatt]

MAC Powerbook G4, OS 10.5.6
Office 2004, Excel 2004 Version 11.5.3

This gives error: 91: Object variable or With block variable not set.
DIM bookname As Workbook
bookname = ActiveWorkbook.Name

This gives an error: 438: Object doesn't support this property or method

ActiveWorkbook.SaveAs FileFormat:=xlCSV, Filename:=shtnew & Format(Now(),
"mm-dd-yy")

The error is because you have bookname defined as a workbook.
Activeworkbookname is a string. If you dim bookname as a workbook you want:
Set bookname=activeworkbook

 

[Laroche J]

Bob Greenblatt wrote on 2009-03-13 18:13:

The error is because you have bookname defined as a workbook.
Activeworkbookname is a string. If you dim bookname as a workbook you want:
Set bookname=activeworkbook

and
filename:=shtold.parent.name & format(now(),"mm-dd-yy")

From a previous post I gather that shtold is a worksheet, its parent is the
workbook (the file) whose name you want appended by the date.

JL
Mac OS X 10.4.11, Office v.X 10.1.9