PDA

View Full Version : Solved: Save CSV as Excel file



Anne Troy
02-04-2006, 09:11 PM
I can't get this together! :banghead:

I want to save the active workbook (which is a CSV file) to an XLS file. I want the name to be:

C:\documents and settings\administrator\ngapp\word\

and then the contents of sheet1!C1

and then the date in mmyy format.

I'll put the code in my personal.xls file. :)

Thanks in advance!!

XLGibbs
02-04-2006, 09:53 PM
YOu can just change the extension using this in a module..

Dim strFileName as string, strPath as String

strFileName = ActiveWorkbook.Name
strPath = "C:\documents and settings\administrator\ngapp\word\"
strFileName = Left(strFileName,Len(strFileName)-4) & Format(now(),"_mmyy") & ".xls"
ActiveWorkbook.save strPath & strFileName


I am burning off a coffee buzz if it needs re-work..

Anne Troy
02-04-2006, 09:57 PM
Yeah. I wasn't copying or moving so I only looked at "save as" and I couldn't get it to work for a different file type...

And I don't want it as the active workbook name. I want it to be whatever is in C1 of Sheet1....but I think I can figure that out. :)

XLGibbs
02-04-2006, 10:02 PM
Duh, perhaps I should have read the post...Doh!


strFileName = Sheets("Sheet1").Range("C1") & Format(Now(),"_mmyy") & ".xls"

Anne Troy
02-05-2006, 10:32 AM
Okay. I get an error on the one line:
Option Explicit
Option Explicit
Sub SvMe()
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Range("A:D").Sort Key1:=Range("D1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Dim strPath As String, strFileName As String

strFileName = ActiveWorkbook.Name
strPath = "C:\documents and settings\administrator\desktop\ngapp\word\"
strFileName = Format(Now(), "_mmyy") & Sheets("Sheet1").Range("B1") & ".xls"
ActiveWorkbook.Save strPath & strFileName

End Sub


On the .Save, above, I get "Wrong number of arguments or Invalid Property Assignment. If I change it to .SaveAs, I get Subscript out of range on that entire line.

Help!

XLGibbs
02-05-2006, 10:53 AM
Change the sheet name to match the CSV files sheet name....if it is not "Sheet1" as seen in the tab, that would be the error....it should have been SaveAs in my original code...my bad on that one...

or change it to Sheets(1) as opposed to Sheets("Sheet1")

You can also do

Cells.Columns.AutoFit
Range("A:D").Sort Key1:=Range("D1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal



without the Select Selection... bits...:)

Let me know!

Pete

Anne Troy
02-05-2006, 11:03 AM
k. Works great. Last night, I'd tried changing the sheet name, but it didn't work. :)

Anne Troy
02-05-2006, 11:07 AM
One more Q, Gibbs. I'm doing different things here, and I'll just change the code... At some point, I'll want "this" month, but right now I'm backtracking, so I want to code the appropriate month. How do I make this:

strFileName = Format(Now(), "mmyy") & " " & Sheets("data").Range("B1") & ".xls"


be more like:

strFileName = "1205 " & Sheets("data").Range("B1") & ".xls"

Where "1205 " is just text...

XLGibbs
02-05-2006, 11:14 AM
basically the strFilename is just a string that can be assembled any way you wish..

The syntax above would work for "1205 " but you can do this as well..


strDate = Format(now(),"mmyy")

StrFileName = strdate & " " & Sheets("data").Range("B1") & ".xls"


whatever you identify as strDate would be the prefix of the file name...


strDate = "1205"


if you want last month....(as an ongoing)

strMonth = Right( "0" & (Month(Now()) -1),2)
if strMonth = "00" then strMonth = 12

strYr = right(Year(now()),2)
If strMonth = "12" then strYear = Right(year(now())-1,2)

strDate = strMonth & strYear


which if run today would produce "0106"

if run in January would produce "1205"

Anne Troy
02-05-2006, 11:16 AM
Cool. I'll try it. Thank you, darlin'.

XLGibbs
02-05-2006, 11:18 AM
Anne, I edited to change the syntax to produce the correct number of characters for a mmyy format string...in the strMonth and strYear syntax...

XLGibbs
02-05-2006, 11:20 AM
You might also find this useful. Not yet available in KB, but under revew...attached...you can specify lots of copying and renaming type things...although nothing is built in to handle your sorting into this thing, but you could effectively change all the filenames and extensions at the same time...

then a loop could be written to loop through the folder of the new files and sort / auto fit Range(A:D) if each workbook sheet 1...


http://vbaexpress.com/forum/showthread.php?p=56510#post56510

look for version 1.4
http://vbaexpress.com/forum/images/smilies/friendship.gif

Anne Troy
02-05-2006, 11:23 AM
I'm doing about 8 files for each "month" or "year". It takes anywhere from 5 to maybe 20 minutes to create a file, so I don't need to automate naming them so much. :)
Thanks for all your help, Gibbs. Marking it solved.

Anne Troy
02-05-2006, 04:37 PM
Okay. I was getting an error. Changing the file name to "xls" doesn't change the file type FROM CSV, so here's the changed code I used finally:

Option Explicit
Sub SvMe()
Cells.Columns.AutoFit
Range("A:D").Sort Key1:=Range("D1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Dim strPath As String, strFileName As String, strDate As String

strFileName = ActiveWorkbook.Name
'strdate = Format(Now(), "mmyy")
strDate = "2005"
strPath = "C:\documents and settings\administrator\desktop\ngapp\word\"
strFileName = strDate & " " & Sheets("data").Range("B1") & ".xls"
ActiveWorkbook.SaveAs strPath & strFileName, xlNormal
Kill "C:\documents and settings\administrator\desktop\ngapp\data.csv"


End Sub