Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Meet the Employees of DW Simpson
Patty Jacobsen Simpson, Bob Morand, Kristyn Sakelaris, Sean Loboda, KC Cho, Maureen Matous, Ellen Page
Aaron Benton, Becki Tobia, Kimberly Skora, Margit Vogele, Barclay Burns, Jason Blundy, Dan Karrow, Tom Troceen
Valorie Mulder, Marianne Westphal, Carol Lee, Jennifer Retford, Kieran Welsh-Phillips, Lindsey Nelson, Emily Paxton
Angie Wachholz, Derek Mulder, Julie Garwood, Caitlin Cunningham, David Benton, Dave Retford, Sarah Cleveland, Rhonda Glick
Genevieve Shannon, Meghan Bautista, Carol Datu, Barb Rave, Jesus Perez, Dan Kane, Chris Zdenek, Scott Simon, Kriss Wells


Reply
 
Thread Tools Display Modes
  #1  
Old 03-16-2010, 11:48 AM
ZugZug's Avatar
ZugZug ZugZug is offline
Member
SOA
 
Join Date: Jan 2009
Location: Computer desk
Posts: 1,029
Default Excel VBA - converting a .csv to xls (or xlsx)

I'm writing a macro that, after performing some data cleaning, saves my file in a specified directory. The original file is .csv, and I'd really like it to come out in either a xls or xlsx format. Here's my code to save the file:

Code:
   Dim newFile As String, fName As String
   fName = Range("A2").Value
   newFile = fName & " " & Format$(Date, "mmddyyyy")
   ChDir _
   "C:\Documents and Settings\username\Desktop"
   ActiveWorkbook.SaveAs Filename:=newFile
Right now it's saving it as a csv. Is there anything I can add or modify which will save this in the format I want? TIA!
Reply With Quote
  #2  
Old 03-16-2010, 12:06 PM
Abused Student's Avatar
Abused Student Abused Student is online now
Member
SOA
 
Join Date: Feb 2007
Location: The Eighth Circle of Hell
Favorite beer: Cold and lots of it
Posts: 40,204
Default

The next option after Filename is FileType. I think if you added the .xls or .xlsx in the file name it would be correct or if you changed the filetype.
__________________
GAME ON!!!!!!! Let your ness show. Join the D&D fun. Started but applications still accepted


Officially assigned the role of Dictator, 9/30/09. Bow to my whims.
Reply With Quote
  #3  
Old 03-16-2010, 12:07 PM
MyKenk's Avatar
MyKenk MyKenk is offline
Note Contributor
CAS
 
Join Date: Nov 2005
Location: twitter.com/mykenk
College: Drake '06
Posts: 8,524
Default

Quote:
Originally Posted by ZugZug View Post
I'm writing a macro that, after performing some data cleaning, saves my file in a specified directory. The original file is .csv, and I'd really like it to come out in either a xls or xlsx format. Here's my code to save the file:

Code:
   Dim newFile As String, fName As String
   fName = Range("A2").Value
   newFile = fName & " " & Format$(Date, "mmddyyyy")
   ChDir _
   "C:\Documents and Settings\username\Desktop"
   ActiveWorkbook.SaveAs Filename:=newFile
Right now it's saving it as a csv. Is there anything I can add or modify which will save this in the format I want? TIA!
Try:

ActiveWorkbook.SaveAs Filename:=newFile, FileFormat:=xlExcel12
__________________
Twitter!

Reply With Quote
  #4  
Old 03-16-2010, 12:16 PM
ZugZug's Avatar
ZugZug ZugZug is offline
Member
SOA
 
Join Date: Jan 2009
Location: Computer desk
Posts: 1,029
Default

Great suggestions, it worked; thanks both of you!
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 03:54 PM.


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.17258 seconds with 7 queries