G Guest Oct 6, 2005 #1 How can I use macro rename the sheet in the .csv files in a folder to "Sheet1" and convert them to .xls files?
How can I use macro rename the sheet in the .csv files in a folder to "Sheet1" and convert them to .xls files?
G Guest Oct 6, 2005 #2 Hi nc, just open the file and use save as 'file name' and in save type 'microsoft excel workbook' anirudh
Hi nc, just open the file and use save as 'file name' and in save type 'microsoft excel workbook' anirudh
G Guest Oct 6, 2005 #3 Anirudh I am not being funny, I meant macro???!!!. Because I have about 70 odd files.
D Dave Peterson Oct 6, 2005 #4 Since you posted in .misc, you're requirement of a macro solution for multiple files wasn't immediately evident--well, not to me or anirudh. But you could try this one: Option Explicit Sub testme() Dim myFileNames As Variant Dim fCtr As Long Dim wks As Worksheet Dim newFileName As String myFileNames = Application.GetOpenFilename _ (filefilter:="Comma Separated Values, *.csv", MultiSelect:=True) If IsArray(myFileNames) = False Then 'user hit cancel Exit Sub End If For fCtr = LBound(myFileNames) To UBound(myFileNames) Set wks = Workbooks.Open(Filename:=myFileNames(fCtr)).Worksheets(1) wks.Name = "Sheet1" newFileName _ = Left(myFileNames(fCtr), Len(myFileNames(fCtr)) - 4) & ".xls" With wks.Parent Application.DisplayAlerts = False .SaveAs Filename:=newFileName, FileFormat:=xlWorkbookNormal Application.DisplayAlerts = True .Close savechanges:=False End With Next fCtr End Sub Just click & ctrl-click on as many as you want to convert (or click the top one and shift-click the bottom one to select all in between).
Since you posted in .misc, you're requirement of a macro solution for multiple files wasn't immediately evident--well, not to me or anirudh. But you could try this one: Option Explicit Sub testme() Dim myFileNames As Variant Dim fCtr As Long Dim wks As Worksheet Dim newFileName As String myFileNames = Application.GetOpenFilename _ (filefilter:="Comma Separated Values, *.csv", MultiSelect:=True) If IsArray(myFileNames) = False Then 'user hit cancel Exit Sub End If For fCtr = LBound(myFileNames) To UBound(myFileNames) Set wks = Workbooks.Open(Filename:=myFileNames(fCtr)).Worksheets(1) wks.Name = "Sheet1" newFileName _ = Left(myFileNames(fCtr), Len(myFileNames(fCtr)) - 4) & ".xls" With wks.Parent Application.DisplayAlerts = False .SaveAs Filename:=newFileName, FileFormat:=xlWorkbookNormal Application.DisplayAlerts = True .Close savechanges:=False End With Next fCtr End Sub Just click & ctrl-click on as many as you want to convert (or click the top one and shift-click the bottom one to select all in between).
