Help with a Macro

[Guest]

I want to program a macro that will format a CSV file and then save it as a
..xls file with the same name (but not the same file extension)

I have tried to get this macro to work but I'm a newbie with macros. Can
someone debug it for me? Thanks in advance.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/20/2005 by CPertel
'
' Keyboard Shortcut: Ctrl+q
'
Dim myFileName As String

Columns("A").Select
Selection.ColumnWidth = 15
Columns("B:O").Select
Selection.Style = "Currency"

myFileName = ThisWorkbook.Worksheets("Sheet99").Range("a99").Value _
& "--" & Format(Now, "yyyy_mm_dd__hh_mm_ss") & ".xls"

ThisWorkbook.SaveAs Filename:=myFileName

End Sub

Have no idea why the Sheet99 (I have one sheet for each workbook) and what
the range a99 means. Also don't know whether the long myFileName statement
needs to be on one line or can be "folded" into two.

 

[Guest]

The sample is trying to read the new file name from Sheet99!a99, and then
tack on a date/time indicator. Your requirement, I think, is simpler:
myFileName=left(activeworkbook.name,len(activeworkbook.Name)-3)&"xls"
activeworkbook.SaveAs filename:=myfn, fileformat:=xlExcel7
Note that I used activeworkbook where the sample used thisworkbook. Because
your macro is formatting a .csv file, I'm assuming that the file you want
saved is different than the file in which the macro is running.
HTH. --Bruce

 

[Dave Peterson]

In your other post, you wanted to pick up a value from a cell in a worksheet.

Since you didn't provide the name of the worksheet or the address of the cell,
that responder just put sheet99 and used A99.

You can use worksheets(1) (since you only have one worksheet), but change A99 to
what you need it to be.

myFileName = ThisWorkbook.Worksheets(1).Range("a99").Value _
& "--" & Format(Now, "yyyy_mm_dd__hh_mm_ss") & ".xls"

This can be put on one line, but in the newsgroups, most people try to wrap long
lines at less than column 80. Then copying and pasting becomes much easier.
(Lots of people post back that the code didn't work--since they didn't know how
to fix those long lines that wrapped without the continuation symbol.)

And this line:

ThisWorkbook.SaveAs Filename:=myFileName
should become:
ThisWorkbook.SaveAs Filename:=myFileName, fileformat:=xlworkbooknormal

=========
In fact, since you're working with a .csv file (and you can't save the macro in
that kind of file), you may want to create a new workbook with that code in it.

Then your code would want to work on the activeworkbook:


Option Explicit
Sub Macro1()

Dim myFileName As String

with activesheet
.Columns("A").ColumnWidth = 15
.Columns("B:O").Style = "Currency"

myFileName = .Range("a99").Value _
& "--" & Format(Now, "yyyy_mm_dd__hh_mm_ss") & ".xls"

.parent.SaveAs Filename:=myFileName, fileformat:=xlworkbooknormal

end with

End Sub

(I removed some of the .select's. But you still have to fix A99.)

If you want the same name as the .csv file (dropping the value from the cell and
the date/time), it becomes:

Option Explicit
Sub Macro2()

Dim myFileName As String

With ActiveSheet
.Columns("A").ColumnWidth = 15
.Columns("B:O").Style = "Currency"

myFileName = Left(.Parent.FullName, Len(.Parent.FullName) - 4) & ".xls"

.Parent.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
End With

End Sub