VBA Help - Convert text to XLS file and save as new workbook in directory

[narpassword]

Hello,

I am very excited to be a part of the lovely world that is the Mr. Excel forum. I have used the forum for a long time to learn, but I have finally decided to join and become an active member.

For my first post I would like some help solving the following problem:

I have several csv files that one of our systems at my job dumps each day. I have written a batch file to copy them to a directory and merge them into one .txt file. I run this everyday, so each day the file is updated.

Next, I have a string of code in a macro that will open the file with delimiters on and comma separation. The problem is that I need the new file to save automatically as a .xls file with a specific name and then close.

Please see the code I currently have below:

Sub openTextTabDelimited()
Workbooks.OpenText Filename:="C:\Users\username\Desktop\blending2\all.txt", _
DataType:=xlDelimited, Comma:=True, Tab:=False
End Sub


Any and all help would be appreciated.

Thanks, and I am looking forward to being a part of such a great community.

 

[Jerry Sullivan]

Welcome to MrExcel,


Here's one way to code that.

Sub OpenCSV_SaveToXLS()
'--opens csv file, saves it as xls format then closes it.
 Const sPATH As String = "C:\Users\username\Desktop\blending2\"
 
 Application.ScreenUpdating = False
 
 Workbooks.OpenText Filename:=sPATH & "all.txt", _
   DataType:=xlDelimited, Comma:=True, Tab:=False
 
 '--optional: to supress "file already exists" warning
 Application.DisplayAlerts = False
 
 With ActiveWorkbook
   .SaveAs Filename:=sPATH & "all.xls", FileFormat:=xlExcel8
   .Close SaveChanges:=True
 End With
 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
 MsgBox "all.xls File has been saved"
End Sub

The macro-recorder can give you a good start to creating a macro like this.